Don’t get caught in check pending

If your database is like mine and you might get have load request that does not come in the right order of foreign key relationship you can get into check pending fiasco.  There might be parents, children, grandchildren and so on, it can become a tiny difficult to create the scripts. Here is an sql I created that helped me generate the SET INTEGRITY sql to get the tables checked :

with gen(tabname, seq) as( select rtrim(tabschema) || ‘.’ || rtrim(tabname)
as tabname, row_number() over (partition by status) as seq
from  syscat.tables
WHERE status=’C’ ),r(a, seq1) as (select CAST(tabname as VARCHAR(3900)), seq
from  gen where seq=1 union all select r.a || ‘,’|| rtrim(gen.tabname), gen.seq
from gen , r where (r.seq1+1)=gen.seq ), r1 as (select a, seq1 from r)
select ‘SET INTEGRITY FOR ‘ || a || ‘ IMMEDIATE CHECKED;’ from r1
where seq1=(select max(seq1) from r1);

This will generate the sqls that can be saved to a file and then run , you may have to run it couple of times to clear all of them.