Monday, May 2, 2016

DB2 LUW - List of tables involved in a referential integrity chain

How do you find all the tables that are connected through referential integrity?

For example, let's assume that I've a table called TABA. This is a parent of TABB and TABB is a parent of TABC and TABC is a parent of TABD and so on. How do I find the all these related tables?

Method 1 - Query SYSCAT.REFERENCES:

db2 "select substr(TABNAME,1,30) as PARENT, substr(CONSTNAME,1,20) as CONSTRAINT, substr(REFTABNAME,1,30) as CHILD, substr(FK_COLNAMES,1,40) as REF_COL from syscat.references where tabname='TABA'"

This will give the child tables of TABA. Run the above query again for the child tables. Continue doing this until the last child table in the chain.

Method 2 - Through db2expl:

Run db2expl on "delete from tabschema.tabname" and parse the output.

db2expln -d lwscomdb -g -q "delete from SCHEMA.TABA" -t | grep -i "Delete:  Table Name"

This will do a db2 explain on the query to delete and then parse the output to get all the table names involved in the referential integrity chain.



My personal preference is Method 2.

Which method do you prefer and why? If you use a different method, please post it as well.

No comments: