Need information regarding ON COMMIT DROP



IBM's flagship relational database management system

Need information regarding ON COMMIT DROP

Postby upendra_water » Sat Dec 04, 2010 11:41 am

Hi,

I am getting complilation error when trying to drop GLOBAL TEMP TABLE.
I used ON COMMIT DROP option while declaring GLOBAL TEMPORARY TABLE.
I got following error message: DSNHPARS LINE 609 COL 17 INVALID KEYWORD "ON";
I reffered application programming and SQL guide version 9.1 to have a look at the syntax.
On page 447, I observed that there are only 2 options available for GLOBAL TEMPORARY TABLES.
ON COMMIT DELETE ROWS and ON COMMIT PRESERVE ROWS.
So is ON COMMIT DROP option not available with GLOBAL TEMPORARY TABLES?
If it is available what is syntax?
If it is not available, how am I supposed to drop this table? Can I drop it once data is returned to front end by using cursor on this temp table?
Your help is highly appriciated!!
upendra_water
 
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Need information regarding ON COMMIT DROP

Postby NicC » Sat Dec 04, 2010 8:38 pm

If it is not in the manual it is not available.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Need information regarding ON COMMIT DROP

Postby upendra_water » Sun Dec 05, 2010 1:09 am

Thanks NicC. Can you please suggest a way to drop this table when front end commits the transaction?
Currently we follow the below approach:
1) When SP is called, we first declare temp table and check the SQLCODE.
2) If SQLCODE is -601, we drop the table and declare the temp table once again.
3) We insert rows into this table and then return rows to front end using cursor "with return for" clause.

My question is:
1) How to drop this table when the call is successful? Or it is dropped automatically? As per our understanding, the table is not dropped automatically when a call is successfully and it keeps resources holding. Kindly confirm if this is correct.
2) Can we drop this table after rows are returned to front end? Means we can open a cursor to return rows to front end and if the OPEN statement is successful we can drop the table. Kindly let me know if this is a right approach.
upendra_water
 
Posts: 33
Joined: Wed Nov 25, 2009 10:58 pm
Has thanked: 0 time
Been thanked: 0 time



Return to DB2

 


  • Related topics
    Replies
    Views
    Last post