How to display the key corresponding to RID Value

Hi,

When you run CHECK DATA utility to verify the violating the RI constraints, you may come across the following error message:

DSNUKERK - ROW (RID=X'0000000202') HAS NO PARENT FOR TSG10.TES3.CTES3A

Here the hexadecimal value X'0000000202' is the Row Identifier of the key of the child table which violates the RI constraint CTES3A.

If someone tell you to find out the record corresponding key to the RID value, then you can follow this approach.

There is one stand alone utility DSN1PRNT by the help of which you can print the VSAM file data.

RID value is always associated with the index of the table.

Hence by searching the RID value in underlying index dataset( for example DSN2.DSNDBD.DTSG10.X1TES300.I0001.A001)  of violating child table, we can get hold of the key.

Here is the sample JCL: Note: always search hexadecimal value in two double quotes.


//TSG10P JOB ,
// CLASS=L,MSGCLASS=O,NOTIFY=TSG10,MSGLEVEL=(1,1)
//****************************************************************
//*
//* JCL TO PRINT HEXADECIMAL DUMP OF DB2 IMAGE COPY
//*
//****************************************************************
//*
//STEP1    EXEC PGM=DSN1PRNT,
//         PARM='FORMAT,VALUE(''0000000202'')'
//SYSUT1   DD DSN=DSN2.DSNDBD.DTSG10.X1TES300.I0001.A001,DISP=SHR
//SYSPRINT DD SYSOUT=*
//*

The subset of the output looks like below:
===
UNIQUE KEYS FOLLOW:
KEY ENTRY:  IPKMAP(XI)='0038'X
KEY:
8001
RID:
0000000201
KEY ENTRY:  IPKMAP(XI)='003F'X
KEY:
8002
RID:
4000000202
KEY ENTRY:  IPKMAP(XI)='0046'X
KEY:
8005
RID:
4000000203

DSN1994I DSN1PRNT COMPLETED SUCCESSFULLY,  00000005 PAGES PROCESSED

Note:

1) To find out the violating rows you can create the exception table and track those and at the same time you can bring the table to RW mode from Check pending status.

2) Or you can use the REPAIR utility for DUMP/DELETE the violating rows By LOCATE TABLESPACE command.

So do not be afraid of seeing the RID hex values, just play with it by the available utilities


Cheers...
Prakash C Singh
IBM Certified DB2 DBA