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

    Answered: tables in pending state

    Hi,

    In one of my procedures I have to alter few tables. I have to add some columns to that table & I have to perform some updates on that table.
    Everything is working fine.
    For the first update table is good. If I tried to update for the second time I m getting the below.

    Error:
    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.

    After this I have to run the Runstats & reorg on that table , then I can able to update the table.So , my process is going on like ds
    1)Add columns to the table
    2)perform update
    3)runstat & reorg

    again i have to go like this
    1)Add columns to the table
    2)perform update
    3)runstat & reorg

    My question is , why the table is going to pending state. What Should I do to stop the table moving into pending state.
    Can someone kindly let me know plz

  2. Best Answer
    Posted by przytula_guy

    "from the commands we can not see which command on which table - be as complete as possible to avoid multi-updates
    according the doc : Altering this attribute for a column requires table reorganization before further table access is allowed.."


  3. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    FIRST : db2level and platform ?
    rc 7 means reorg needed
    publish the alter commands ..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #3
    Join Date
    Jul 2014
    Posts
    294
    Db2 10.5 on windows .

    Alter commands:
    ALTER TABLE '|| p_database ||'.' || p_table || ' ADD COLUMN TQRITRI_1 INTEGER NOT NULL WITH DEFAULT 0
    ALTER TABLE '|| p_database ||'.' || p_table || ' ALTER GAFFK_1DROP DEFAULT'
    ALTER TABLE '|| p_database ||'.' || p_table || ' ALTER KGKAGFK_1 SET GENERATED BY DEFAULT AS IDENTITY

  5. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    from the commands we can not see which command on which table - be as complete as possible to avoid multi-updates
    according the doc : Altering this attribute for a column requires table reorganization before further table access is allowed..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  6. #5
    Join Date
    Jul 2014
    Posts
    294
    Thanks I am able to understand now.

  7. #6
    Join Date
    Sep 2003
    Location
    United States
    Posts
    65
    Provided Answers: 1
    if you are adding multiple columns and don't want to deal with reorgs every time you add a column, you create a brand new table with all the required columns and do data move (insert/select method) and drop the old table (I usually rename the old one)
    >>
    >> Learn PHP/MySQL for free https://www.youtube.com/watch?v=mpQts3ezPVg
    >> Free Web Hosting with PHP, MySQL, Website Builder : http://www.000webhost.com/862861.html

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
  •