Rebuilding an Index Area

You can also use the RETIX function to rebuild an Index Area which has been destroyed or damaged in some way.
datacom
You can also use the RETIX function to rebuild an Index Area which has been destroyed or damaged in some way. When using the RETIX function to rebuild the Index Area for all areas in a database at once, initialize the Index Area immediately before using the RETIX function.
When to Use
Use the RETIX function to rebuild an Index Area when you have added a new key definition to a table. If you redefine an existing key, making it longer or shorter, you must first remove it from the Index Area (IXX). See REMOVE (Remove Key ID) for details. Then, rebuild the Index Area using the RETIX function.
When
Datacom/DB
encounters a duplicate value for a Unique Key or a Master Key that has been defined as allowing no duplicates,
Datacom/DB
produces a snap dump of the duplicate.  Duplicate values can happen if the table is loaded with LOADDUPS=YES specified,
After key definition changes, you normally execute the DBUTLTY RETIX function to rebuild the index for affected areas or the entire database. With MINIMAL=NO (the default), RETIX reads the data area in physical sequence, extracted current key definitions, and merges them into the index in a process that also deleted any unnecessary index entries. With MINIMAL=YES, you can restrict RETIX to only performing the minimal actions necessary.
The use of MINIMAL=YES is allowed at the area level only, not the database level. The AREA= keyword is therefore also required. The use of MINIMAL=YES usually reduces the number of I/O events, reduces the amount of CPU used, and reduces the elapsed time of the RETIX, compared to not using MINIMAL=YES. The savings are larger for areas with more records, compared to areas with fewer records. Areas with no records or few records might see a small saving or a small increase. A MINIMAL=YES RETIX cannot run after the INIT of the IXX. It can run after the INIT of a Multi-Dataset Index other than the IXX, such as I2. Using MINIMAL= requires that the tables in the area all have their URI index set to loaded. The CXX REPORT must therefore not have an INDEX LOADED status of NO.
Successful Execution Requirements and Controls
Environmental
Requirements
when MULTUSE=NO
  • The database may
    not
    be open for update anywhere.
  • The database may
    not
    be open in any MUF for read.
  • The database may
    not
    have any table in
    unloading
    status.
Environmental
Controls
when MULTUSE=NO
  • The database will be opened for update.
  • The utility has no knowledge about current ACCESS database or area status.
  • The utility sets no ACCESS database or area status.
Environmental
Requirements
when MULTUSE=YES
  • The database may or may not be open for update in the connected MUF.
  • The database may
    not
    be open for update in another MUF or Single User job.
  • The database may
    not
    have any table in
    unloading
    status.
  • The database ACCESS must be set OPTIMIZE and WRITE/UTLTY.
  • The area ACCESS must be set WRITE/UTLTY.
  • The area may have no table in an open User Requirements Table.
  • The area may
    not
    have any other MULTUSE=YES utility executing.
  • The area must be closed.
  • When MULTUSE=YES, this function will not be allowed against a Data Sharing MUF if more than one is executing.
Environmental
Controls
when MULTUSE=YES
  • The database will be opened by MUF for update.
  • The utility sets ACCESS area OFF (utility set).
  • The utility sets RETIX executing this MUF this database or area.
Successful Execution Requirements and Controls
Environmental
Requirements
when MULTUSE=NO, SIMPLIFY NO, or MUF down
  • The database may
    not
    be open for update anywhere.
  • The database may
    not
    be open in any MUF for read.
  • The database may
    not
    have any table in
    unloading
    status.
Environmental
Requirements
when MULTUSE=NO, Simplify YES and MUF enabled
  • The database may be open for update but must have no users.
Environmental
Controls
when MULTUSE=NO, SIMPLIFY NO, or MUF down
  • The database will be opened for update.
  • The utility has no knowledge about current ACCESS database or area status.
  • The utility sets no ACCESS database or area status.
Environmental
Controls
when MULTUSE=NO, Simplify YES and MUF enabled
  • The database will be opened for update if not already.
  • All areas are closed if open.
  • The utility executes with every ACCESS database or area status.
  • The utility sets protection to block other incompatible DBUTLTY functions.
