Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What would be the syntax for testing if there is already a global temporary table in DB2 for IBM i 7.1?

I tried this after DECLARE GLOBAL TEMPORARY TABLE:

SELECT * FROM QSYS2.SYSTABLES WHERE SYSTEM_TABLE_SCHEMA = 'QTEMP';

and it returned an empty result set. The permanent tables seem to be included into that view, but not the temporary tables.

Ultimately I need to conditionally drop a temp table or truncate/delete data if it exists.

share|improve this question
    
Why you want to drop the table? –  WarrenT Jan 22 at 23:44
    
That might not be necessary, if only everything worked as documented. We are running i7 SQL stored proc in ASP.NET web app: opening OleDBConnection, calling proc, then closing connection in Finally block. But the temp table stays and when we re-open connection, it exists. IBM docs state it would be deleted when connection closed. Thus I implemented a workaround of dropping table prior to running the proc and ignoring exception. –  ajeh Jan 23 at 14:54
    
QTEMP library is unique to each job. Your job is not ending when you close the connection. Your ASP.NET server may be using connection pooling, and your server is keeping the connection open for the next person, and if the connection to the IBM i server job is reused then you won't get a new QTEMP. –  WarrenT Jan 25 at 22:20

1 Answer 1

up vote 5 down vote accepted

Add WITH REPLACE to the end of your DECLARE GLOBAL TEMPORARY TABLE statement. This will create your table in QTEMP regardless of whether one already exists or not.

share|improve this answer
    
Works like a charm! –  ajeh Jan 27 at 21:10
1  
Awesome! Nice to have an iSeries DB2 person on the site! –  Chris Aldrich Jan 28 at 13:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.