Hi all,

I our Dev the programmer was loading some data on staging server.
He left table in Load Pending State.

He wants to terminate load and use the table.

I do not have the command he used.

1)
How can I bring the table out of Load Pending State.

2)

If Nothing Works I would like to drop the table and recreate it- but this state does not allow for dropv the table


any help will be appreciated.

DBFinder

The best way (and some times the only way) to terminate this "load pending" clean is issuing the same Load statement that was used to load and replace either "REPLACE/INSERT" option by "TERMINATE".

But first you have to find out (if you don't know) which table/tablespace was left pending by using:
1) Find out which tablespace id was left pending by using "db2 list tablespace show detail"
2) then issue the floowing SQL against the database:

SELECT SUBSTR(A.DEFINER,1,15) AS DEFINER, SUBSTR(A.TABSCHEMA,1,15) AS TABSCHEMA,SUBSTR(A.TABNAME,1,15) AS TABNAME, SUBSTR(B.TBSPACE,1,15) AS TBSPACE_NAME FROM SYSCAT.TABLES A, SYSCAT.TABLESPACES B WHERE TABLEID=275 AND B.TBSPACEID=4 AND A.TBSPACEID=B.TBSPACEID;

---
In the above case the tbalespace in question is the number 4 and the oject id (table) inside the tablespace is 275.

Now you can issue teh following (you will have to adjust to your envrionment)

"Load from ...any input file (since you won't be loadning anything) ... of DEL MESSAGES ....any output path... TERMINATE into ...your table in trouble..."

If you want to retry loading you would have to change instead of TERMINATE using RESUME and have to pass a valid input file...

I hope this can help you ( I have done this so many times ) ...

rmarzullo
IBM Certified DB2 9 LUW DBA

Appreciated,
Thanks