DBUTLTY VERINDEX: Why do key counts differ between INDEXDATA and DATAINDEX reports
Article ID: 192861
Updated On:
Products
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.
Feedback
