DEFRAG (Defragment Index)

The DBUTLTY DEFRAG function defragments or compresses the DXX, that is, the "lowest" level of the IXX that contains key values and pointers to actual rows. The DBUTLTY DEFRAG function provides a simpler way to accomplish much the same results as performing an INIT to the Index Area (IXX) and then doing a RETIX or LOAD of the DBID.
The DBUTLTY DEFRAG function defragments or compresses the DXX, that is, the "lowest" level of the IXX that contains key values and pointers to actual rows. The DBUTLTY DEFRAG function provides a simpler way to accomplish much the same results as performing an INIT to the Index Area (IXX) and then doing a RETIX or LOAD of the DBID.
For the INIT function to operate on the IXX, the DBID must be completely offline to other users. However, the DEFRAG function runs through the MUF and allows the DBID to remain online and usable to other applications during the DEFRAG process.
The following topics are discussed on this page:
DEFRAG Overview
manages index space usage at the block level. The process of adding index entries and pointers adds to an existing index block until it is full. At that point the index must be split.
chooses intelligent split points based on the pattern of maintenance to the existing block and, from there, continues filling existing blocks until they must split.
Based on the above add patterns, each low-level (DXX) block represents a given key range into which future adds may fill. If only some of the pointers or entries are deleted in a given block, the space formerly occupied by them is available for new adds, but only within the key range of that block. When future adds are all outside this range, index fragmentation results.
If all entries are deleted from an index block, it is reclaimed as a free block and is then available for future use. In this case, no fragmentation exists.
Instead of defragmenting the IXX by performing an INIT of the IXX then doing a RETIX or LOAD of the DBID, you can simply use the DEFRAG function. The DEFRAG function does not alter the high-level IXX blocks or lower the number of index levels.
DEFRAG SMP locking is used to interact with backwards processing in the index, such as when a LOCBR command is issued. In such cases, DEFRAG obtains an exclusive SMP lock at the start of each key ID processing. If there are no backward requests for this key ID during the life of the DEFRAG, the lock is simply maintained. If there is a backward request during the process, DEFRAG notes where it is in the DXX and frees the SMP lock to allow the backward process to run. DEFRAG then reestablishes the lock, rereads its blocks, and continues as before. Backwards processing obtains a shared SMP lock. Information about DEFRAG SMP locking can be found in the SMP Informational Report obtained by specifying it with the SMPTASK MUF startup option. See the description of the DEFRAG event shown in the SMP Informational Report section.
The DEFRAG function is necessarily an I/O intensive process. Before two blocks can be merged, they are written to DASD, if they are already in the pipeline. Each write in the merge process is an immediate write.
When two blocks are chosen to be merged, their block numbers and information are written to IXX control block. If the MUF fails, restart (or any open of the DBID for update) uses this information to ensure that the index is consistent in regard to those two blocks. If you decide not to restart the DBID and then attempt to open the DBID as read-only, the open fails with a return code 43(005).
The merged-to block is always written first. The merged-from block is then deleted and written. Write I/O errors during the DEFRAG process are unlikely. If any should occur, however, they are handled in the following way. If it is on the first block (the merged-to block) the block is simply marked invalid, which requires a future user of the block to reread it from DASD, and the DEFRAG terminates. If it is on the second block (the newly deleted block), the index is marked in the MUF memory as unusable. No I/O reads at the DXX level are allowed. You should close the DBID and correct the cause of the I/O error. When the DBID is reopened for update, the same correcting code that exists for system failure runs and deletes the second block. If the I/O error persists, the index has to be initialized and rebuilt using the RETIX function, that is, dealt with the same as any other I/O error problem.
The DEFRAG function:
  • Cannot be used with entry points other than DBNTRY, that is, cannot be used with 
     entry points. If the IXX is already open in the MUF when DEFRAG begins, the DEFRAG process checks to see if there have been any old entry point users for that DBID since the physical IXX was opened. If the answer to that test is no, DEFRAG continues. If the answer to that test is yes, DEFRAG ends with a return code 94(072).
    While DEFRAG is running, you can run an old entry point request against the DEFRAG DBID, but if that is done the DEFRAG function of DBUTLTY stops with a return code of 94(072).
  • Cannot be run in a MUFplex environment if two or more MUFs are enabled.
