DB2 on the Mac arrives
Antonio Cangiano just posted that DB2 Express-C for 64-bit Intel Mac OS X 10.5 (Leopard) is now available for download from IBM.
Stay tuned to his site for more details.
Listing DB2 triggers, routines, indexes and keys
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
FROM SYSIBM.SYSTRIGGERS
WHERE SCHEMA = ‘[SCHEMA]‘
ORDER BY NAME
Viewing stored procedures and UDFs: P = Procedure, F = Function
FROM SYSIBM.SYSROUTINES
WHERE ROUTINESCHEMA = ‘[SCHEMA]‘
ORDER BY ROUTINENAME
Viewing indexes
FROM SYSIBM.SYSINDEXES
WHERE CREATOR = ‘[SCHEMA]‘
ORDER BY NAME
Viewing foreign keys (by database)
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)
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.