Deleting a Global Temporary table datas

**sakumar9** · Feb 13 '08, 06:39 AM

Originally posted by birundha

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

**docdiesel** · Feb 13 '08, 05:20 PM

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 "transactio n logs full" error:

Code:

DECLARE GLOBAL TEMPORARY TABLE
session.mygtt (...)
ON COMMIT PRESERVE ROWS
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):

Code:

LOAD FROM
  emptyfile.txt
OF DEL
REPLACE INTO
  sessin.myGTT ;

Regards,

Bernd

Deleting a Global Temporary table datas

Deleting a Global Temporary table datas

Comment

Comment