Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: Alter table in DB2 V9.7

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2009
    Posts
    272
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There is a difference between "a single statement" and "a single transaction". What you have shown is a single statement, not "four drop statements".
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jun 2009
    Posts
    272
    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.
    Last edited by blazer789; 03-23-10 at 18:14.

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Try executing four alter statements with autocommit turned off - this will be considered a single transaction.

  7. #7
    Join Date
    Jun 2009
    Posts
    272
    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.

  8. #8
    Join Date
    Jun 2009
    Posts
    272
    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.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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
    Last edited by db2girl; 03-24-10 at 21:15.

  10. #10
    Join Date
    Jan 2011
    Posts
    29

    Reorg Table

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •