Resolving a 'Load Pending' state on a tablespace

How to reset when a tablespace is put into a load pending state (0x0008).

This is a tip for DB2 UDB on AIX. If during a multi-table load process, one of the loads fails, the tablespace

containing the table will be put into a Load Pending state (0x0008). The following simple process will allow you to reset the state and proceed with the remaining loads while you resolve the problem table.

1. Determine which tablespace is causing the problem.

list tablespaces show detail | grep -E "Name|State"

This will list the tablespace that is causing the problem. In addition to this, you will see the object ID and the tablespace ID.

2. Determine the table that has failed

select tabname, tableid, tbspaceid from syscat.tables where tableid=<objectID> and tbspaceid=<tablespaceID>

This will reveal the table that is causing the problem. At this stage, until the load problem is resolved all access to the tablespace will be denied by the DBM. You can either restart the load for the table, or initiate the contents to zero records (this cannot be done by DELETE).

3.1. Create an empty file:

touch dummy

3.2. Simulate a load into the problem table:

load from dummy of del replace into <tablename>

3.3. Update statistics on the table and indexes

runstats on table with distribution and indexes all shrlevel change

This will release the load pending state on the table and the tablespace.

Reader Feedback

Greg D. writes: A really easy way to recover from a tablespace locked in load pending state is to restart the load command that was executing prior to the lock in load pending state. For example: If you were running "load from junk.ixf of ixf replace into schema.tablename" when the tablespace locked, this is most likely due to a DB2 force applications all and a db2stop, or some catastrophic crash. You can restart the load after the database has been restarted by restarting the same command: "load from junk.ixf of ixf replace into schema.tablename"

Parminder S. writes: A couple of days back I was also stuck in a load pending state -- it took me long time to come out of this. I hope by reading this you guys can save your time. If a load is not completed successfully, it has gone into a load pending state, and the tablespace in which this table resides is put into load pending state, you cannot access data from that tablespace. Here's what to do: Issue the LOAD TERMINATE command 2-3 times. It will try to put the tablespace back into an accessible state. Be patient, it takes some time. If this doesn't work, the last option to bring back the tablespace into a normal state is to force all the applications (if you have the privilege), then issue the LOAD TERMINATE command. It will definitely work out.

db2 "load from /dev/null of del terminate into schema.table_name"

Or

db2 force applications (applications id)
db2 "load from /dev/null of del terminate into schema.table_name"

These commands are for Unix boxes.

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free DB2 tips and scripts.
  • Tip contest: Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical DB2 questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: DB2 tips, tutorials, and scripts from around the Web.


 

This was first published in June 2002

Dig deeper on DB2 UDB (universal databases)

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close