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.
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.
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.
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.
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.
The following restrictions apply:
- The area in which the table (that you specify for OLREORG) resides must be loaded with URI=YES.
- Logging must be turned on in order to use OLREORG.
The DBUTLTY external resource name for the OLREORG function is OLREORG. It has no table rights associated with it.
When to Use OLREORG
Execute the DBUTLY OLREORG function when you determine that a reference group reorganization is appropriate.
Using Empty Blocks in OLREORG
When the amount of free space is limited in used data blocks, the DBUTLTY function OLREORG allows the use of empty blocks in a reorganization.
Support for the OLREORG empty block feature includes the following categories:
- Implementation of a way to keep track of empty blocks
- New DBUTLTY behaviors
- Changes to the REPORT produced by OLREORG
- External limits
Keeping Track of Empty Blocks
Datacom/DBkeeps track of the available space of a data area using a special key ID in each DBID index. Data space option 1, basic space reclamation, and 2, wraparound mode, do not keep track of which blocks are logically empty, that is, blocks that were used in the past but currently have all their rows deleted.
To enhance the efficiency of OLREORG, the following space options were added that keep track of empty blocks:
- Option 4 is basic space reclamation with an empty block index.
- Option 5 is wraparound mode with an empty block index.
If you plan to use OLREORG, then it is appropriate to migrate all DSOP option 1 areas to DSOP option 4 and all DSOP option 2 areas to DSOP option 5.
Changing the space option does not affect the loaded status of the data area and is effective immediately following its change in the CXX. Because the empty block index was not maintained in the past for basic and wraparound modes, we recommend that, when changing to a DSOP 4 or 5, you run a DATASCAN OPTION=REBUILD_SPACE_INDEX to rebuild the space index, including the new empty block component of the space index.
You are not required to run this RETIX.
DBUTLTY OLREORG Report Example Change
If the DSOP area is 4 or 5, the line is:
Empty blocks used.
The line displays a count of the number of empty blocks to which rows were moved during the OLREORG. The following is a sample of this report:
Rows processed - 5,000 Rows moved - 2,512 Blocks freed in reference group - 2,510 Exclusive control conflicts - 0 Empty blocks used - 5
If the DSOP area is 0, 1, or 2, the line is:
No empty blocks used due to DSOP.
A DSOP of 3 is not supported by OLREORG.
The following is a sample of this report:
Rows processed - 5,000 Rows moved - 284 Blocks freed in reference group - 284 Exclusive control conflicts - 0 No empty blocks used due to DSOP
If there are no more blocks in the empty block index, OLREORG uses the next available overflow (NAO) block. OLREORG never causes a dynamic extend.
How to Use OLREORG
The MUF must be active when you execute this command. Execute the OLREORG function using the following command format.
►►─ OLREORG TABLE=tname,DBID=dbid,REFGROUP=n─┬────────────────┬────────────► └─ ,NUMBER= ─n─┘ ►─┬──────────────────┬─────────────────────────────────────────────────────►◄ ├─ ,FIRSTKEY= ─n─┤ └─ ,LASTKEY= ─n──┘
- OLREORGInvokes the OLREORG function.
- ,TABLE=tnameSpecify the name of the table that you want to use. The area in which the table resides must be loaded with URI=YES. If you attempt to generate a REFGROUP report and the area was not loaded with URI, an error occurs and a return code is generated.
- Valid Entries:a valid table name
- Default Value:(No default)
- ,DBID=Specify the name of the DBID that you want to use.
- Valid Entries:a valid DBID
- Default Value:(No default)
- ,REFGROUP=nSpecify the number of blocks to treat as a reference group. Each REFGROUP represents the number of blocks which are treated as a group. Each REFGROUP also has a certain number of blocks in which it already has rows stored. The goal of OLREORG is to move rows from blocks with the fewest rows within the REFGROUP to blocks with most rows within the REFGROUP, and hence to reduce the number of blocks used to contain this REFGROUP.A REFGROUP value represents the number of data blocks it takes to hold a range of key values for the native sequence key. For example if you specify REFGROUP=5, the index is scanned starting with the lowest key, and the data block to which each key value points is noted. This scanning continues until more than 5 unique data blocks are encountered. These 5 unique data blocks and their associated index values represent the first REFGROUP. The 6th data block and its associated key value represent the start of the next REFGROUP.You must consider what seems to you to be a good value for REFGROUP=. If you commonly use the DBURTBL macro parameter SEQBUFS= to read this table, half of the value you specify for SEQBUFS= may be a good value for REFGROUP=.
- Valid Entries:2 through 100
- Default Value:(No default)
- ,FIRSTKEY= and/or ,LASTKEY=You can use FIRSTKEY= and/or LASTKEY= to limit OLREORG to a segment of the native sequence key. We recommend you allow FIRSTKEY=/LASTKEY= to default. The OLREORG function normally defaults to include the full specified key range from low values to high values. FIRSTKEY= overrides the default starting position while LASTKEY= overrides the default ending position, limiting what is processed by OLREORG. If one keyword is specified and the other is not specified, a default value is selected for the missing keyword. NormalDatacomkey lengths are from 1 through 180 bytes. The key value data entered for FIRSTKEY= and LASTKEY= can be from 0 (zero) through 59 bytes long. You only need to code the number of positions of the key value that are significant to you, but be aware that the value coded isleft justified regardless of key field data type.The utility pads the low order or remaining positions of the key value with low values on FIRSTKEY= and high values on LASTKEY=. To use the entire key in one step, specify neither FIRSTKEY= nor LASTKEY=. Alternately, you could specify FIRSTKEY=00 and/or LASTKEY=00 to cause processing to start at low values and end at high values. The length of zero indicates that the padded key values (low values and high values) are to be used.The data is not edited or interpreted. Therefore, if the key value data you need to enter is in binary, you must set up the JCL using a hexadecimal mode display and enter the key value data in hexadecimal. This data can contain blanks, commas, and any other special characters.Datacom/DBcannot edit this data for syntax, so the control statement canlookinvalid, especially when it includes blanks or commas or key value data containing English words. For example, FIRSTKEY=12121212121212 means the length is 12, key value is 121212121212. Another example, FIRSTKEY=10FIRSTKEY10 means the length is 10, key value is FIRSTKEY10.
- Valid Entries:A 2-byte length (in a range of 00 through 59) followed by a character string of key value bytes (for the length specified) that represents the key value.
- Default Value:The starting/ending position of the full key range from low to high values.
- NUMBER=nThis parameter can be specified as 1-99 or omitted. When you omit NUMBER=, all blocks qualify as source blocks and OLREORG moves as many rows as possible, so that as few blocks as possible contain the REFGROUP. Specifying a value for this parameter indicates you want to control which blocks qualify as move-from blocks. It can be used to limit row movement to those cases which provide the most benefit. For example, specifying NUMBER=2 indicates that only blocks with two or fewer rows may have their rows moved to other blocks in the REFGROUP.
- Valid Entries:1 through 99
- Default Value:(No default)
Example JCL (OLREORG)
Following is example JCL for an OLREORG.
Use the following as a guide to prepare your JCL. The JCL statements are for example only. Lowercase letters in a statement indicate a value you must supply. Code all statements to your site and installation standards.
//jobname See the previous note andJCL Requirements.// EXEC PGM=DBUTLTY,REGION=2M //STEPLIB See the previous note andJCL Requirements.//SYSIN DD * Command input OLREORG TABLE=ALS,DBID=2,REFGROUP=50 /*
Following is a sample report page. For an example report header, see Sample Report Headers.
For sample REFGROUP reports, see Sample Report: REFGROUP Summary. An example of the summary report generated by executing the OLREORG function against a table containing 3,000 rows follows:
Rows processed - 3,000 Rows moved - 384 Blocks freed in reference group - 129 Exclusive control conflicts - 0
The report displays the following information:
- Rows processedRows processed is the total rows in the table, or if using FIRSTKEY or LASTKEY, the total rows within the specified key range.
- Rows movedRows moved is the total rows moved from less heavily populated blocks within a reference group to more heavily populated blocks.
- Blocks freed in reference groupThe number printed for the blocks freed in reference group is not anabsolutenumber, but must be understood as being relative to the reference group concept. That is, at the start of processing each reference group uses the same number of blocks specified by the REFGROUP parameter. After the OLREORG is run, the same set of rows for each REFGROUP may be in fewer blocks. A block freed in a reference group represents the difference in the number of blocks required to hold each reference group. Because each actual physical block may contain rows from multiple REFGROUPs, the number printed in the report doesnottherefore necessarily represent completelyemptyblocks, that is, a blockfreedis not necessarily the same as anemptyblock.
- Exclusive control conflictsExclusive control conflicts refers to the following. Applications are allowed to maintain rows in the specified table while OLREORG is running. The number printed in the report represents rows that OLREORG chose for movement but skipped because some other task currently had either primary or secondary exclusive control on the row. These rows are skipped for performance reasons.