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
SELECT * FROM DB2INST1.EMPLOYEE
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
SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED
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
Cause:
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.
Resolution:
Restart or terminate the previously failed LOAD operation on
this table by issuing LOAD with the RESTART or TERMINATE option
respectively.
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.
SET INTEGRITY FOR db2inst1.employee IMMEDIATE CHECKED
After these steps your table should be back to normal and is usable.
Microsoft SQL Server and db2 LUW 9 - Monitoring, Administration, Optimization, Tips & Tricks and my experiences.
Wednesday, May 9, 2012
Subscribe to:
Post Comments (Atom)
About Me
- Technology Yogi
- 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.
1 comment:
Great tip!
Helped me get through an upgrade to 10.5 where db2ckupgrade failed due to two tables in load pending state.
Appreciate it!
Post a Comment