75 ways to demystify DB2: #32: Techtip: Understanding Orphan Rows in DB2 system catalogs.

Body

What is an orphan row in DB2 system catalog tables?

Orphan rows in system catalog tables are entries for which corresponding dependent entry in another sytem catalog table is missing

Example:
Let's consider a SAMPLE database which has table DEPARTMENT and view VDEPT.
There will be records in SYSIBM.SYSTABLES for DEPARTMENT and a dependency record in SYSIBM.SYSVIEWDEP for VDEPT

db2 "select creator, name from sysibm.systables where name='DEPARTMENT'"
CREATOR NAME
-----------------------------
SKHARCHE DEPARTMENT

1 record(s) selected.

db2 "select dcreator, dname from sysibm.sysviewdep where bcreator='SKHARCHE' and bname='DEPARTMENT' and DNAME='VDEPT'"
CREATOR DNAME
------------------------------
SKHARCHE VDEPT

1 record(s) selected.

Now, consider the row in SYSIBM.SYSTABLES is missing (due to some cause) then the row in SYSIBM.SYSVIEWDEP for VDEPT will be considered as an orphan row and reported during db2ckupgrade.

How to detect orphan rows problem?

Before you upgrade your databases, it is important to use the db2ckupgrade command to verify that your databases are ready for upgrade. The db2ckupgrade command verifies that a list of conditions is true in order to succeed at the database upgrade. The db2iupgrade also calls the db2ckupgrade command internally. One of the checks done by db2ckupgrade is checking for existence of orphan rows in system catalogs.


This is highlighted in the the db2ckupgrade log ( log file given with -l option of db2ckupgrade ). It will have entries like :

Version of DB2CKUPGRADE being run: VERSION "10.1"

Database: "SAMPLE"

SQL1344N Orphan rows found in the system catalogs. Contact your technical service representative prior to attempting a database upgrade.
DBT5517N The db2ckupgrade utility failed because the database is in an inconsistent state.
DBT5529N The db2ckupgrade utility did not complete successfully. The database cannot be upgraded. The output log file is named "log".

What causes orphan rows?

Its very difficult to know what exactly causes orphan rows unless its caught when it happens. It could be due to a defect in the code causing orphan rows as we saw in past with an APAR IZ23815. Its not possible to ask user to keep running db2ckupgrade at constant intervals to check for orphan rows. Its also not feasible to add checks/validations to monitor orphan rows for every catalog update due to the performance overhead. That being said, for root cause analysis, things to collect are:

- Export the system catalog table data

eg : for tables -
SYSIBM.SYSROUTINES, SYSIBM.SYSTABLES, SYSIBM.SYSDEPENDENCIES, SYSIBM.SYSMODULES, SYSIBM.SYSROUTINEPARMS, SYSIBM.SYSVIEWS, SYSIBM.SYSVIEWDEP, SYSIBM.SYSVERSIONS, SYSIBM.SYSINVALIDOBJECTS

- db2ckupgrade log
- db2support package
 

How to fix orphan rows?

db2cleancat is an internal tool for cleaning orphan rows in the system catalog. You need to run db2cleancat utility first to remove the orphan rows in order to proceed with with the upgrade. Please open a PMR with IBM support to request db2cleancat utility for your DB2 level. The db2cleancat tool includes two files:

- the executable (db2cleancat)
- the bind file (db2cleancat.bnd)

Here  are the instructions to run the tool:
- Back up the database before using the tool. If anything goes wrong, you should restore the database before doing anything else.

a) Place the two following files on the server where you will need to clean orphan entries.

db2cleancat
db2cleancat.bnd

b) Connect to the database

db2 "connect to <database_name>"

c) Bind the file

db2 "bind db2cleancat.bnd"

d) Set tool password given to you from IBM support

export DB2SVCPW=<Password>
// this service password will be provided by IBM Support.

-----------------------------------
Command Syntax:
db2cleancat dbname option

where options are:
0 # reports the orphan rows
1 # removes the orphan rows.
-----------------------------------

e) Check for orphan rows in catalog

db2cleancat <dbname> 0

f) If orphans found, remove with option 1

db2cleancat <dbname> 1

Note:

db2cleancat utility is different for every platform and db2level so be sure to provide exactly 'db2level' output and platform to IBM support when you open the ticket.
 

Recent known issue with db2ckupgrade tool w.r.t reporting false positives for orphan rows

1. APAR IT01225 – Fixed in DB2 v10.5 FP4
False positives were reported for orphan rows in db2ckupgrade when there were invalid objects( Routines in this case).
A query of SYSCAT.INVALIDOBJECTS would show invalidated objects.

2. APAR IT06666 / IT06667- Probable fix in DB2 V10.1 FP5/10.5 FP6
False positives were reported for orphan rows in db2ckupgrade when there were invalid objects( Views in this case).
A query of  SYSCAT.INVALIDOBJECTS would show invalidated objects.