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:
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.
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"
db2 force applications (applications id) db2 "load from /dev/null of del terminate into schema.table_name"These commands are for Unix boxes.
For More Information
- 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