Saturday, July 24, 2010

Know the power of REPAIR utility in DB2

Hello my blog readers...

In this blog, I am going to explain how you can utilize REPAIR utility the most.

Everybody know REPAIR utility is popularly used for resetting the pending states of Index and Tablespaces.



Example 1: 
Everybody know REPAIR utility is popularly used for resetting the pending states of Index and Tablespaces.

//STEP3    EXEC DSNUPROC,UID='SSTRR',                            
//            UTPROC='',SYSTEM='DSN2'                                      
//SYSIN    DD *                                                            
  REPAIR OBJECT                                                            
  SET INDEX (ALL) TABLESPACE DTSG10.SSTRR NORBDPEND                  
  SET TABLESPACE DBTEST1.STEST PART 1 NOAUXCHKP                        
  SET TABLESPACE DBTEST1.STEST PART 4 NOCHECKPEND
/*

Example 2:
Suppose you want to delete a row corresponding to a ROWID mentioned in the referential violation constraint.


REPAIR
  LOCATE TABLESPACE DTSG10.TS1 RID (X'0000000503')
    DELETE




Example 3:
Replacing damaged data and verifying replacement.

* Repair the specified page of table space DTSG10.STEST1, as indicated by the LOCATE clause.
* Verify that, at the specified offset (50), the damaged data (0A00) is found, as indicated by the VERIFY clause.
* Replace the damaged data with the desired data (0D11), as indicated by the REPLACE clause.
* Initiate a dump beginning at offset 50, for 4 bytes, as indicated by the DUMP clause. You can use the generated dump to verify the replacement.

//STEP1    EXEC  DSNUPROC,UID='IUIQU1UH',UTPROC='',SYSTEM='DSN'
//SYSIN DD *
REPAIR OBJECT
  LOCATE TABLESPACE DSN8D81A.DSN8S81D PAGE X'02'
    VERIFY OFFSET 50 DATA X'0A00'
    REPLACE OFFSET 50 DATA X'0D11'
    DUMP OFFSET 50 LENGTH 4

Example 4:
Reporting whether catalog and directory DBDs differ. The following control statement specifies that REPAIR is to compare the DBD for DTSG10 in the catalog with the DBD for DTSG10 in the directory.

REPAIR DBD TEST DATABASE DTSG10


Example 5:
Reporting differences between catalog and directory DBDs. The following control statement specifies that the REPAIR utility is to report information about the inconsistencies between the catalog and directory DBDs for DTSG10. Run this job after you run a REPAIR job with the TEST option (as shown in example 4), and the condition code is not 0. In this example, SYSREC is the output data set, as indicated by the OUTDDN option.

REPAIR DBD DIAGNOSE DATABASE DSN8D2AP OUTDDN SYSREC

Example 6:
Repairing a table space with an orphan row. After running DSN1CHKR on table space SYSDBASE, assume that you receive the following message:

DSN1812I ORPHAN ID = 20 ID ENTRY = 0190 FOUND IN
         PAGE = 0000000024

From a DSN1PRNT of page X'0000000024' and X'0000002541', you identify that RID X'0000002420' has a forward pointer of X'0000002521'.

Repair the table space by taking the following actions:

1. Submit the following control statement, which specifies that REPAIR is to set the orphan's backward pointer to zeros:

      REPAIR OBJECT LOG YES
       LOCATE TABLESPACE DSNDB06.SYSDBASE RID X'0000002420'
        VERIFY OFFSET X'0A' DATA X'0000002422'
        REPLACE OFFSET X'0A' DATA X'0000000000'

Setting the pointer to zeros prevents the next step from updating link pointers while deleting the orphan. Updating the link pointers can cause DB2 to abnormally terminate if the orphan's pointers are incorrect.

2. Submit the following control statement, which deletes the orphan:

      REPAIR OBJECT LOG YES
       LOCATE TABLESPACE DSNDB06.SYSDBASE RID X'00002420'
        VERIFY OFFSET X'06' DATA X'00002521'
        DELETE


Example 7:
Updating version information. The control statement specifies that REPAIR is to update the version information in the catalog and directory for table spaces STEST1, STEST2, and STEST3.

REPAIR VERSIONS example control statement

//STEP1    EXEC DSNUPROC,UID='JUKQU3AS.REPAIR',TIME=1440,        
//         UTPROC='',                                            
//         SYSTEM='SSTR',DB2LEV=DB2A                             
//SYSIN    DD *                                                       
 REPAIR VERSIONS TABLESPACE DTSG10.STEST1                   
  REPAIR VERSIONS TABLESPACE DTSG10.STEST1 2                   
  REPAIR VERSIONS TABLESPACE DTSG10.STEST1 3  

So arm the knowledge of REPAIR Utility and set for the adventure.

Cheers..

Prakash C Singh.
IBM Certified DB2 DBA

Monday, July 5, 2010

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