469,613 Members | 2,012 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,613 developers. It's quick & easy.

Deleting a Global Temporary table datas

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
2 3935
sakumar9
127 Expert 100+
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
297 Expert 100+
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.

Similar topics

5 posts views Thread by Bernard Koninckx | last post: by
6 posts views Thread by Martin Bischoff | last post: by
2 posts views Thread by David Parker | last post: by
2 posts views Thread by chettiar | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.