Wednesday, May 9, 2012

Recovering from a failed LOAD operation in DB2 LUW

Have you ever dealt with situation where you have to bring a table to a normal state from the faild load condition and you don't know what load command the user used ?
When you do a db2 load and if that terminates with error then the table sits in integrity pending state, below are the sql statements and the sysmtoms of the situation

Operation not allowed for reason code "1" on table "db2inst1.employee".. SQLCODE=-668, SQLSTATE=57016, DRIVER=4.8.86

Based on the above error code you attempt

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0668N  Operation not allowed for reason code "3" on table
"db2inst1.employee".  SQLSTATE=57016

SQL0668N - reason code "3" - states that

        The table is in the Load Pending state. A previous LOAD attempt
         on this table resulted in failure. No access to the table is
         allowed until the LOAD operation is restarted or terminated.


         Restart or terminate the previously failed LOAD operation on
         this table by issuing LOAD with the RESTART or TERMINATE option

Caution: below steps will truncate the data in your table.

Recovery Steps:

touch test.del ...just to create some dummy file (zero bytes)
LOAD FROM 'test.del' OF DEL TERMINATE INTO db2inst1.employee .. I did not know what the original load command was.

After these steps your table should be back to normal and is usabe.

1 comment:

Ola Andersson said...

Great tip!
Helped me get through an upgrade to 10.5 where db2ckupgrade failed due to two tables in load pending state.
Appreciate it!

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.