Stopping DEFRAG
The index defragmentation is subject to REQABORT if you want to stop it while it is running. This REQABORT is non-destructive and leaves the index perfectly usable.
Security Considerations
If DBUTLTY is externally secured, you must have access to this DTUTIL resource: cxxname.DBUTLTY.DEFRAG. There are no underlying table rights.
Report includes information about the DXX index level and also IXX index level 
The report naturally provides information about the DXX blocks within the index data set. The report has been enhanced to also track and report the number of high-level index blocks that have a usage for the different DXX key IDs involved. This high-level information is not usually needed and can be ignored. It might have a value if using BUFFER_POOL_DEF statement in MUF to separate buffer pools by DBID or sets of bases. In this case it may be valuable to know the size needed for IXX buffers different than DXX buffers. Because of how high-level blocks can be shared across key ID values, there will not always be a perfect accounting of the number of buffers.
When to Use
Use the DEFRAG function when you want to defragment the index (IXX).
How to Use
The MUF must be active when you execute this command. Execute the DEFRAG function using the following command format:
 ─┬─────────────────────────────────┬─┬──────────────┬───────►                    └─ ,KEYID=
 ─┬──────────────────┬─┘ └─ ,PERCENT=
 ─┘                                 ├─ ,FIRSTKEY= ─ 
 ─┤                                 └─ ,LASTKEY= ─ 
 ──┘ ►───┬──────────┬──────────────────────────────────────────────────────────────►◄     └─ PRTY=n ─┘
    Invokes the DEFRAG function.
Required Keywords
  • DBID=
    Specify the name of the DBID that you want to use.
    • Limits
      A valid DBID
    • Default:
      (No default)
Optional Keywords
  • ,KEYID=
    Specify the DATACOM-ID of the key that you want to use for this execution of the DEFRAG function.
    When you do not specify a KEYID=, all of the key IDs in the database are subjected to defragmentation. 
    If by analyzing past executions you know that certain key IDs do not benefit from defragmentation, you can exclude those key IDs from being defragmented by using KEYID= to specify a single key ID that does benefit from a DEFRAG execution. 
    If more than one key ID benefits from defragmentation, specify a separate DEFRAG execution for each of those key IDs.
    • Limits
      3-digit DATACOM-ID of the desired key
    • Default:
      (No default)
  • ,FIRSTKEY= and/or ,LASTKEY=
    If you specify KEYID=, you can use FIRSTKEY= and/or LASTKEY= to limit DEFRAG to a segment of the specified key. We recommend you allow FIRSTKEY=/LASTKEY= to default. The DEFRAG 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 DEFRAG. If one keyword is specified and the other is not specified, a default value is selected for the missing keyword. Normal
    key 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 is left 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 DEFRAG 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.
    cannot edit this data for syntax, so the control statement can 
     invalid, 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.
    • Limits
      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:
      The starting/ending position of the full key range from low to high values.
    PERCENT= defaults to 100, which means all DXX blocks that can be merged will be merged. Therefore, any block that is less than completely full is a merge candidate. PERCENT=0 (zero) is treated as PERCENT=100. Coding a value between 1 and 99 means you want to tune which DXX blocks qualify as a merge candidate. Each DXX block has its current populated percentage compared to the number specified. If it is less than the specified percent, it is a merge candidate.
    We recommend that you do not code a value for PERCENT= but allow it to default to 100
  • ,PRTY= 
    Identifies for this execution the URT priority of frequent or long-running DB requests in the MUF issued by this DBUTLTY function.
    • Limits
      1 (lower) through 15 (higher)  
      0 is allowed but means that you should use the default 16 value
    • Default:
      16 is the default value if PRTY= is not specified or is set as 0
Example JCL
The following shows the command to defragment an index with DBID=781.
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.
See the previous note and 
See the previous note and 
 //SYSIN    DD *                                   Command input           DEFRAG  DBID=781  /*
Sample Report (DEFRAG)
Following is a sample report page. For an example report header, see Sample Report Headers. 
                    CONTROL CARD(S)                     .........1.........2.........3.........4.........5.........6.........7.........8                          DEFRAG  DBID=781                       FUNCTION=DEFRAG                        DBID=00781
This page of the report shows the following:
  • The command exactly as entered.
  • An analysis of keywords encountered and expected. Any errors found are flagged with a note in the left margin.
  • Any messages related to syntax processing.