Environmental
Requirements
when MULTUSE=YES
  • The database may or may not be open for update in the connected MUF.
  • The database may
    not
    be open for update in another MUF or Single User job.
  • The database may
    not
    have any table in
    unloading
    status.
  • The database ACCESS must be set OPTIMIZE and WRITE/UTLTY.
  • The area ACCESS must be set WRITE/UTLTY.
  • The area may have no table in an open User Requirements Table.
  • The area may
    not
    have any other MULTUSE=YES utility executing.
  • The area must be closed.
  • When MULTUSE=YES, this function is not allowed against a Data Sharing MUF if more than one is executing.
Environmental
Controls
when MULTUSE=YES
  • The database is opened by MUF for update.
  • The utility sets ACCESS area OFF (utility set).
  • The utility sets RETIX executing this MUF this database or area.
Dynamic Extend during RETIX
Dynamic extend of an index area is attempted if requirements are met. If requirements are not met, dynamic extend is not attempted. The requirements are as follows:
  • SORT must be specified as greater than 0 (zero).
  • The data areas must be using the URI option.
How to Use
You can execute this command in either Single User or with the MUF active. Execute the RETIX function using the following command format.
Rebuilding a Full Index Area
►►─ RETIX DBID=
n
─┬────────────────────────┬─┬─────────────────────────┬──────► └─ ,OPTIMIZE= ─┬─ NO ──┬─┘ └─ ,OPTION1= ─┬─ NOF ◄ ─┬─┘ └─ YES ─┘ ├─ '(I)' ─┤ └─ '(U)' ─┘ ►─ ,SORT=
n
─┬───────────────────────┬─┬─────────────┬─┬─────────────┬────────► └─ ,VERIFY= ─┬─ NO ◄ ─┬─┘ └─ ,SORTDD=
c
─┘ └─ ,SORTWK=
n
─┘ └─ YES ──┘ ►┬───────────┬──┬─────────────────────────┬──────────────────────────────────►◄ └─ ,UNIT=
c
─┘ └─ ,SORTDFLT= ─┬─ NO ◄ ─┬─┘ └─ YES ──┘
Rebuilding an Area's Index in Single User
►►─ RETIX DBID=
n
,AREA=
a
─┬────────────────────────┬───────────────────────────► └─ ,OPTIMIZE= ─┬─ NO ──┬─┘ └─ YES ─┘ ►─┬─────────────────────────┬─ ,SORT=
n
─┬───────────────────────┬────────────► └─ ,OPTION1= ─┬─ NOF ◄ ─┬─┘ └─ ,VERIFY= ─┬─ NO ◄ ─┬─┘ ├─ '(I)' ─┤ └─ YES ──┘ └─ '(U)' ─┘ ►─┬─────────────┬─┬─────────────┬─┬───────────┬─┬───────────────┬────────────► └─ ,SORTDD=
n
─┘ └─ ,SORTWK=
n
─┘ └─ ,UNIT=
c
─┘ └─ ,MULTUSE=NO ─┘ ►─┬────────────────────────┬─────────────────────────────────────────────────►◄ └─ ,MINIMAL= ─┬─ NO ◄ ─┬─┘ └─ YES ──┘
Rebuild an Area's Index Area in MUF
►►─ RETIX DBID=
n
,AREA=
a
,SORT=
n
─ ,MULTUSE=YES ────────────────────────────────► ►─┬─────────────────────────┬─┬─────────────┬─┬─────────────┬────────────────► └─ ,OPTION1= ─┬─ NOF ◄ ─┬─┘ └─ ,SORTDD=
n
─┘ └─ ,SORTWK=
n
─┘ ├─ '(I)' ─┤ └─ '(U)' ─┘ ►─┬───────────┬─┬────────────────────────┬───────────────────────────────────►◄ └─ ,UNIT=
c
─┘ └─ ,MINIMAL= ─┬─ NO ◄ ─┬─┘ └─ YES ──┘
Command
  • RETIX
    Invokes the function to rebuild a data space index or Index Area.
Required Keywords
  • DBID=
    Specifies the database for which the Index Area is to be rebuilt.
    • Valid Entries:
      DATACOM-ID of the database
    • Default Value:
      (No default)
