The MAINTAIN INDEX utility builds, rebuilds, or deletes one or more indexes in a non-SQL-defined database.
The MAINTAIN INDEX utility builds, rebuilds, or deletes one or more indexes in a non-SQL-defined database.
You can run the MAINTAIN INDEX utility all at once or break it into steps.
This article describes the following information:
To process an index in a segment, you need the privilege DBAWRITE, on all areas of the segment.
►►─── MAINTAIN INDEX ─────────────────────────────────────────────────────────► ►─── IN ─┬─ SEGMENTsegment-name─────┬──────────────────────────────────────► └─ DBNAMEdatabase-name─────┘ ►─── USING subschema-name ─┬─────────────────────────────┬───────────────────► └─ NEWSUBnew-subschema-name─┘ ►─── NOTIFYnotify-record-count─────────────────────────────────────────────► ►─┬──────────────────────┬───────────────────────────────────────────────────► ├─ STEP ─┬─step-name─┘ └─ FROM ─┘ ┌─────────────────────────────────────────────┐ ►─▼─┬─ BUILD ──┬─index-name─────────────────┬─┴────────────────────────────► ├─ DELETE ─┘ │ └─ REBUILDindex-nameFROM ─┬─ INDEX ───┬─┘ ├─ ALLrows ─┤ └─ MEMbers ─┘ ►────────┬────────────────┬───────────────┬──────────────────────┬───────────►◄ └── as SORTEXIT ─┘ └─── REUSE workfiles ──┘
- IN SEGMENTSpecifies the segment containing the index(es) to be processed.
- segment-nameSpecifies the name of the segment.
- IN DBNAMESpecifies the database containing the index(es) to be processed.
- database-nameSpecifies the name of the database.
- USINGSpecifies the subschema that defines the index(es).If processing an index associated with an ASF table, specify the name of the table's default subschema, RUnnnnnn wherennnnnnis the table's definition number preceded by zeros.If processing an ASF index that resides in ASF's definition area, IDMSR-AREA, which specify the IDMSRSSA subschema.
- subschema-nameSpecifies the name of the subschema.
- If building an index, this is the name of the subschema defining the index to be built.
- If deleting an index, this is the name of the subschema defining the index to be deleted.
- If rebuilding an index, this is the name of a subschema describing either the existing index structure or the new index structure.
- NEWSUBSpecifies the subschema that defines one or more indexes to be rebuilt. Use this clause only when rebuilding an index according to a new definition.
- new-subschema-nameSpecifies the name of the new subschema.
- NOTIFYA message is sent to the system console after a specified number of records have been processed in the current step.
- notify-record-countSpecifies the number of records to process before sending a message.By default or if 0 is specified, no message is sent to the system console except the standard message sent at the end of each step indicating the number of records processed during the step.Notify messages are displayed by steps IDMSTABX, IDMSDBL3, and IDMSDBL4 when a notify-record-count is specified.
- STEPDirects the MAINTAIN INDEX utility to execute only one step of the index maintenance process.By default, if you do not specify STEP, all steps are performed.If the specified step is any step except IDMSTABX, all other parameters are ignored. In this case, the information normally provided by the other parameters is obtained from the intermediate work files.
- FROMSpecifies that MAINTAIN INDEX processing should begin at a specified step and complete all remaining steps.If the specified restart step is any step except IDMSTABX, all other parameters are ignored. In this case, the information normally provided by the other parameters is obtained from the intermediate work files.
- step-nameSpecifies the name of the first or only step to execute.The name must be one of the following:
- BUILDFor system-owned indexes only, directs the MAINTAIN INDEX utility to add all member record occurrences to the specified index.Both the index andallassociated areas and files must be defined in the subschema specified in the USING SUBSCHEMA-NAME clause.
- DELETEFor system-owned indexes only, directs the MAINTAIN INDEX utility to:
- Disconnect all members from the specified index
- Remove the SR7 and SR8 records for the index from the database
- REBUILDFor system-owned indexes only, directs the MAINTAIN INDEX utility to rebuild an existing, non-empty index.
- index-nameSpecifies the name of the index to process.
- FROMIdentifies the records to use in rebuilding an index.Use this parameter only with REBUILD.
- INDEXDirects the MAINTAIN INDEX utility to connect only members of the existing index to the new index.
- ALLrowsDirects the MAINTAIN INDEX utility to:
- Sweep the area of the database that contains eligible members
- Connect all eligible member occurrences to the rebuilt index
- MEMbersDirects the MAINTAIN INDEX utility to:
- Sweep the area of the database that contains the member record type
- Determine if record occurrences found, participate in the index and if they do, connect them to the rebuilt index
- Connect all member record occurrences to the rebuilt index (this is the same as the ALLROWS option) for unlinked system-owned indexes only
- as SORTEXITCauses each DBLxstep in the utility to return its input data directly from the preceding sort instead of having the sort write the data to a workfile. This option eliminates one workfile for each sort and saves the I/O it takes to write, then read, the workfile.
- REUSE workfilesCauses each step in the utility to reuse an existing workfile, if possible, when writing its output data, instead of writing to a new one for each step. This reduces the number of workfiles that need to be allocated.
How to submit the MAINTAIN INDEX utility
You submit the MAINTAIN INDEX utility only through the batch command facility. You must run the batch command facility in local mode.
All areas affected by the index must be varied offline.
When to use MAINTAIN INDEX
Use the MAINTAIN INDEX utility to process indexes in a non-SQL-defined database. This includes indexes associated with individual ASF tables and ASF indexes that reside in the IDMSR-AREA; specify the IDMSRSSA subschema.
When not to use MAINTAIN INDEX
To process indexes in an SQL-defined database, use the LOAD or BUILD utility.
Multiple operations in one execution
You can perform maintenance on multiple indexes in one execution of the MAINTAIN INDEX utility. However, you should perform only one operation on an index within the same execution. For example, do not DELETE and BUILD the same index at the same time.
If the subschemas specified in the MAINTAIN INDEX utility reside in a load library, they must not be linked with the reentrant attribute, nor can they reside in the LPA (OS) or SVA (DOS). If the subschemas are loaded from a dictionary load area, these issues are not relevant.
BUILD-option with non-MA indexes
Specifying the BUILD-option will connect every potential member record to the system-owned index. Therefore, if the index is not MANDATORY AUTOMATIC and there are occurrences of the member record that should not participate in the index, do not use the BUILD-option. Instead, use the REBUILD FROM MEMBERS-option.
When rebuilding an index, MAINTAIN INDEX might need a subschema that describes the old index, the new index, or both depending on the changes (if any) being made to the index structure. The following table specifies the REBUILD option and subschema to use based on the function that the rebuild operation is performing:
Reorganize an existing index (for example, after deleting many member occurrences)
Rebuild a damaged index
Modify the following index tuning options:
Change the following index characteristics:
Rebuild an index from all member occurrences without making index changes
Rebuild an index from all member occurrences making index changes
Where there is a choice between using FROM INDEX or FROM MEMBERS, consider the following:
- FROM INDEX is generally more efficient because only the index structure is read, rather than every member record occurrence
- FROM INDEX preserves the order of entries with duplicate key values; FROM MEMBERS or FROM ALLROWS rebuilds the index with duplicate entries in db-key sequence
- FROM INDEX requires that the index exist and be readable
Changing symbolic index values
The following values can be supplied as a symbolic index parameter in the physical area definition:
- The number of entries in an SR8 (INDEX BLOCK CONTAINS)
- The number of pages bottom-level SR8s are displaced from top-level SR8s (DISPLACEMENT)
If these values are changed in the physical definition, you can (if desired) reorganize the index to reflect the new values by rebuilding it using a DMCL containing the updated segment definition.
Changing subarea page range
The page range in which a system-owned index resides can be specified by using a symbolic subarea parameter in the physical area definition. If this value is changed, execute MAINTAIN INDEX twice:
- The first execution must use a DMCL with the old values for the symbolic parameters and specify STEP IDMSTABX to indicate that only the first step of index maintenance is to be performed.
- The second execution must use a new DMCL (with the same name as the old DMCL) and specify FROM SORT3 to indicate that all remaining steps of index maintenance are to be performed.
To ensure that the correct DMCL is used in each case, you can either change load libraries or rename the new DMCL to have the same name as the old DMCL.
Adding or removing indexes
If adding or removing a linked system-owned index or a user-owned index set, you must use RESTRUCTURE to add or remove index and optional owner pointers in the member record.
Adding, removing, or changing the sort key of a sorted index (system- or user-owned) may change the control length of the record. If it does, and the record is compressed or variable in length, you must also use RESTRUCTURE to adjust the control length of the record in the database.
Duplicate index entries
When building an index (or rebuilding an index with the FROM ALLROWS or MEMBERS option), MAINTAIN INDEX stores duplicate index entries in the order in which the corresponding member record occurrences exist in the database (that is, in db-key sequence). When rebuilding an index with the FROM INDEX option, the order of duplicate index entries is maintained in the rebuilt index.
SORTEXIT and FROM/STEP
When using the FROM and STEP options with the SORTEXIT option, each pair of SORT
xsteps are considered to be one step. If either half of the SORT
xis specified on a FROM or STEP option, processing will start with the SORT
nstep and the DBL
xstep will also be executed. For example:
- FROM IDMSDBL3 will start with step SORT3 and will continue to the end.
- STEP SORT3 will run steps SORT3 and IDMSDBL3.
SORTEXIT/REUSE WORKFILE restart considerations
Since SORTEXIT combines each SORT
nstep with the DBL
xstep that follows it, if a failure occurs in the DBL
xstep, a restart (if a restart is possible) must begin with the sort step and the input to the step will be resorted. Non-SORTEXIT mode will take longer to run but can be restarted after the sort in this case. Therefore, if restart time is more critical than normal runtime do not run the utility as a sortexit.
If the REUSE WORKFILE option is used with SORTEXIT, some input workfiles will be used as output files in the same step. Therefore if these two options are used together and a failure occurs, the utility must be restarted from the beginning.
Workfile Considerations for restarting a failed MAINTAIN INDEX
If the MAINTAIN INDEX command fails, depending on the reason for failure, restart the command at the failing step using the "FROM step-name" syntax. You can only restart a step if the input files to that step are intact and valid.
To prepare for a possible restart when running a one-step MAINTAIN INDEX, the Intermediate work files should have a disposition that preserves the data set in the event of an abend, for example, "DISP=(NEW,CATLG,CATLG)."
To restart MAINTAIN INDEX at a particular step, the input files to that step must have a disposition to specify that the files already exist, for example, "DISP=OLD".
To determine which files were input to a given step, see the "Intermediate Work File" tables under "JCL Considerations". Partially created output files should be deleted before restarting the job, and the original disposition should be used in the restart job, for example, "DISP=(NEW,CATLG,CATLG)".
The SYSPCH file contains sort parm information for sort steps. It is an output file to IDMSDBL
nsteps but is not read unless restarting or running in step mode. So during a normal run the SYSPCH file should be treated as a normal output file, for example, "DISP=(NEW,CATLG,CATLG)." However, restarting is not as straightforward. If the previous job failed in an IDMSDBL
xstep, the SYSPCH file was an output file and should be deleted before restarting. But if the failure occurred in a SORT
xstep, the contents of the SYSPCH file should contain the same values that were input to the SORT
xstep. In this case the SYSPCH file should be preserved and defined as a SYS001 input file to the restart step.
When the SORTEXIT option is used, the SORT
xand IDMSDBLx steps are combined. If a failure occurs in this mode, the SYSPCH file should normally be preserved and used as a SYS001 input file to the restart. However, there is a small window at the end of a IDMSDBL
xstep where the SYSPCH file is opened for output and new SORT parameters are written. If the job fails at this point, the entire SORT
xstep must be restarted, but the SYSPCH file will not be valid as a SYS001 input file. In this case, the sort parameters must be recreated by hand or the job must be restarted at an earlier IDMSDBL
xstep if possible. One way to avoid this situation is to run in step mode when running SORTEXIT mode.
The RELDCTL data set is always an input file to the first step of a MAINTAIN INDEX whether being restarted or not.
The steps of MAINTAIN INDEX
The MAINTAIN INDEX utility consists of the following steps, which you can run separately or as a single operation. Each step has an input and output:
Note:The SYS003 record length can be calculated as follows:
(largest sortkey length in the subschema * 2) + 28
Sorts the contents of the SYS003 file and puts the results in SYS004.
SYS004 contains the sorted contents of SYS003
Note:The SYS005 record length is 32.
Sorts the contents of the SYS005 file, and puts the results in SYS006.
SYS006 contains the sorted contents of SYS005
Note:The SYS006 record length is 32.
Fills in prefix pointers for user-owned indexes and linked system-owned indexes.
SYS006 from SORT4
SYSLST contains a summary of the results of the MAINTAIN INDEX operation
The above table describes the input and output files when you execute MAINTAIN INDEX
withoutthe SORTEXIT and REUSE options. For the impact of running MAINTAIN INDEX with these options, see "JCL Considerations" later in this section.
Sort output after each step
If you execute the MAINTAIN INDEX utility a step at a time, you must use the sort parameters to sort the contents of the intermediate work files. You can use your own sort program or IDMSSORT.
Sort the intermediate work files as follows:
File to sort
16 + (2 x
nis the length of the longest sort or CALC key in the subschema
If running in step mode, the sort parameters generated by IDMSTABX and IDMSDBL3 are not sufficient for stand-alone sort programs running under z/VSE. If you want to use your own sort program, you must add "WORK=" parameters to specify more than one sort work file.
In general, the procedure for changing indexes is as follows:
Step 1: Create a new schema and global subschema, if necessary
These steps are not necessary if the only change being made is to the value of symbolic parameters associated with the index.
- Create a new schema that is identical to the original schema.
- Create a global subschema for the new schema with a name that is different from that of any other subschema in the dictionary. Include in the subschema all areas, records, and sets associated with the schema.
- Make the necessary changes to the new schema definition.
- Validate the schema.
- Regenerate the global subschema.
Step 2: Modify the segment and DMCL if necessary
Segment and DMCL modification is necessary only if adding or changing the values of symbolic index or subarea parameters associated with the index or adding a new area in which to store the index.
- Make the appropriate changes in the segment definition. Make sure that subareas and other symbolics are defined appropriately.
- Generate, punch, and link all DMCLs containing the altered segment.
Step 3: Make changes to the index
- Backup the area(s).
- Use the MAINTAIN INDEX utility to change a system-owned index.
- Use a user-written program in conjunction with IDMSTBLU and the MAINTAIN INDEX utility to change a user-owned index.
- Verify the change with IDMSDBAN or a retrieval program, CA OLQ or CA Culprit.
- Back up the altered area(s).
Step 4: Complete the change
If schema changes were necessary:
- Update the original schema in the same way that the copy was changed.
- Regenerate all subschemas associated with the original schema that are affected by the change.
- Recompile all access modules affected by the change, using the ALTER ACCESS MODULE statement with the REPLACE ALL option.
As appropriate, make the new subschemas, DMCL(s), and area(s) available to your runtime environment.
Maintaining user-owned indexes
To build, rebuild, or delete user-owned indexes, you must write a program which calls IDMSTBLU and passes information about the indexes to be operated on and the owner and member record occurrences participating in the indexes.
Once the program executes, complete the operation by executing the MAINTAIN INDEX utility, specifying FROM SORT3. Use the SYSPCH, SYS002, and RELDCTL work files generated by IDMSTBLU as input to the MAINTAIN INDEX utility as SYS001, SYS003, and RELDCTL respectively.
Creating the user-written program
The following considerations apply when writing your program:
- Include in your program the following descriptors:
- The global subschema describing the index
- The owner and member record descriptions
- Descriptions of the IDMSTBLU parameters outlined next
- The general logic of the program should:
As information is passed to IDMSTBLU, it creates work files needed to build, rebuild, or drop the specified indexed sets.
- As the first call to IDMSTBLU, pass the subschema descriptor:CALL IDMSTBLU USING SUBSCTYP.
- For each occurrence of a user-owned indexed set, identify the owner by passing an owner descriptor:CALL IDMSTBLU USING OWNERTYP.If more than one set per owner is to be processed during a single execution of the user-written program, multiple owner descriptors must be passed to IDMSTBLU. For example, to rebuild two indexed sets in which REC-A is owner and REC-B is member, as the second and third calls to IDMSTBLU, for example:CALL IDMSTBLU USING OWNERTYP. :..........+2 REBUILD IXSET-1 REC-A's dbkey CALL IDMSTBLU USING OWNERTYP. :..........+2 REBUILD IXSET-2 REC-A's dbkeyIn this example, subsequent REC-B member descriptors passed to IDMSTBLU should contain two occurrences of set name and owner dbkey information (one occurrence for IXSET-1 and one occurrence for IXSET-2).
- For each record that participates as a member of an indexed set to be processed, pass a member descriptor and the member record occurrence:CALL IDMSTBLU USING MEMBERTYP member-record.
- As the last call to IDMSTBLU, pass the end-of-file descriptor:CALL IDMSTBLU USING EOFTYP.
- Link edit your program with IDMSTBLU. Also, link it with IDMS if your program binds a rununit.
The subschema descriptor identifies the subschema and segment that contain the indexed sets. If you are rebuilding an index and changing its characteristics, the identified subschema and segment must describe the new index definition.
The subschema descriptor also identifies the name of the DMCL to be used during MAINTAIN INDEX execution. It must be the same as the name of the DMCL specified in the SYSIDMS parameter file used to execute the MAINTAIN INDEX utility.
Fullword binary value 1
01 SUBSCTYP. 02 FILLER PIC S9(8) COMP VALUE +1. 02 ssnm PIC X(8) VALUE 'EMPSS01'. 02 segnm PIC X(8) VALUE 'EMPSEG'. 02 dmclnm PIC X(8) VALUE 'NEWDMCL'.
The owner descriptor describes each record that participates as an owner in an indexed set to be processed:
Fullword binary value 2
'BUILD ', 'REBUILD ', 'DELETE ', 'EXTEND '
16-character set name
Fullword binary owner db-key (zero if owner is an SR7 record)
01 OWNERTYP. 02 FILLER PIC S9(8) COMP VALUE +2. 02 OFUNC PIC X(8) VALUE SPACES. 02 OSET PIC X(16) VALUE SPACES. 02 ODBKEY PIC S9(8) COMP SYNC VALUE +0.
The member descriptor describes each record that participates as a member in one or more indexed sets to be processed:
Fullword binary value 3
16-character record name
Fullword binary db-key
Number of sets
Fullword binary value that specifies the number of sets in which the record participates as a member
Repeat the following two fieldsonce for every indexed set in which the record participates as a member:
16-character set name
Fullword binary owner db-key value
For example, the following illustrates a member that participates in user-owned indexed sets:
01 MEMBRTYP. 02 FILLER PIC S9(8) COMP VALUE +3. 02 MREC PIC X(16) VALUE SPACES. 02 MDBKEY PIC S9(8) COMP SYNC VALUE +0. 02 MSETS PIC S9(8) COMP VALUE +2. 02 MSET-INFO OCCURS n TIMES. 04 MSET PIC X(16). 04 MODBKEY PIC S9(8) COMP SYNC.
n represents the number of indexed sets that are actually being processed (for example, those for which an owner descriptor was previously passed to IDMSTBLU and in which MREC participates as a member).
Database record descriptor
The database record descriptor describes each member record type in an indexed set to be processed:
01 COPY IDMS RECORD EMPLOYEE. 01 COPY IDMS RECORD SKILL. 01 COPY IDMS RECORD EXPERTISE. 01 COPY IDMS RECORD JOB.
The end-of-file descriptor serves as an end-of-file indicator:
Fullword binary value -1 (X'FFFFFFFF')
01 EOFTYP. 02 FILLER PIC S9(8) COMP VALUE -1.
When you submit a MAINTAIN INDEX utility through the batch command facility, the JCL to execute the facility must include statements to define:
- The files containing the areas to be processed
- The intermediate work files
- Sort space
For more information about the generic JCL used to execute the batch command facility, see the section for your operating system in this section.
Work file JCL considerations for STEP mode
MAINTAIN INDEX normally runs as a single step but runs as separate steps using the "STEP step-name" syntax. When running in step mode, input files should have dispositions that state the file already exists, for example, "DISP=OLD"
Preserve output files on successful completion but not when the job fails, for example, "DISP=(NEW,CATLG,DELETE)".
See the "Intermediate Work File" table to determine which files are input and which files are output and when they are used.
The RELDCTL file is always input to every step.
The SYSPCH file is created by an IDMSDBLx step and used as input to a SORT
xstep. When used as input, it is defined as SYS001.
Work file record lengths:
- The RELDCTL file is a fixed length file with a record length of 60 bytes.
- The SYSPCH file is a fixed length file with a record length of 80 bytes.
- All SYSxxxfiles are variable length files. The record length can vary from one step to the next, from one job to the next. Do not code an LRECL value in the JCL just code a BLKSIZE value. A BLKSIZE value should be chosen based on the optimal size for the device being used, for example, 1/2 track if disk or 32k if tape.
Intermediate work files
The following tables indicate which work files are created and read by the different utility steps depending on the use of the SORTEXIT and REUSE WORKFILE options.
MAINTAIN INDEX: NOT sortexit mode and NOT reusing workfiles
MAINTAIN INDEX: NOT sortexit mode and REUSING workfiles
MAINTAIN INDEX: SORTEXIT mode and NOT reusing workfiles
MAINTAIN INDEX: SORTEXIT mode and REUSING workfiles
The following example directs the MAINTAIN INDEX utility to rebuild the SKILL-NAME-NDX in the EMPDEMO segment.
maintain index in segment empdemo using empss01 rebuild "skill-name-ndx" from members;
The following command directs MAINTAIN INDEX to rebuild the SKILL-NAME-NDX, to run all steps as a sortexit, and to reuse workfiles:
maintain index in segment empdemo using empss01 as sortexit reuse workfiles rebuild skill-name-ndx from members;
The following listing is generated after successful completion of the MAINTAIN INDEX utility statement in the previous example.
IDMSBCF IDMS Batch Command Facility mm/dd/yy PAGE 1 MAINTAIN INDEX IN SEGMENT EMPDEMO USING EMPSS01 REBUILD "SKILL-NAME-NDX" FROM MEMBERS ; UT010002 BEGINNING PROCESSING FOR STEP IDMSTABX UT012001 IDMSTABX RELEASE nn.n TAPE volser STARTED UT012004 69 INTERMEDIATE RECORDS WERE WRITTEN TO SYS003 UT012006 SYS003 MAXIMUM RECORD SIZE IS 78 UT012007 IDMSTABX RELEASE nn.n PROCESSING COMPLETED UT010003 STEP IDMSTABX HAS COMPLETED SUCCESSFULLY UT010002 BEGINNING PROCESSING FOR STEP SORT3 UT009001 IDMSDBLY RELEASE nn.n TAPE volser SORT STARTED UT009002 69 RECORDS WERE READ FROM SYS003 UT009003 69 RECORDS WERE WRITTEN TO SYS004 UT009004 IDMSDBLY RELEASE nn.n SORT COMPLETED SUCCESSFULLY UT010003 STEP SORT3 HAS COMPLETED SUCCESSFULLY UT010002 BEGINNING PROCESSING FOR STEP IDMSDBL3 UT006001 IDMSDBL3 RELEASE nn.n TAPE volser PROCESSING STARTED UT006007 69 INTERMEDIATE RECORDS WERE READ FROM SYS004 UT006002 68 INTERMEDIATE RECORDS WERE WRITTEN TO SYS005 UT006005 NO DATABASE ERRORS WERE ENCOUNTERED UT006006 IDMSDBL3 RELEASE nn.n PROCESSING COMPLETED UT010003 STEP IDMSDBL3 HAS COMPLETED SUCCESSFULLY UT010002 BEGINNING PROCESSING FOR STEP SORT4 UT009001 IDMSDBLY RELEASE nn.n TAPE volser SORT STARTED UT009002 68 RECORDS WERE READ FROM SYS005 UT009003 68 RECORDS WERE WRITTEN TO SYS006 UT009004 IDMSDBLY RELEASE nn.n SORT COMPLETED SUCCESSFULLY UT010003 STEP SORT4 HAS COMPLETED SUCCESSFULLY UT010002 BEGINNING PROCESSING FOR STEP IDMSDBL4 UT007001 IDMSDBL4 RELEASE nn.n TAPE volser PROCESSING STARTED UT007004 NO DATABASE ERRORS WERE ENCOUNTERED UT007005 NO LOGIC ERRORS WERE ENCOUNTERED UT007002 68 RECORDS WERE READ FROM SYS006 UT007006 IDMSDBL4 RELEASE nn.n PROCESSING COMPLETED UT010003 STEP IDMSDBL4 HAS COMPLETED SUCCESSFULLY UT013001 DATABASE INDEX/ASF TABLE HAS COMPLETED SUCCESSFULLY Status = 0