Browse Prior Art Database

SQLREORG-DBSPACE Reorganization Utility for SQL/DS Data Bases

IP.com Disclosure Number: IPCOM000100763D
Original Publication Date: 1990-Jun-01
Included in the Prior Art Database: 2005-Mar-16
Document File: 3 page(s) / 119K

Publishing Venue

IBM

Related People

Masemore, TL: AUTHOR

Abstract

Disclosed is a program that provides a Data Base Administrator (DBA) with a dependable and easy to use method of reorganizing dbspaces within the IBM Structured Query Language/Data System (SQL/DS) (Program 5688-004) data base product. This program automates the multiple step reorganization process which assures maximum data base performance and minimum disk storage space usage.

This text was extracted from an ASCII text file.
This is the abbreviated version, containing approximately 52% of the total text.

SQLREORG-DBSPACE Reorganization Utility for SQL/DS Data Bases

       Disclosed is a program that provides a Data Base
Administrator (DBA) with a dependable and easy to use method of
reorganizing dbspaces within the IBM Structured Query Language/Data
System (SQL/DS) (Program 5688-004) data base product.  This program
automates the multiple step reorganization process which assures
maximum data base performance and minimum disk storage space usage.

      Dbspace reorganization is one of the routine maintenance
activities required to support an SQL/DS data base.  It will decrease
the amount of disk space required to store data by packing data
closely together and eliminating unused space.  It will also increase
performance by replacing all related data together and sorting it in
the most frequently used sequence.

      The basic method of performing the reorganization function
within SQL/DS is to make a copy of everything related to a dbspace,
drop the dbspace, re-acquire the dbspace, and then restore everything
back to the dbspace in a packed and orderly manner.

      Although SQL/DS provides the basic capabilities to perform this
process, it is a very laborious and time-consuming activity that is
prone to error.  Without the SQLREORG program, it is the
responsibility of the Data Base Administrator to correctly identify
everything that is related to the dbspace and to properly save it and
restore it during the reorganization.

      All information that is related to a dbspace is kept by the
Data Base Management (DBMS) within a set of SQL/DS catalogs.  Much of
this information is stored in an internal format which is easy for
SQL/DS to use, but can be difficult for the Data Base Administrator
to understand.

      The SQLREORG program will correctly capture all data related to
an SQL/DS dbspace by retrieving the information from the data base
catalogs and translating it into the SQL statements which were
actually executed by data base users to generate the information.
Dbspace information that is captured by the program includes all
tables, indexes, views, grants, table comments, column comments,
table labels, column labels, access modules, referential integrity
relationships, and the user data itself.

      One of the more difficult parts of the dbspace information
capture process within SQLREORG is the  correct sequencing of the
restore statements so that the chronological chain of dependencies is
re-established.  If not properly sequenced, dbspace restore
statements will fail due to missing dependent objects at the time
that each statement is executed.  This program maintains the correct
sequencing by first collecting the dbspace information from th...