Optional Keywords
  • ,AREA=
    Identifies the data area or all areas in the database for which the Index Area is to be rebuilt. If this parameter is omitted, all areas are processed.
    • Valid Entries:
      DATACOM-NAME of the area in the database
    • Default Value:
      All areas are processed
  • MINIMAL=
    Specifying MINIMAL=NO (the default) requires the RETIX function to perform everything it did in previous releases. If MINIMAL=YES is specified, however, RETIX does only the minimal actions necessary to correct the key definition index for tables in the area.
    MINIMAL=YES requires SORT= to be specified with a value greater than 0 (zero).
    For key definitions marked not-loaded, only those keys participate in the normal RETIX process.
    For key entries in the index that have no current key definition, the low-level (DXX) blocks of the index are sequentially searched for pointers that need to be deleted.
    MINIMAL=YES is only allowed at the area level (full database processing is invalid), and is only available for areas that are loaded in the URI format. When you specify MINIMAL=YES, make certain that every table in the area is loaded and has its URI index loaded.
    Message DB13283E is related to the use of the MINIMAL= keyword.
    For information about changes in the RETIX report when MINIMAL= is used, see RETIX Report Changes Related to MINIMAL=.
    • Valid Entries:
      YES or NO
    • Default Value:
      NO
  • ,MULTUSE=
    Specifying MULTUSE=YES allows a single area in a database to have its index rebuilt, with no loss of read and update access to other areas in the database. When MULTUSE=YES, the function executes in a new format that is a combination of MUF and Single User modes. The database involved must be able to be opened for update in the MUF address space. The area must have no tables open. If the database is currently open,
    Datacom/DB
    closes this area with the area close (you would need to close the area yourself
    only if
    the database is open and you need the MUF to close it, so that any type of open data set lock can be released, which could be necessary in order to scratch and reallocate the data set). Once started, the function does not allow other tasks to open any tables in the area being processed. Other tables in the involved database, but not in the area, may be open for read or update in the same MUF. With MULTUSE=YES:
    • The area is first opened in MUF for the RETIX.
    • The area is also opened in Single User mode.
    • The data area processing occurs in the utility address space.
    • The key build/sort occurs in the utility address space.
    • The index load/merge occurs in the MUF address space. Packages of index entries (about 32k bytes) are sent from the utility to the MUF.
    When MULTUSE=YES, the SORT= option must be specified but not as zero (0). When MULTUSE=YES, the following options are not allowed to be specified: OPTIMIZE= and VERIFY=.
    When MULTUSE=NO is specified or is allowed to default in the LOAD and RETIX functions, the functions execute with the index and data areas data sets in the DBUTLTY address space.
    For more information on area level DBUTLTY control, see Area Level DBUTLTY Control.
    • Valid Entries:
      NO or YES
    • Default Value:
      NO
  • ,OPTIMIZE=
    Specifies whether the Index entry creation optimization option is to be used. When YES is selected, if this Index Retrieve fails for any reason, reinitialize the Index Area. If any other data areas are already loaded, re-create the Index entries using this function of DBUTLTY, or reload all areas. If you select YES, you must also specify a value in the SORT= parameter.
    • NO
      All Index entries are to be validated as they are created.
    • YES
      All Index entries are not to be completed until the Index retrieve terminates. Selection of this option enhances performance by deferring Index pointer validation and complete Index structure update.
    Do not specify this option if MULTUSE=YES is specified.
  • ,OPTION1=
    (z/OS only)
    Specifies the statistics display option to be passed to the sort program (see the SORT parameter).
    • NOF
      Corresponds to the NOFLAG parameter of the IBM sort. Only critical messages are to be displayed on the console.
    • '(I)'
      Specifies that informational and critical messages are to be displayed on the printer and the console.
    • '(U)'
      Specifies that only critical messages are to be directed to both the printer and the console.
    NOF, '(I)', and '(U)' correspond to the Syncsort MSG options of CC, SC, and CB respectively. The DBUTLTY LOAD function also accepts any 3-character (including the surrounding parentheses) FLAG parameter that can be processed by the IBM sort. See your IBM sort documentation for details.
    • Valid Entries:
      NOF, '(I)', or '(U)'
    • Default Value:
      NOF
  • ,SORT=
    Activates the Index Area (IXX) entry sort option. When you specify a value, no IXX entries are created as the data is retrieved. The required IXX entries are sorted at the end of the data retrieval and merged into the Index Area in ascending sequence. Selecting this option enhances the performance of the Index Retrieve process for large databases, and we recommend it when the number of records exceeds several hundred.
    The SORT option is required when specifying the RETIX function for the full database, or if you specify VERIFY=YES, OPTIMIZE=YES, or MULTUSE=YES. The SORT option is also required when running a DBUTLTY RETIX of a data area that includes any tables with a key larger than 180 bytes.
    This parameter's value specifies the estimated number of entries to be sorted. Sort treats each entry as a record. To determine the number of entries:
    • Count one entry for each key definition for each row in the area.
    • For any key defined with INCLUDE-NIL-KEY=NO, entries which would be low values or blanks are not passed to the sort.
    • If the area is defined with space management option 1, 2, 4, or 5, entries exist for space management.
    • If the area is defined with space management option 3, entries exist for both clustering and space management.
    DBUTLTY passes the value specified for this parameter to the sort routine. Therefore, the value specified for SORT= can affect the amount of runtime used by the sort. If a number is supplied to the sort, the Index Area entries in the Index Area are not deleted by a separate Index Area pass. The Index Area is updated and only the Index Area blocks that reflect change are written. This option is strongly recommended since it can improve performance. The OPTION1= parameter is also highly recommended to obtain warning and critical messages if a problem occurs.
    Do
    not
    specify over 99 million unless you verify that the sort package you are using allows a larger number. Specifying SORT=0 (or any number of zeros) is the same as not specifying the keyword.
    Many features introduced in previous releases require a non-zero value for SORT=, and the following two features that are also blocked during RETIX executions when the sort option is not being used are as follows:
    • On-demand dynamic extend of any Index Area
    • Execution against a database with Multi-Dataset Indexes
    Most user-JCL is expected to already have a non-zero value for SORT= specified. If, however, a user has a LOAD or RETIX function coded in their JCL with SORT=0 specified or defaulted (SORT= not specified at all), this information is added to the history report, and the first time in each MUF execution that an area is opened in a database that has a LOAD or RETIX coded without the sort option, an informational message is generated. The message alerts you to the condition and can therefore help you know to first add the sort option, then see the results.
    Two messages related to using LOAD and RETIX without the sort option are messages DB02809I and DB13128E. For details about these messages, see Messages.
    • Valid Entries:
      0 to 99999999999
    • Default Value:
      (No default)
  • SORTDD=
    Specifies an optional DD statement, provided to the SORT that redirects SYSPRINT-type output from the SYSPRINT DD (used by DBUTLTY) to this optional DD statement.
    • Valid Entries:
      printed output directed to optional DD statement
    • Default Value:
      printed output directed to SYSPRINT, intermixed with DBUTLTY output
  • ,SORTDFLT=
    (z/OS only)
    Specify SORTDFLT=YES when you want the DBUTLTY SORT= value to be overridden with what is expected to be a more accurate number.
    SORTDFLT= can be set to YES or NO (the default). If SORTDFLT=YES, the SORT= value is required to be greater than zero.
    The keyword, SORTDFLT= allows the SORT value, when specified as greater than zero (0) for a RETIX to be overridden with an estimate expected to be more accurate. SORTDFLT= can only be used in conjunction with the SORT= keyword. When set as SORTDFLT=YES, the RETIX uses the current CXX count of records and definition to calculate a value for the SORT= override. If the count is zero (no records) for all tables to be loaded, the SORT=n value is used and not overwritten. Verify that it is in the appropriate range to allow a successful RETIX.
    The record count and the number of key definitions per table are multiplied for each table to be loaded, and the products are summed as the estimate for record index entries. Based on the area space management (DSOP) option, additional counts are added to verify a successful sort.
    When using this DBUTLTY RETIX keyword, take into consideration that the number of records in a table after a MUF outage is likely to be inaccurate.
    • Valid Entries:
      NO or YES
    • Default Value:
      NO
  • ,SORTWK=
    Specifies the number of sort work areas. The parameter is always edited, but is only used when the SORT= parameter is specified as not zero
    Do not specify a 2-digit number unless the sort package you are using allows a 2-digit number. Sort packages that do not support a 2-digit number can fail in an unpredictable manner, if you specify a 2-digit number.
  • ,UNIT=
    (z/OS only)
    Specifies the unit name for sort work areas. The parameter is always edited, but is only used when the SORT= parameter is specified as not zero.
    • Valid Entries:
      1- to 8-character unit name
    • Default Value:
      SYSDA
  • ,VERIFY=
    Verifies if the Index Area and data area are in exact agreement. It is only used when the SORT= parameter is specified as not zero.
    Specify VERIFY=YES to activate the search for differences in the current Index and the Index being built. If a difference is detected, a Master List dump is produced. Only the first five differences cause a dump. Any additional differences are ignored.
    The dumps have no effect on the execution of the RETIX function which continues to successful completion regardless of the value specified for the VERIFY option.
    If you specify VERIFY=YES, you must specify the SORT option. If you do not specify SORT= when using VERIFY=YES, the first five records found cause a Master List dump whether or not a difference is found between the current Index and the Index being built.
    Do not specify VERIFY=YES if you are using the RETIX function for the full database. Using the VERIFY is also
    not
    recommended for non-URI areas because records that have been moved are dumped, even though this condition is not an error.
    Do not specify this option if MULTUSE=YES is specified.
