By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,325 Members | 1,177 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,325 IT Pros & Developers. It's quick & easy.

Deleting a Global Temporary table datas

P: 4
Hi ... Please help as soon as possible...


We have a Global temporary table where we will insert the datas and return the result set to front end by opening the cursor in the stored procedure... We have deletion of GTT at the start of the program.... but we are getting -913 error.. So we thought of commenting it out. If we comment that deletion , then the GTT returns the old datas along with the newly inserted rows.. But as a GTT the datas inside it must be deleted once the session is over(hope am right).. But the datas reside and we are getting that during the next call.. Can anybody give the reason why it happens and hw can we get out of it?
Feb 13 '08 #1
Share this Question
Share on Google+
2 Replies

sakumar9
Expert 100+
P: 127
Hi ... Please help as soon as possible...


We have a Global temporary table where we will insert the datas and return the result set to front end by opening the cursor in the stored procedure... We have deletion of GTT at the start of the program.... but we are getting -913 error.. So we thought of commenting it out. If we comment that deletion , then the GTT returns the old datas along with the newly inserted rows.. But as a GTT the datas inside it must be deleted once the session is over(hope am right).. But the datas reside and we are getting that during the next call.. Can anybody give the reason why it happens and hw can we get out of it?

In your case, since you are returning the resultsets, the rows will be preserved. I'll try to find the reason why you are getting -913 error.

One alternative is you can delete the contents from your GTT instead of dropping it. Incase you do not want any logging of DELETE, you can use TRUNCATE stored procedure. (You can find this stored procedure in SQLLIB/samples/admin_scripts/truncate.db2)


Regards
-- Sanjay
Feb 13 '08 #2

docdiesel
Expert 100+
P: 297
Hi,

if the data remains in the GTT it means the session somehow doesn't terminate.

However, if you need a workaround to delete some rows from that GTT, you'd better define it with the parameter NOT LOGGED. This speeds up things and avoids "transaction logs full" error:
Expand|Select|Wrap|Line Numbers
  1. DECLARE GLOBAL TEMPORARY TABLE
  2. session.mygtt (...)
  3. ON COMMIT PRESERVE ROWS
  4. NOT LOGGED
If you want/need to delete all the rows, you could load the table from /dev/null (*nix) or nul (device "nul" on Win*), respectively, or from an empty file (very fast solution):

Expand|Select|Wrap|Line Numbers
  1. LOAD FROM
  2.   emptyfile.txt
  3. OF DEL
  4. REPLACE INTO
  5.   sessin.myGTT ;
Regards,

Bernd
Feb 13 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.