OLREORG (Reorganize a Database Online)

When the physical sequence of records in an area is causing a performance problem, a database reorganization is usually scheduled. In the past, the table being reorganized was unavailable while backups and reloads of the data, in Native Key sequence, were performed. The OLREORG function lets you reorganize the database table without taking it offline.

For information about support for the OLREORG function by the DBUTLTY STATUS console command, see DBUTLTY STATUS.

Note: The DBUTLTY STATUS command is only available when either the FIRSTKEY= and LASTKEY= keywords are not specified or no rows are found in the specified key range.

The OLREORG function of DBUTLTY combines rows in a given key range (reference group) into as few blocks as possible while reducing the effort (physical I/O) needed to retrieve the rows in native sequence, and it does this without making the data unavailable to other users. OLREORG moves rows from blocks with the fewest rows within a specified reference group (REFGROUP) to blocks with the most rows within the REFGROUP, thereby reducing the number of blocks used to contain the REFGROUP. Be aware, however, that OLREORG does not provide a one hundred percent Native Key sequence.

A reference group is a specified number of data blocks that are considered as a group in analyzing (reporting) and reorganizing (using OLREORG) data rows on a reference group by reference group basis.

You can use statistical information about sequential data access to determine if there is a significant amount of sequential access, and of those accesses how many are to non-contiguous data blocks. This statistical information is contained in the Area Daily Statistics (ADS) table in the history database. We therefore recommend that you enable ADS before using OLREORG.

Note: For information about the history database and ADS, see the CA Datacom/DB Database and System Administration Guide.

Once you have established that a given table has significant sequential access where there have been a large number of non-contiguous data block accesses, or if you know that a significant amount of sequential processing is to occur, you can execute the DBUTLTY REPORT REFGROUP option to review the current native sequence population. For information about the REFGROUP report, see REFGROUP Report.

Once your review of the current native sequence population is completed, you can decide if an online reference group reorganization should be executed to improve the native sequence population.

OLREORG runs through the MUF so that concurrent access is available to other users of the specified table.

Note: You can interrupt or stop the execution of OLREORG without any risk to your environment by issuing a COMM REQABORT. The REQABORT is honored after each reference group is processed. In this case, no summary report is produced. The last key value processed is provided instead. This key value can be used for the FIRSTKEY= parameter if you want to start the process again by running a new DBUTLTY OLREORG.

If the OLREORG is not able to be interrupted using REQABORT, an operator cancel can be used.

As OLREORG moves rows from block to block to improve order, it als frees space in blocks. You can rerun it and it will often find additional movements that would be beneficial.

For verification of the reorganization process, you can include another reference group (REFGROUP) report specification after the reference group reorganization request to see the effect of the OLREORG.