DB2 Check Pending Script

by | Nov 14, 2008

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.

You can download the script from here