hi,

when i issue alter table statement add column in db2 v9.5, the tables goes to reorg pending state.

57016(-668)[IBM][CLI Driver][DB2/AIX64] SQL0668N Operation not allowed for reason code "7" on table "Test". SQLSTATE=57016
(0.06 secs)


any advice to avoid this error.

regards

Sanjai

I do not think that you can avoid this error.

If you do a reorg (REORG TABLE XXX) after the alter on this table its going to be ok!

hi,

thanks for your reply. But in 8.2 we don't face this issue. In db2 9.5 only this reorg pending issue is coming. is this a kind of bug or is it necessary to do reorg after the alter table.

pls advice.

regards

sanjai

I dont see a issue. which FP you are on?

$ db2 "alter table course_detail add column c5 varchar(100)"
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from course_detail"

1
-----------
4567960

1 record(s) selected.

$ db2level
DB21085I Instance "db2inst5" uses "64" bits and DB2 code release "SQL09050"
with level identifier "03010107".
Informational tokens are "DB2 v9.5.0.0", "s071001", "AIX6495", and Fix Pack
"0".
Product is installed at "/opt/IBM/db2/V9.5".


--------------------------

I think we need to see your alter command. I have a feeling that you probably did something there that caused the table to go to reorg pending.
Dave

RTFM page 75 and 76 of the SQL Ref 2

There is more.

The maximum number of REORG-recommended alters have been
performed. Up to three REORG-recommended operations are allowed
on a table before a reorg must be performed, to update the tables
rows to match the current schema.

I just did 3 alters and were able to select after each one of them. However, forth update does not work until you do a reorg.

Really?

Reorgs are required only in certain conditions. I had this with one database almost all tables needed REORG (93 otu of 107).

In another database we did not need even one REORG. ( 104 tables were altered)

DB2V9.5 FP 4 on Win 2003 server

I did not get any clue why ?

Any body finds reason , I need to know.

DBFinder

Have you read what I said in post#6?

Yes I did. Thanks for that.

I hope you remember

Mass Table Structure Modification


Between june 7 and 10, I did experiments. In POS 117 tables were modified one column each, There were no need of REORGS.

On GAMING 204 tables were modified where script REORGed 187 tables which took about 72 minutes to complete.

This was practical on same machine, same SERVER but different DB. The requirement of REORGs : I still feel that this cannot be a co-incident.
I am able (willing) to do more practical, this time my mirror is matched with prod server (memory and storage wise).

I remember you helped me at that time, Do you feel like suggesting me (something) for doing this test. I will do it on Monday and bring results back for all.

Thanks Cougar8000 for your time.

DBFinder

According to the manual and as Cougar indicated, you can perform up to three alter table operations before db2 will force you to reorg the table. The manual also states that changing a column from char(n) to varchar(n+x) will set "reorg recommended" to yes.

My test results confirmed that the info in the manual is correct. What the manual doesn't mention is that selects won't be allowed only if the table has index(es) and db2 needs to perform index scan.

The information I found regarding selects:
"Table scans are allowed on tables which are in reorg pending state while index scans are prohibited. The plan the optimizer chooses will result in whether the statement will be successful or not. There is no knowledge by the optimizer to avoid particular plans - the table simply has to be reorganized to become fully accessible. The only reason we even allow table scans to be successful is because internally we need to support this in case index recreation is required as a prereq to reorg."

DML is not allowed regardless of whether there are any indexes on the table.


My test results (two tables: t1 and t2; t1 has an index)

test@panipuri /home/test > db2 "create table t1 (c1 int, c2 char (14), c3 char (14), c4 char (14), c5 char (14))"
DB20000I The SQL command completed successfully.
test@panipuri /home/test > db2 "insert into t1 values (1, 'bella', 'bella', 'bella', 'bella')"
DB20000I The SQL command completed successfully.
test@panipuri /home/test > db2 "create index i1 on t1 (c1)"
DB20000I The SQL command completed successfully.

test@panipuri /home/test > db2 "create table t2 (c1 int, c2 char (14), c3 char (14), c4 char (14), c5 char (14))"
DB20000I The SQL command completed successfully.
test@panipuri /home/test > db2 "insert into t2 values (1, 'bella', 'bella', 'bella', 'bella')"
DB20000I The SQL command completed successfully.



test@panipuri /home/test > db2 "select substr(tabname,1,30), reorg_pending from table(sysproc.admin_get_tab_info('TEST','')) as t where reorg_pending='Y'"

1 REORG_PENDING
------------------------------ -------------

0 record(s) selected.



test@panipuri /home/test > db2 "alter table t1 alter column c2 set data type varchar (20)"
DB20000I The SQL command completed successfully.

test@panipuri /home/test > db2 "select substr(tabname,1,30), reorg_pending from table(sysproc.admin_get_tab_info('TEST','')) as t where reorg_pending='Y'"

1 REORG_PENDING
------------------------------ -------------
T1 Y

1 record(s) selected.


test@panipuri /home/test > db2 "select count (*) from t1"

1
-----------
SQL0668N Operation not allowed for reason code "7" on table "test.T1".
SQLSTATE=57016


test@panipuri /home/test > db2 "alter table t2 alter column c2 set data type varchar (20)"
DB20000I The SQL command completed successfully.

test@panipuri /home/test > db2 "select substr(tabname,1,30), reorg_pending from table(sysproc.admin_get_tab_info('TEST','')) as t where reorg_pending='Y'"

1 REORG_PENDING
------------------------------ -------------
T1 Y
T2 Y

2 record(s) selected.

test@panipuri /home/test > db2 "select count (*) from t2"

1
-----------
1

1 record(s) selected.


test@panipuri /home/test > db2 "alter table t1 alter column c3 set data type varchar (20)"
DB20000I The SQL command completed successfully.
test@panipuri /home/test > db2 "alter table t1 alter column c4 set data type varchar (20)"
DB20000I The SQL command completed successfully.
test@panipuri /home/test > db2 "alter table t1 alter column c5 set data type varchar (20)"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20054N The table "test.T1" is in an invalid state for the operation.
Reason code="23". SQLSTATE=55019



test@panipuri /home/test > db2 "alter table t2 alter column c3 set data type varchar (20)"
DB20000I The SQL command completed successfully.

test@panipuri /home/test > db2 "select count (*) from t2"

1
-----------
1

1 record(s) selected.

...


DML is not allowed:

test@panipuri /home/test > db2 "insert into t2 values (1, 'bella', 'bella', 'bella', 'bella')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0668N Operation not allowed for reason code "7" on table "test.T2".
SQLSTATE=57016

test@panipuri /home/test > db2 "delete from t2"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0668N Operation not allowed for reason code "7" on table "test.T2".
SQLSTATE=57016

So I am getting closer to believe that my test might have been a co-incidence.

The fresh image is restored on first of month. I will do double test to comfirm this.

Thanks Bella for you effort.

DBFinder

I have this same reorg issue in v9.5.
My question: Is there any harm in issuing a "REORG TABLE XXX" command when a table is not in REORG pending state?

No problem. But do it inplace allow write access if you do it in production database.

In my situatrion time required to REORG did matter. If I have to do reorgs on all the tables in list (110 tables) during an outage, this will cost the company in thousands of dollars.

DBFinder

Please note that, if you table is in reorg pending state you can not do an inplace reorg. Hence appending <reorg inplace allow write> to every alter script is not going to help. You need to tackle it as and when it appears. REORG with allow read may lock you table in production environments for quite a considerable amount of time, depending upon the volume of data, and hence is not a recommended option.