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.