If a single key ID is requested with the keyword KEYID then only that key ID is provided. Without the keyword the report provides one line for each key ID defined for the database. In addition, it prints one line for each internal key ID that is in use for index area.
The column BLOCKS BEFORE provides the number of low-level DXX index blocks found for the key ID before the defrag action.
The column BLOCKS COMBINED provides the number of those blocks that had sufficient space in a pair to combine into one block.
The column BLOCKS DELETED provides the number of the blocks found as currently empty and available to be deleted from this key ID and made part of the index free space blocks.
The column BLOCKS AFTER provides the number of blocks remaining after any combination and/or delete.
The column PERCENT DELETED provides the percent of the before blocks removed by combination or delete.
The column NOTES provides a short test of what an internal key ID is used for.
The column Index area provides the name of the index area such as IXX or I01 through I99.
The column HIGH LEVEL INDEX BLOCKS provides the number of HIGH LEVEL IXX index blocks that includes one or more entries for this key ID. Because multiple key ID values may share one high level IXX index block the total 'might' be higher than actually exist..
Following the one or more key ID print lines is a total line for that key ID.
Following the total line is optionally a second report that provides totals by index area. This is only printed if multiple index areas are involved in the key ID report section. The columns are similar to the key ID reported columns.   
Example 1 report with a single key ID (DEFRAG DBID=-297,KEYID=2)
                                                     DEFRAG REPORT                        BLOCKS         BLOCKS         BLOCKS         BLOCKS         PERCENT              INDEX     HIGH LEVEL  KEY ID         BEFORE       COMBINED        DELETED          AFTER         DELETED  NOTES        AREA   INDEX BLOCKS      2            688              0              0            688            0.00                102              4       TOTALS            688              0              0            688            0.00                102              4
Example 2 report  with a Multiple Dataset Index (DEFRAG DBID=0297)
                                                     DEFRAG REPORT                                                               BLOCKS        BLOCKS        BLOCKS        BLOCKS  PERCENT                         INDEX    HIGH LEVEL KEY ID        BEFORE      COMBINED       DELETED         AFTER  DELETED  NOTES                   AREA  INDEX BLOCKS                                                                                                                         1            53            10            24            29    45.28                           I01             1      2            85            28            32            53    37.64                           I02             1      3            23             2             2            21     8.69                           I03             1      4            39            18            19            20    48.71                           I04             1      5            23            14            14             9    60.86                           I05             1      6            12             8             8             4    66.66                           I06             1      7            12             8             8             4    66.66                           I07             1      8            57            39            39            18    68.42                           I08             1      9            65             3             3            62     4.61                           I00             1     11            11             1             2             9    18.18                           I00             1     12            13             0             3            10    23.07                           I00             1     98             0             0             0             0     0.00                           I00             1     99             0             0             0             0     0.00                           I00             1  39327             1             0             0             1     0.00  DATA FREE SPACE          IXX             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I00             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I01             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I02             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I03             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I04             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I05             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I06             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I07             1  39321             1             0             0             1     0.00  INDEX FREE SPACE         I08             1 TOTALS           403           131           154           249    38.21                                          23                                                       DEFRAG REPORT - TOTALS BY INDEX AREA INDEX        BLOCKS        BLOCKS        BLOCKS        BLOCKS  PERCENT    HIGH LEVEL    AREA         BEFORE      COMBINED       DELETED         AFTER  DELETED    INDEX BLOCKS                                                                                            IXX             1             0             0             1     0.00               1    I01            54            10            24            30    44.44               2    I02            86            28            32            54    37.20               2    I03            24             2             2            22     8.33               2    I04            40            18            19            21    47.50               2    I05            24            14            14            10    58.33               2    I06            13             8             8             5    61.53               2    I07            13             8             8             5    61.53               2    I08            58            39            39            19    67.24               2     
  • In the report all user keyids which are processed have statistics printed. Any special keyid (those with notes, that is, INDEX FREE SPACE, DATA FREE SPACE, etc.) only print statistics if it has at least one DXX block. This example does not contain an exhaustive list of the special keyids.
  • The INDEX FREE SPACE key ID exists in every index area once it has a deleted block.
  • There is a printed statistic of "blocks deleted". This contains two things.
    • The first is DXX blocks which DEFRAG found already deleted but which the index queue has not yet processed.
    • The second are blocks which the DEFRAG itself deletes.
    These are typically blocks which are combined with other blocks, but they may also be something like a cleanup of logically deleted entries which were still physically present in the DXX when DEFRAG ran. Hence DEFRAG can be used as a form of DXX TSN cleanup.