Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2007
    Posts
    33

    Unanswered: Table goes to reorg pending state - after alter table command in db2 v9.5

    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

  2. #2
    Join Date
    Jul 2009
    Posts
    3
    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!

  3. #3
    Join Date
    Jan 2007
    Posts
    33
    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

  4. #4
    Join Date
    Apr 2004
    Posts
    1

    Alter table 9.5 reorg pending

    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".


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

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 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

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by mioot
    just run reorg for the table
    Really?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Have you read what I said in post#6?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Cougar8000
    Have you read what I said in post#6?
    Yes I did. Thanks for that.

    I hope you remember

    http://www.dbforums.com/db2/1643452-...ication-2.html


    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
    Last edited by DBFinder; 07-25-09 at 21:34.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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
    Last edited by db2girl; 07-25-09 at 23:05.

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  13. #13
    Join Date
    Sep 2009
    Posts
    1
    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?

  14. #14
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  15. #15
    Join Date
    Jun 2010
    Posts
    1

    Red face

    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.

Posting Permissions

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