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, 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.
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)
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!