Saurabh Agrawal Saurabh Agrawal - 6 months ago 46
SQL Question

DB2: Setting Integrity in parent-child order

I am trying to write a script (shell+DB2) which sets integrity of all tables in database.
I used below dynamic SQL to generate a script and executed it:

db2 -x "select 'SET INTEGRITY FOR '|| TABSCHEMA ||'.'||TABNAME || ' IMMEDIATE CHECKED;' from SYSCAT.TABLES where STATUS='C' and type='T'" > set_integrity.sql


It works fine but the issue is when tables have relationships (parent-child tables).
It gives error SQL3608N "cannot check integrity of child table when parent is in set integrity pending"

Please let me know how I can modify my SQL or write a shell script / stored procedure such that it sets integrity in parent-child order. My final goal is to have zero tables in check-pending / set integrity pending state.

Please help.
Thanks!!

Answer

You can certainly look at SYSCAT.REFERENCES to identify the parent-child order, but you'll end up needing to write a recursive query if you have more than 1 level of dependencies. Not to mention that if you have 2 co-dependent tables (i.e. table1 has a foreign key on table2; and table2 has a foreign key on table 1), it's much more difficult to generate this list.

I find that it's generally easier to just ignore the failed SET INTEGRITY statements and repeat the process until there are no tables left:

db2 -x "select 'SET INTEGRITY FOR '|| TABSCHEMA ||'.'||TABNAME || ' IMMEDIATE CHECKED;' from SYSCAT.TABLES where STATUS='C' and type='T'" > set_integrity.sql

tabcnt=$(wc -l set_integrity.sql)
while [[ ${tabcnt} -gt 0 ]] ; do
    db2 -tf set_integrity.sql

    # look for more tables in check pending state
    db2 -x "select 'SET INTEGRITY FOR '|| TABSCHEMA ||'.'||TABNAME || ' IMMEDIATE CHECKED;' from SYSCAT.TABLES where STATUS='C' and type='T'" > set_integrity.sql
    tabcnt=$(wc -l set_integrity.sql)
done

This isn't pretty or elegant but it works.

Comments