Example JCL
The following shows the command to rebuild the Index for the DEM area on database 1.
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
.
// EXEC PGM=DBUTLTY,REGION=2M //STEPLIB
See the previous note
.
//CXX DD DSN=cxx.data.set,DISP=SHR Directory data set //* //*
Note: The following sort work allocations are not
//*
required if your sort package can dynamically
//*
allocate sort work areas. Be sure to provide
//*
sufficient space for all keys to be sorted.
//SORTWK01 DD UNIT=disk,SPACE=(alctyp,(prim,scnd)) //SORTWK02 DD UNIT=disk,SPACE=(alctyp,(prim,scnd)) //SORTWK03 DD UNIT=disk,SPACE=(alctyp,(prim,scnd)) //* //SYSIN DD * Command Input RETIX DBID=1,AREA=DEM,OPTIMIZE=YES,SORT=1000 /*
Sample Report for RETIX
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 RETIX AREA=DEM,DBID=1,OPTIMIZE=YES,SORT=1000 FUNCTION=RETIX AREA=DEM DBID=1 OPTIMIZE=YES SORT=00001000
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.
TABLE RECORDS PNC 0 PNM 0 POH 0 POL 4 TOTAL 4
This page of the report shows confirmation that four records were indexed from the requested table.
RETIX Report Changes Related to MINIMAL=
The following RETIX report changes, related to the use of the MINIMAL= keyword, were made.
If MINIMAL=YES is specified, the RETIX report starts with a standard RETIX report with table names and record counts, followed by a section showing tables and key names that have been built. These are the key definitions that were marked "not loaded or NL." If no table was found with a key of "not loaded," the word NONE appears to indicate that fact, as shown in the following sample report:
TABLE RECORDS C01 6,000 TOTAL 6,000 TABLE KEY NAME LOADED C01 ANEWK F01 ZNEWK KEY ID DELETED PROCESSED NONE
This page of the report shows the following:
  • KEY NAME LOADED
    Displays the key name, not in a loaded status, that was processed by this RETIX MINIMAL= option and is now loaded. All Keys that are not loaded participate in the RETIX MINIMAL=YES function.
    In the sample report just shown, TABLE is the
    Datacom
    table that the key is in.
  • KEY ID DELETED PROCESSED
    Displays that if there were keys in the index that were no longer being used, the RETIX MINIMAL= option deletes them and lists their KEYIDs here. The sample just shown lists NONE for this category, but see the following for a different case.
The following sample report shows key ID values that have been in the index (since the last INIT) and currently have no CXX definition (indicating they have been deleted). The key ID is the only value known for a former key. If no key IDs exist, the word NONE is used to indicate that fact, as shown in the previously given sample report.
TABLE RECORDS C01 9 TOTAL 9 TABLE KEY NAME LOADED C01 BNEWK c01 CNEWK KEY ID DELETED PROCESSED 8 10
This page of the report shows that in Single User (the default), all deleted keys are printed. In MUF, the first seven deleted keys are printed, then if more than seven exist, a note of the fact that there are more than seven is printed.
SORTDFLT=YES Report Section for RETIX
If SORTDFLT=YES, a SORTDFLT INFORMATION section occurs in the RETIX report before the table and count information.
The line after the SORTDFLT INFORMATION heading contains the following:
  • Table name
  • Record count from the current CXX
  • Count of key definitions for the table
  • Estimated count of keys to sort
The next line, starting with *DSOP CLUSTER, is the count of index entries possible for clustering (DSOP 3). The count is the same as the number of records, a ‘worst case’ estimate unlikely to occur because it presumes that every record has a unique cluster value, which would make clustering a poor choice for this data area.
The next line, starting with *DSOP PARTIAL, is the count of index entries possible for partial space (DSOP other than 0).The count is the number of data blocks divided by 56, a ‘worst case’ estimate unlikely to occur because it presumes that every block group of 56 blocks has at least one block with space for a record.
The next line, starting with *DSOP EMPTY, is the count of index entries possible for empty blocks that have been used (DSOP 3, 4, and 5).The count is the number of data blocks divided by 56, a ‘worst case’ estimate unlikely to occur because it presumes that every group of 56 blocks has at least one block that has been used but currently contains no records.
The next line, starting with *MOVED RECORDS, is the count of index entries possible for tracking moved records. However, because
Datacom
does not save a count of moved records, this value is currently always 0. Because it is normal for this to not be a large number, the estimate is expected to be reasonable for the RETIX estimate.
The last line is TOTAL and provides the sum of the key estimates from the prior lines. It also provides information about what is overriding the SORT= keyword. If the estimated count of keys from the record input is all zeros, the *DSOP values are not added into the total, and the override does not occur.
If the record counts are significantly incorrect but not zero, the sort uses the estimates as they exist. If the RETIX fails because of low sort count issues, change the JCL to provide a more accurate SORT= value then rerun the LOAD without using the SORTDFLT= keyword.
Example
For this example, database 2 was used. The first statement is a DATASCAN with OPTION=COUNT_SET_RECORDS which recounts the records in the area. it was used only to ensure the counts were accurate in the example and is not intended for normal use.
DATASCAN AREA=DD1,DBID=2,OPTION=COUNT_SET_RECORDS COMM OPTION=CLOSE,DBID=2 INIT DBID=2,AREA=IXX RETIX DBID=2,SORT=99999,SORTDFLT=YES, OPTION1='(I)'
The resulting output is as follows:
SORTDFLT INFORMATION TABLE RECORDS KEYS ESTIMATE AGR 308 8 2,464 ALS 1,624 3 4,872 ARA 48 3 144 ATZ 9 2 18 BAS 23 5 115 DVW 37 2 74 ELM 601 5 3,005 FCN 0 3 0 FIL 86 2 172 FLD 11,845 10 118,450 HSD 202 1 202 JOB 0 2 0 KEY 524 12 6,288 KWC 30 9 270 LIB 0 2 0 MEM 0 2 0 MOD 0 2 0 OD 0 3 0 PCV 0 2 0 PER 2 2 4 PGM 0 2 0 PLN 0 3 0 PNL 0 2 0 PRC 0 3 0 PRT 0 2 0 REL 2,118 7 14,826 RPT 0 2 0 STM 0 3 0 STP 0 2 0 SYS 12 2 24 TRG 0 3 0 TXT 93 2 186 *DSOP CLUSTER 0 *DSOP PARTIAL BLOCKS 48 *DSOP EMPTY BLOCKS 0 *MOVED RECORDS 0 TOTAL 151,162 SORT=00000151162
Information from SORT:
CASO000I SORT FILSZ=E151162,FIELDS=(1,186,BI,A),DYNALLOC=(SYSDA,03) CASO061I RCD IN 00000000,OUT 00149005
In this example, the count of keys sorted is less than the estimate because
Datacom Datadictionary
uses the rarely used NIL key feature to not be part of the index. NIL keys are defined to have a value of either all blanks (x'40') or all binary zeros (x'00').