Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: tablespace in load pending state after failed load

    We have a tablespace that is stuck in load pending mode after our load failed recently. We have tried restarting and terminating the load using the TERMINATE INTO and RESTART INTO options in the DB2 LOAD command but to no success. We then tried to QUIESCE and un-QUIESCE the tablespace to no success. Just curious as to what fixes you have used to get the tablespace back into a normal state after a failed load? Thanks gurus

    Scott

  2. #2
    Join Date
    Jun 2003
    Posts
    127
    Scott, I had the same problem sometime back. Taking a backup resolved the problem. The tablespace came out of pending state. Like how you did, I tried everything. Still I'm not clear why I had to take a backup to fix the problem.

  3. #3
    Join Date
    Mar 2003
    Posts
    343
    We do the following for failed autoloads and it has worked so far(lucked out? maybe). I hope you can apply this to the load command (which autoloads are essentially doing under the cover) and get it out of load pending. In an MPP environment, prepend the command with db2_all.

    Before loading a table the id acquires a Z-lock on the tablespace. As far as I know, only that id has the ability to re-acquire that lock again (in MPP from the same node from which it initiated the failed load).

    I will update this post wwith where that information is available tomorrow.

    I hope you are aware of this id.

    Connect as that id.

    Quiesce the tablespace for the table in exclusive mode.(db2 quiesce tablespaces for <schema>.<table> exclusive - please verify cmd from cmd ref - use db2_all for MPP)

    db2atld -c <config filename> -terminate

    db2 quiesce tablespaces for <schema>.<table> reset(use db2_all for MPP)

    Hope this helps. Good Luck.

  4. #4
    Join Date
    May 2003
    Posts
    369

    thanks this worked for us

    db2atld -c <config filename> -terminate

    we used the above autloader command to return the tablespace back to a normal state. Note, when you run autoloader DO NOT try to restart a failed autloader script by running the DB2 LOAD command terminate into by itself, as this does not solve the problem. DB2 is finicky and picky and insists that the -terminate string be used with the db2atld -c command for some weird reason. Wish that IBM would re-engineer DB2 to allow more flexibility with their data load utilities!

Posting Permissions

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