If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Load Terminate ??

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 654
Load Terminate ??

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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2007
Posts: 56
Load Terminate

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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 654
Appreciated,
Thanks
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On