IBM Mainframe Forum

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!!

If it is not in the manual it is not available.

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.

I got the correct syntax: ON COMMIT DROP TABLE.

Please refer below link:

http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnsqk18/5.49?ACTION=MATCHES&REQUEST=drop+on+commit&TYPE=FUZZY&SHELF=dsnshka6.bks&DT=20100611105851&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT