Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: reorg pending state after altering table

    DB2 v9.5 ESE on AIX v6.1

    after we alter table
    alter table DBA.TABLE1 alter column COL1 set data type VARCHAR(1136)

    and try to do "select count(*)" on it we get an error
    SQL0668N Operation not allowed for reason code "7" on table "DBA.TABLE1". SQLSTATE=57016
    which means that table is in reorg pending state.
    Is it a bug or something, because documentation (alter table statement) says that change from varchar(n) to varchar(n+x) does not require a reorg?

    I did the same on db2 v8.2 and did not have this problem.
    thanks in advance
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how many alters have you done on the table since the last reorg? This has been covered on the forum here in the past. I, believe, you are allowed 3 'free' alters, then on the 4th you have to pay the piper.
    Dave

  3. #3
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by dav1mo View Post
    how many alters have you done on the table since the last reorg? This has been covered on the forum here in the past. I, believe, you are allowed 3 'free' alters, then on the 4th you have to pay the piper.
    Dave
    so I did reorg table's indexes, reorg table, then runstats (just in case), did the change (alter table ... alter column) and got same error.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Then does the increase in length exceed your pagesize? Otherwise, I am kind of stumped. Can you share the DDL for table, pre alteration?
    Dave

  5. #5
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    Quote Originally Posted by dav1mo View Post
    Then does the increase in length exceed your pagesize? Otherwise, I am kind of stumped. Can you share the DDL for table, pre alteration?
    Dave
    it does not (about 2000 bytes) and table is in USERSPACE1 tablespace. here is the columns:


    CREATE TABLE "DBA "."TABLE1" (
    "ACC" CHAR(26) NOT NULL ,
    "PROD" INTEGER NOT NULL ,
    "AAA_ID" INTEGER NOT NULL ,
    "BB_ID" CHAR(3) NOT NULL ,
    "BBBBBB_ID" CHAR(13) NOT NULL ,
    "XXX_ID" INTEGER NOT NULL ,
    "MMML_" SMALLINT NOT NULL ,
    "GGG_TYPE" INTEGER NOT NULL ,
    "KKK_TYPE" INTEGER NOT NULL ,
    "CCC_ID" INTEGER NOT NULL ,
    "LKJLKJ_" SMALLINT NOT NULL ,
    "LKJLJKERSED_" SMALLINT NOT NULL ,
    "DLKJLKJSABLE_" SMALLINT NOT NULL ,
    "DLKJLKJAL_PRIME" SMALLINT NOT NULL ,
    "RRRRRRR_" CHAR(31) NOT NULL ,
    "REF_AAA" CHAR(31) NOT NULL ,
    "EFFECTIVE_DATE" CHAR(12) FOR BIT DATA NOT NULL ,
    "POST_DATE" CHAR(12) FOR BIT DATA NOT NULL ,
    "ZZZZZZ_UNTIL" CHAR(12) FOR BIT DATA NOT NULL ,
    "QQQQ_LKJLK_REF" VARCHAR(144) FOR BIT DATA NOT NULL ,
    "AAA_AUTH" VARCHAR(130) FOR BIT DATA NOT NULL ,
    "AAA_NOTE" VARCHAR(128) FOR BIT DATA NOT NULL ,
    "AAA_DESCRIPTION" VARCHAR(256) FOR BIT DATA NOT NULL ,
    "AAA_DATA" VARCHAR(1024) FOR BIT DATA NOT NULL ,
    "FAILED_" SMALLINT NOT NULL ,
    "UNITS_" DECIMAL(26,12) NOT NULL ,
    "STATUS_" INTEGER NOT NULL ,
    "PPPRESS_ON_STMTS" SMALLINT NOT NULL ,
    "SRC_TYPE" INTEGER NOT NULL WITH DEFAULT 0 ,
    "SRC_COMPANY_RELATIONSHIP" SMALLINT NOT NULL WITH DEFAULT 0 ,
    "SRC_BBB" CHAR(4) NOT NULL WITH DEFAULT ' ' ,
    "SRC_BBBBBBB" CHAR(5) NOT NULL WITH DEFAULT ' ' ,
    "SRC_ABABAB" CHAR(16) NOT NULL WITH DEFAULT ' ' ,
    "SRC_KBKBKB" CHAR(20) NOT NULL WITH DEFAULT ' ' ,
    "KKKK_LENGTH" CHAR(12) FOR BIT DATA NOT NULL WITH DEFAULT ' ' ,
    "TTT_ID" INTEGER NOT NULL WITH DEFAULT 0 ,
    "TTTTTTT_ID" CHAR(32) NOT NULL WITH DEFAULT ' ' ,
    "TTTTT_TIMESTAMP" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
    "PROCESSING_STATE" SMALLINT NOT NULL WITH DEFAULT 0 )
    IN "USERSPACE1" ;


    sorry, had to mask col names.

    "AAA_DATA" VARCHAR(1024) FOR BIT DATA NOT NULL is the column i alter to 1136.
    Last edited by MarkhamDBA; 08-03-10 at 16:01.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  6. #6
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    found how to fix the problem. i added 'for bit data' to the 'alter table alter column' statement:

    instead of
    alter table DBA.TABLE1 alter column COL1 set data type VARCHAR(1136)

    I use
    alter table DBA.TABLE1 alter column COL1 set data type VARCHAR(1136) for bit data

    it does change the column as it was for bit data in the first place, but does not require a reorg after the change now.
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

Posting Permissions

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