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

SELECT NAME
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA = ‘[SCHEMA]‘
ORDER BY NAME

Viewing stored procedures and UDFs: P = Procedure, F = Function

SELECT ROUTINENAME, ROUTINETYPE
FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA = ‘[SCHEMA]‘
ORDER BY ROUTINENAME

Viewing indexes

SELECT NAME, CREATOR, USER_DEFINED
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = ‘[SCHEMA]‘
ORDER BY NAME

Viewing foreign keys (by database)

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

Viewing foreign keys (by table)

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]‘

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.

1 comment
  

One Response 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

Leave a Reply