Verifying Datacom database index and data areas

Products

• Datacom
• Datacom/AD
• Datacom/DB

Issue/Introduction

If you suspect that you have a problem with your database and think that the index might not match the data, there is a DBUTLTY command called VERINDEX that can help without making the tables or database unavailable to other processing. The VERINDEX function can help ensure that the data rows and their index entries match while the table stays open.

As noted in the Datacom/DB DBUTLTY Reference Guide:

The function is intended to pass through the data and index areas producing a list of the index and data mismatches. You can review the output listing to make a determination of the action to be taken next. If there is only one index and data mismatch, you can attempt to fix it manually by deleting and adding a row again rather than rebuilding the entire index.

Resolution

If a specific table is in question, you can easily set up the command to check from the data rows to the index or from the index to the data rows. Here is an example of the commands to use - first to check form the data to the index, then the index to the data - using table ACE in database 143:

VERINDEX DBID=00143,TABLE=ACE,OPTION=INDEXDATA
VERINDEX DBID=00143,TABLE=ACE,OPTION=DATAINDEX

In addition, you can add another parameter (KEYNAME=kkkkk), if you want to check a specific keyname for this table. Otherwise, all keys will be processed for the table as the default.

It is important to note that when scanning using the DATAINDEX option, individual data rows are locked until all of the keys that refer to this row are processed, then the row is unlocked. When using the INDEXDATA option, there is no locking, so it could be possible that a data row could be deleted or moved (due to compression settings) before the index entry is updated, thereby producing an error. It is recommended to run the job a second time for just that key name in order to determine if a true problem exists or if this was due to a timing issue.

If you want to easily scan all the tables in a database, you must create the VERINDEX commands for each table. For some databases, this could be a time-consuming process to identify and then code the commands. Attached to this case is a sample z/OS job stream that will take a parameter of one or more comma-separated DBIDs and automatically generate and run the commands. This job will:

• Use Rexx to generate database-level report commands and to create a series of SQL commands to get all the table names;
• Run the DBSQLPR program to generate the individual DBUTLTY commands at the table level. The commands will:
  > Run the VERINDEX function for each table in each DBID (both DATAINDEX and INDEXDATA)
  > Produce a DATANE (Native Efficiency) report for each table in each DBID
• Finally, run the DBUTLTY commands generated by the Rexx and DBSQLPR programs

Note that if you have tables with many records and many keys, this process could run for a while. In one test of a table with over 2 million rows and 12 keys for each of those records, the job ran for over 90 minutes, but used only about 4 CPU minutes, as the majority of work was done in the MUF.

Additional Information

For more information about the VERINDEX command please refer to the Datacom Core documentation for this DBUTLTY VERINDEX command.

As always, please contact Broadcom support for Datacom if you have further questions.