DB2 模拟常见的表状态异常以及如何查看异常的表

DB2 模拟常见的表状态异常，包括 set integrity pending, reorg pending, load pending, 不可用等。

1. set integrity pending

db2 "drop table t2023a"
db2 "drop table t2023b"
db2 "drop table t2023c"
db2 "create table t2023a(id int not null primary key)"
db2 "create table t2023b(id int not null primary key)"
db2 "create table t2023c(id int not null primary key)"

db2 "alter table t2023b add  constraint cons1 check (id < 100000000)"
db2 "alter table t2023b add  foreign key(id) REFERENCES t2023a"
db2 "alter table t2023c add  foreign key(id) REFERENCES t2023b"

seq 1 10 > t1.del

db2 "load from t1.del of del replace into t2023a nonrecoverable"
db2 "load from t1.del of del replace into t2023b nonrecoverable"
db2 "load from t1.del of del replace into t2023c nonrecoverable"

这里只所以用3张表，是为了展示表的依赖关系，这时候如果直接对 t2023c 做 set integrity 操作，是解除不了表的状态的

2. load pending

db2 "drop table t2023d"
db2 "create table t2023d(id int not null primary key)"
seq 1 1000000 > t2.del

db2 "load from t2.del of del replace into t2023d nonrecoverable"
Ctrl+C <--中断load作业

3. reorg pending

db2 "drop table t2023e"
db2 "create table t2023e(name char(10))"
db2 "alter table t2023e alter column name set data type char(21)"

4. Unavailable

db2 "drop table t2023f"
db2 "create table t2023f(name char(10))"
db2 +c "alter table t2023f activate not logged initially"
db2 "rollback"

查询状态异常的表SQL如下：

db2 "select varchar(tabschema,40) as tabschema, varchar(tabname,80) as tabname, 'SetIntegrityPending' as status from syscat.tables where STATUS='C'
union select varchar(tabschema,40) as tabschema, varchar(tabname,80) as tabname, 'ReorgPending' as status from SYSIBMADM.ADMINTABINFO where REORG_PENDING='Y'
union select varchar(tabschema,40) as tabschema, varchar(tabname,80) as tabname, 'LoadPending' as status from SYSIBMADM.ADMINTABINFO where LOAD_STATUS='PENDING'
union select varchar(tabschema,40) as tabschema, varchar(tabname,80) as tabname, 'Unavailable' as status from SYSIBMADM.ADMINTABINFO where AVAILABLE='N'
 with ur"
 

TABSCHEMA      TABNAME           STATUS             
-------------- ----------------- -------------------
DB2INST1       T2023A            SetIntegrityPending
DB2INST1       T2023B            SetIntegrityPending
DB2INST1       T2023C            SetIntegrityPending
DB2INST1       T2023D            LoadPending        
DB2INST1       T2023E            ReorgPending       
DB2INST1       T2023F            Unavailable        

  6 record(s) selected.