DB2 v9.5 ESE on AIX v6.1

after we alter table
alter table DBA.TABLE1 alter column COL1 set data type VARCHAR(1136)

and try to do "select count(*)" on it we get an error
SQL0668N Operation not allowed for reason code "7" on table "DBA.TABLE1". SQLSTATE=57016
which means that table is in reorg pending state.
Is it a bug or something, because documentation (alter table statement) says that change from varchar(n) to varchar(n+x) does not require a reorg?

I did the same on db2 v8.2 and did not have this problem.
thanks in advance

how many alters have you done on the table since the last reorg? This has been covered on the forum here in the past. I, believe, you are allowed 3 'free' alters, then on the 4th you have to pay the piper.
Dave

so I did reorg table's indexes, reorg table, then runstats (just in case), did the change (alter table ... alter column) and got same error.

Then does the increase in length exceed your pagesize? Otherwise, I am kind of stumped. Can you share the DDL for table, pre alteration?
Dave

it does not (about 2000 bytes) and table is in USERSPACE1 tablespace. here is the columns:


CREATE TABLE "DBA "."TABLE1" (
"ACC" CHAR(26) NOT NULL ,
"PROD" INTEGER NOT NULL ,
"AAA_ID" INTEGER NOT NULL ,
"BB_ID" CHAR(3) NOT NULL ,
"BBBBBB_ID" CHAR(13) NOT NULL ,
"XXX_ID" INTEGER NOT NULL ,
"MMML_" SMALLINT NOT NULL ,
"GGG_TYPE" INTEGER NOT NULL ,
"KKK_TYPE" INTEGER NOT NULL ,
"CCC_ID" INTEGER NOT NULL ,
"LKJLKJ_" SMALLINT NOT NULL ,
"LKJLJKERSED_" SMALLINT NOT NULL ,
"DLKJLKJSABLE_" SMALLINT NOT NULL ,
"DLKJLKJAL_PRIME" SMALLINT NOT NULL ,
"RRRRRRR_" CHAR(31) NOT NULL ,
"REF_AAA" CHAR(31) NOT NULL ,
"EFFECTIVE_DATE" CHAR(12) FOR BIT DATA NOT NULL ,
"POST_DATE" CHAR(12) FOR BIT DATA NOT NULL ,
"ZZZZZZ_UNTIL" CHAR(12) FOR BIT DATA NOT NULL ,
"QQQQ_LKJLK_REF" VARCHAR(144) FOR BIT DATA NOT NULL ,
"AAA_AUTH" VARCHAR(130) FOR BIT DATA NOT NULL ,
"AAA_NOTE" VARCHAR(128) FOR BIT DATA NOT NULL ,
"AAA_DESCRIPTION" VARCHAR(256) FOR BIT DATA NOT NULL ,
"AAA_DATA" VARCHAR(1024) FOR BIT DATA NOT NULL ,
"FAILED_" SMALLINT NOT NULL ,
"UNITS_" DECIMAL(26,12) NOT NULL ,
"STATUS_" INTEGER NOT NULL ,
"PPPRESS_ON_STMTS" SMALLINT NOT NULL ,
"SRC_TYPE" INTEGER NOT NULL WITH DEFAULT 0 ,
"SRC_COMPANY_RELATIONSHIP" SMALLINT NOT NULL WITH DEFAULT 0 ,
"SRC_BBB" CHAR(4) NOT NULL WITH DEFAULT ' ' ,
"SRC_BBBBBBB" CHAR(5) NOT NULL WITH DEFAULT ' ' ,
"SRC_ABABAB" CHAR(16) NOT NULL WITH DEFAULT ' ' ,
"SRC_KBKBKB" CHAR(20) NOT NULL WITH DEFAULT ' ' ,
"KKKK_LENGTH" CHAR(12) FOR BIT DATA NOT NULL WITH DEFAULT ' ' ,
"TTT_ID" INTEGER NOT NULL WITH DEFAULT 0 ,
"TTTTTTT_ID" CHAR(32) NOT NULL WITH DEFAULT ' ' ,
"TTTTT_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
"PROCESSING_STATE" SMALLINT NOT NULL WITH DEFAULT 0 )
IN "USERSPACE1" ;

sorry, had to mask col names.

"AAA_DATA" VARCHAR(1024) FOR BIT DATA NOT NULL is the column i alter to 1136.

found how to fix the problem. i added 'for bit data' to the 'alter table alter column' statement:

instead of
alter table DBA.TABLE1 alter column COL1 set data type VARCHAR(1136)

I use
alter table DBA.TABLE1 alter column COL1 set data type VARCHAR(1136) for bit data

it does change the column as it was for bit data in the first place, but does not require a reorg after the change now.