DB2 Check Pending Script

Reprint 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 tab Les after LOAD or other operations. It's easy-to-generate a check pending script, but the importance of this script was that it builds the sequence in such a Fashion that the dependencies is 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 was that the order of the tables is not as per the dependencies with a result that you May get this error:

db21034e the command is processed as an SQL statement because it is 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'll be put
Into the set Integrity Pending state by the set Integrity statement.
Sqlstate=428a8

You have the 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 was as per dependencies and the above single statement would run check pending comma nd in the right order.

The only limitation are the size of the SET command–based on this script it cannot being larger that 3900 characters. You can increase the size up to 30,000 characters, and if you would need to has System temporary tablespace of 32K, which is isn't available by default.

DB2 Check Pending Script