What's new in DB2 V9.7: Transactions can include an increased limit of ALTER TABLE operations



What does this mean ? I don't see any difference between DB2 V9.5 and DB2 V9.7

Any explaination will be appreciated.

I would read that as being allowed to have an unlimited number of ALTER TABLE operations in a single transaction, i.e. more than 3. If the transaction has 3 or more such statements, the table will go into reorg-pending once the transaction is committed. In previous versions, you were limited to at most 3 ALTER TABLE statements in a single transaction - and then you had to do the reorg as well.

I am in version 9.5 and I am executing

db2 "alter table db2perf.department drop column deptname drop column admrdept drop column location drop column mgrno"

It has four drop statements. But it still worked, without performing a reorg. I would appreciate if you could give an example

There is a difference between "a single statement" and "a single transaction". What you have shown is a single statement, not "four drop statements".

The reason I provided that single statement is because, I tested four seperate drop statements in V9.5 and V9.7. After 3 drop statements get executed, fourth one throws the same error in DB2 V9.5 and DB2 V9.7

And the error is:
SQL20054N The table "DB2PERF.DEPARTMENT" is in an invalid state for the
operation. Reason code="23". SQLSTATE=55019


SO I am a bit confused as to what functionality has been changed.

@n_i. I would appreciate if you can provide such an example with single transaction.

Try executing four alter statements with autocommit turned off - this will be considered a single transaction.

You are right. It worked now.

In 9.5 when I tried to issue the fouth alter in a single transaction it failed where as in 9.7 it worked fine.


9.5 :
$ db2level
DB21085I Instance "db2perf" uses "64" bits and DB2 code release "SQL09053"
with level identifier "06040107".
Informational tokens are "DB2 v9.5.0.3", "special_21401", "U818975_21401", and
Fix Pack "3".
Product is installed at "/opt/IBM/db2/V9.5".

(user@host1)/db2udb/db2perf:
$ db2 +c "alter table db2perf.department drop column location"
DB20000I The SQL command completed successfully.
(user@host1)/db2udb/db2perf:
$ db2 +c "alter table db2perf.department drop column admrdept"
DB20000I The SQL command completed successfully.
(user@host1)/db2udb/db2perf:
$ db2 +c "alter table db2perf.department drop column mgrno"
DB20000I The SQL command completed successfully.
(user@host1)/db2udb/db2perf:
$ db2 +c "alter table db2perf.department drop column deptname"
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 "DB2PERF.DEPARTMENT" is in an invalid state for the
operation. Reason code="23". SQLSTATE=55019


9.7 :
$ db2level
DB21085I Instance "db2perf" uses "64" bits and DB2 code release "SQL09071"
with level identifier "08020107".
Informational tokens are "DB2 v9.7.0.1", "s091114", "IP23027", and Fix Pack
"1".
Product is installed at "/opt/IBM/db2/V9.7".

$ db2 +c "alter table db2perf.department drop column location"
DB20000I The SQL command completed successfully.
(user@host2)/db2udb/db2perf:
$ db2 +c "alter table db2perf.department drop column admrdept"
DB20000I The SQL command completed successfully.
(user@host2)/db2udb/db2perf:
$ db2 +c "alter table db2perf.department drop column mgrno"
DB20000I The SQL command completed successfully.
(user@host2)/db2udb/db2perf:
$ db2 +c "alter table db2perf.department drop column deptname"
DB20000I The SQL command completed successfully.

Thanks everyone for the help on previous issue.
Can some one clarify me a different scenario. I am doing the following operations in sequence as seperate transactions.

1. Alter the table to drop the primary key
2. Alter the table to change the data type of one of the column from varchar to double
3. Alter the table to create the foreign key back.


In both V9.5 and V9.7 after doing the second alter, the table goes to reorg pending state and I cannot do the third alter. When I try to do the third alter, the following error comes out.


SQL0668N Operation not allowed for reason code "7" on table
"DB2PERF.DEPARTMENT". SQLSTATE=57016


So, the question is. How is this error different from
SQL20054N The table "DB2PERF.DEPARTMENT" is in an invalid state for the
operation. Reason code="23". SQLSTATE=55019

In both cases, reorg on a table needs to be done after either of the error comes out.

I think it depends on what you're attempting to execute. For example:


$ db2 "create table t1 (c1 int, c2 char (14), c3 char (14), c4 char (14), c5 char (14))"
DB20000I The SQL command completed successfully.

$ db2 "insert into t1 values ('1','2','3','4','5')"
DB20000I The SQL command completed successfully.

$ db2 "alter table t1 alter column c2 set data type varchar (20)"
DB20000I The SQL command completed successfully.

$ db2 "alter table t1 alter column c3 set data type varchar (20)"
DB20000I The SQL command completed successfully.

$ db2 "alter table t1 alter column c4 set data type varchar (20)"
DB20000I The SQL command completed successfully.

$ 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


$ db2 "insert into t1 values ('1','2','3','4','5')"
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.T1".
SQLSTATE=57016

$ db2 "delete from t1"
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.T1".
SQLSTATE=57016

$ db2 "load from /dev/null of del insert into t1"
SQL0668N Operation not allowed for reason code "7" on table "test.T1".
SQLSTATE=57016

As I observed,when table is in reorg pending state,

If we try to alter the structure of the table(executing the alter statements) then we will receive the below error:

SQL20054N The table is in an invalid state for the operation. Reason code="23". SQLCODE: -20054, SQLSTATE=55019

If we try to perform DML operations then we will receive the below error:

SQL0668N Operation not allowed for reason code "7" SQLCODE: -668, SQLSTATE=57016

Even I am not sure how many maximum number of alter statements are allowed on a table in DB2 V 9.7.