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.

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值