Db2 reorg pending state

Error

Operation not allowed for reason code “7” on table “IBMCONSOLE.T1”… SQLCODE=-668, SQLSTATE=57016, DRIVER=4.27.25

The table is in the reorg pending state. This can occur after an ALTER TABLE statement containing a REORG-recommended operation. refer

Why

Starting with Db2® Version 9.7, you can run an unlimited number of ALTER TABLE statements within a single unit of work. In most situations, after processing three units of work that include such operations, the REORG TABLE command must be run.

Starting with Db2 Version 10.5 Fix Pack 5, when the table is in a reorg pending state, the data type of a column can be altered only once before the REORG TABLE command must be run. This restriction applies whether the alter operation occurs in a single unit of work or across multiple units of work.

refer

连续三次set data type将会使该表进入reorg pending状态。其实每次set data type都是需要reorg的，换句话说就是Db2可以容忍你两次不reorg，第三次就必须了。

For example:

drop table t1;
create table t1(c1 int, c2 int, c3 int, c4 int);

alter table t1 alter c1 set data type varchar(10);
alter table t1 alter c3 set data type varchar(10);
alter table t1 alter c2 set data type varchar(10);

获取次数

SELECT NUM_REORG_REC_ALTERS from SYSIBMADM.ADMINTABINFO where tabname = 'T1';

其他会进入reorgpending状态的操作

如何判断一个表是否处于reorg pending

SELECT REORG_PENDING FROM SYSIBMADM.ADMINTABINFO where tabname = 'tabname'

ADMINTABINFO KC

如何解除

reorg table schema.tabname