DBUTLTY VERINDEX: Why do key counts differ between INDEXDATA and DATAINDEX reports

book

Article ID: 192861

calendar_today

Updated On:

Products

CA Datacom CA Datacom/DB CA Datacom/AD

Issue/Introduction

I am running the CA Datacom DBUTLTY VERINDEX function, and I noticed that the count of entries for a KeyID is different on the INDEXDATA report from the DATAINDEX report.

CXX report for Datadictionary - DBID 2 (partial):

TABLE NAME - AGR                  
OCCURRENCE - AGGREGATE P012       
TABLE ID   -     17               
RECORD LENGTH    -    809 /    821
RECORDS IN TABLE -           1,911
. . .
KEY - NAME   ID   LENGTH  MASTER  NATIVE  UNIQUE  DIRECT  KEY INC  KEY USE
      AGRSA  0202     64   NO      NO      NO       NO      NO     ANY    

KEY - NAME   ID   LENGTH  MASTER  NATIVE  UNIQUE  DIRECT  KEY INC  KEY USE
      AGRSS  0202     64   NO      NO      NO       NO      NO     ANY    

DBUTLTY VERINDEX reports

DataIndex

KEYID   KEYNAME   MAST   MATCH_COUNT   ERROR_COUNT   ST
  202     AGRSA      N          1716             0   LD
  202     AGRSS      N          1749             0   LD

IndexData

KEYID   KEYNAME   MAST   MATCH_COUNT   ERROR_COUNT   ST
  202     AGRSA      N          3465             0   LD
  202     AGRSS      N          3465             0   LD

Environment

Component : CA DATACOM/AD

Component : CA DATACOM/DB

Resolution

When looking at the VERINDEX function output like the above sample, there are different factors that lead to variances in the IndexData vs DataIndex values:

  • On the AGR table, all but one of the secondary keys has Key Include of NO. This attribute shows "whether the key values that are all blanks (X'40') or binary zeros (X'00') are to be included in the CA Datacom/DB index." This means that for these keys, the number of index entries can be less than the number of rows. The report you sent shows that there are actually more entries on the INDEXDATA report for KeyID 202 (3465) than the row count (1911), which is covered in the next point.
  • When you have a shared KeyID (like 202 on this report), the number of entries will actually be higher than the record count because the total entries for all the keys shared will be added together and this same number will be used on the IndexData report for all keys sharing that ID. When running the function from the index to the data (INDEXDATA), since the index has only the KeyID, and not the specific key name, it does not know which key was used to create an entry. Therefore, when you look at the data to index report (DATAINDEX), you can see that KeyID 202 has key AGRSA with 1716 records, and key AGRSS with 1749 records. Together, this KeyID has 1716 + 1749 = 3465 records, shown on the INDEXDATA report.

The result of all this is that you can compare VERINDEX output between multiple runs only on the same report - INDEXDATA to INDEXDATA or DATAINDEX to DATAINDEX, but you cannot necessarily compare INDEXDATA to DATAINDEX.

Additional Information

For more information on the VERINDEX function of DBUTLTY, please refer to the appropriate documentation.

CA Datacom Core 15.1 / DBUTLTY Reference / VERINDEX (Verify Index)

CA Datacom Core 15.0 / DBUTLTY Reference / VERINDEX (Verify Index)

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