IBM Support

Steps to run db2cleancat when orphan rows were found in your system catalog

Question & Answer


Question

How do I get and run db2cleancat?

Cause

db2ckupgrade failed with SQL1344N Orphan rows found in the system catalogs.
Contact your technical service representative prior to attempting an upgrade.

Answer

Either:
Download db2cleancat from the following link to run under query mode(0). Execute mode(1) requires a service password provided by support. https://www-01.ibm.com/support/docview.wss?uid=swg22013213.
Or:
Open a PMR and request db2cleancat tool from your local IBM support. IBM
support will provide you:

1) db2cleancat tool
2) db2cleancat.bnd
3) service password (this password is valid for next 7 days)

Follow the steps below to run the db2cleancat :

*** Take a database backup before using the tool. This step is for safety reasons as you will use this tool to update the system catalog table.

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

b) Connect to the database
db2 connect to <dbname>

c) Bind the file
db2 "bind db2cleancat.bnd"

d) Set the tool password given to you by IBM support
export DB2SVCPW=<password_provided_by_IBM>
export LIBPATH=<instance path>/sqllib/lib

e) Run the db2cleancat tool

db2cleancat <dbname>
0

If orphan rows are found, confirm next steps with IBM Support. Some occurrences of orphan rows identified with db2cleancat require additional support analysis. Once confirmed by IBM Support, proceed with next step.

db2cleancat <dbname>
1

f) Once the tool is finished, execute db2ckupgrade again to verify all orphan rows were resolved.

Example
/TMP/...@db2inst2->db2 connect to sample
Database Connection Information
Database server = DB2/AIX64 9.5.3
SQL authorization ID = username
Local database alias = SAMPLE
/TMP/... @db2inst2->db2 bind db2cleancat.bnd
LINE MESSAGES FOR db2cleancat.bnd
------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
/TMP/...@db2inst2->./db2cleancat
USAGE: db2cleancat <dbname> option
This tool should only be used when instructed by DB2 support]]
option 0 - Report orphan rows
option 1 - Remove orphan rows
* WARNING: BACKUP DATABASE BEFORE USING THIS TOOL *
/TMP/... @db2inst2->db2cleancat sample 0
*********** SYSCOLDIST *********************
0 row(s) found for SYSCOLDIST
*********** SYSCONSTDEP *********************
0 row(s) found for SYSCONSTDEP
*********** SYSKEYCOLUSE *********************

db2cleancat has completed successfully.
-------------------------
Related Information
If orphan rows are found by db2ckupgrade, but not by db2cleancat 0, another query may be run to find additional orphan rows. Reference technote: Finding orphan rows in database that need to be changed: https://www-01.ibm.com/support/docview.wss?uid=swg22006136

[{"Business Unit":{"code":"BU001","label":"Analytics Private Cloud"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"DB2 Tools - Troubleshooting","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5;11.1","Edition":""}]

Document Information

Modified date:
12 August 2019

UID

swg21984793