DB2 Check Pending Script
转载 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.
最新文章
- Linux内核分析 第二周
- JQuery Mobile入门——设置后退按钮文字(转)
- Java多线程理解
- js函数的几个特殊点
- [ZOJ 1011] NTA (dfs搜索)
- C#轻量级企业事务 - TransactionScope
- 转载:Flash AS3.0 加载外部资源(图片,MP3,SWF)的两种方式
- Linux 网络故障排查
- C语言递归回溯法迷宫求解
- format的应用
- 即时通讯软件openfire+spark+smack
- 动态游标(例如表名作为参数)以及动态SQL分析
- Java进阶(二十六)公司项目开发知识点回顾
- iframe中的a标签电话链接不能正常打开
- WebWorker与WebSocket实现前端消息总线
- python,可变对象,不可变对象,深拷贝,浅拷贝。
- redux 入门
- windows网络编程中文 笔记(二)
- 初识gispro
- 五、angularjs在进入界面前加载数据