Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Error with REORG

    Hi,

    I am trying to alter a table. Able to alter the table & after that I performed the REORG. It is working & now I am trying to update the same table.This is the error which I am getting. can some one help me with this.These are the steps which I am following:

    alter table test2 add column id integer not null with default 0

    alter table test2 alter id drop default

    alter table test2 alter id set generated always as identity

    REORG TABLE TEST2

    update test2 set ID = default

    error after the update stmt:

    DB2 SQL Error: SQLCODE=-1822, SQLSTATE=560BD, SQLERRMC=-668;SAMPLE; SQL0668N Operation not allowed for reason code "7" on ta, DRIVER=4.17.30
    Run of routine failed.

    even REORG is happening. when updating it is throwing some error.These are the steps which I am running inside a stored procedure.

    According to my understanding the table is going to reorganize pending state. can some one suggest me how to solve this issue.

    Thanks
    Last edited by HABBIE; 11-24-14 at 03:55.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    REORG is asynchronous. You can issue the command, but it will finish sometime later. You need to wait to do the update until the reorg completes.

    Andy

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I, believe, there are already a few examples of scripts that will check if REORG is complete or sleep for n out there somewhere. You can try googling that.
    Dave

  4. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,
    Your example is not reproducible.
    Which db2 version do you use?
    Run the following statements which return no errors as is or provide your own full example which returns the error.
    Code:
    create table test2 (a int) in userspace1;
    insert into test2 values 1;
    alter table test2 add column id integer not null with default 0;
    alter table test2 alter id drop default;
    alter table test2 alter id set generated always as identity;
    REORG TABLE TEST2;
    update test2 set ID = default;
    Regards,
    Mark.

  5. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Classic reorg is not asynchronous.
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

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