DB2 Check Pending Script

q36988

　　转载 http://www.zinox.com/archives/144
　　Thanks to Max Petrenko of DB2 Toronto Lab for sharing a very useful script to remove check pending status from the DB2 tables after LOAD or other operations. It is easy to generate a check pending script, but the importance of this script is that it builds the sequence in such a fashion that the dependencies are taken care automatically.   
A simple approach to remove check pending
　　Generate script using a simple SELECT statement as shown below:
　　CONNECT TO TESTDB;   
SET INTEGRITY FOR "VIKRAM"."DEBUG_TABLE" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."DESTINATION" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."CLASSES" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."CALL_STACKS" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."ERRORS" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."EXCEPTION_TABLE" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."LOG_TABLE" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."MAJOR_STATS" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."SOURCE" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."ERROR_STACKS" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."REGISTERED_STUDENTS" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."STUDENTS" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."RS_AUDIT" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."TABNUM" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."TEMP_TABLE" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."ROOMS" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."TAB1" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."TAB3" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."TMP" IMMEDIATE CHECKED;   
SET INTEGRITY FOR "VIKRAM"."TAB2" IMMEDIATE CHECKED;   
TERMINATE;
　　But, the problem with above approach is that the order of the tables is not as per the dependencies with a result that you may get this error:
　　DB21034E  The command was processed as an SQL statement because it was not a   
valid Command Line Processor command.  During SQL processing it returned:   
SQL3608N  Cannot check a dependent table "VIKRAM.REGISTERED_STUDENTS" using   
the SET INTEGRITY statement while the parent table or underlying table   
"VIKRAM.STUDENTS" is in the Set Integrity Pending state or if it will be put   
into the Set Integrity Pending state by the SET INTEGRITY statement.   
SQLSTATE=428A8
　　You have to run above script iteratively few times to remove tables from check pending status. This is definitely cumbersome.   
A more elegant approach
　　db2 connect to sample   
db2 -tx +w "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)" > db2FixCheckPending.sql   
db2 -tvf db2FixCheckPending.sql
　　A sample output:
　　SET INTEGRITY FOR VIKRAM.ERROR_STACKS,VIKRAM.CLASSES,VIKRAM.CALL_STACKS,VIKRAM.ERRORS,VIKRAM.REGISTERED_STUDENTS,   
VIKRAM.ROOMS,VIKRAM.STUDENTS IMMEDIATE CHECKED;
　　The order of the tables in above script is as per dependencies and the above single statement will run check pending command in the right order.
　　The only limitation is the size of the SET command – based on this script it cannot be larger that 3900 characters. You can increase the size up to 30,000 characters, but in this case you would need to have System Temporary Tablespace of 32K, which is not available by default.