Listing DB2 triggers, routines, indexes and keys

10 December 2008 » DB2, IBM

I recently compared two DB2 databases that should have been identical, but for some reason were not. The foreign keys were missing from one of the instances.

I suspect the keys were lost during a migration when the administrator commented out the constraints in the DDL as a means to avoid errors on import. Unfortunately, the admin never ran the ALTER statements to add them even after all tables were present.

To be sure nothing else was different, I checked for the existence of other database objects to make sure they were not lost as well. DB2 offers a simple way to query for these objects via the SYSIBM and SYSCAT schemas:

Viewing triggers
[code lang=”sql”]
SELECT NAME
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA = ‘[SCHEMA]’
ORDER BY NAME
[/code]

Viewing stored procedures and UDFs: P = Procedure, F = Function
[code lang=”sql”]
SELECT ROUTINENAME, ROUTINETYPE
FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA = ‘[SCHEMA]’
ORDER BY ROUTINENAME
[/code]

Viewing indexes
[code lang=”sql”]
SELECT NAME, CREATOR, USER_DEFINED
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = ‘[SCHEMA]’
ORDER BY NAME
[/code]

Viewing foreign keys (by database)
[code lang=”sql”]
SELECT
SUBSTR(TABNAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTNAME, 1, 20) FK_NAME,
SUBSTR(REFTABNAME, 1, 12) PARENT_TABLE,
SUBSTR(REFKEYNAME, 1, 20) PK_ORIG_TABLE,
FK_COLNAMES
FROM SYSCAT.REFERENCES
ORDER BY TABLE_NAME
[/code]

Viewing foreign keys (by table)
[code lang=”sql”]
SELECT
SUBSTR(TABNAME, 1, 30) TABLE_NAME,
SUBSTR(CONSTNAME, 1, 20) FK_NAME,
SUBSTR(REFTABNAME, 1, 12) PARENT_TABLE,
SUBSTR(REFKEYNAME, 1, 20) PK_ORIG_TABLE,
FK_COLNAMES
FROM SYSCAT.REFERENCES
WHERE TABNAME = ‘[TABLE]’
[/code]

To make sure I don’t have consistency problems in the future, my next step is to put together a short script to automate these queries post install.

  

2 Responses to 'Listing DB2 triggers, routines, indexes and keys'

Subscribe to comments with RSS

  1. Michael Lawson said,

    17 December 2008 at 10:10 am

    :O

    dis post nedz moar commentz

  2. dsaju said,

    19 April 2013 at 4:46 am

    very helpful

Leave a Reply