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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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



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 '14 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 '14 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 '14 at 22:20
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 '14 at 21:10
Awesome! Nice to have an iSeries DB2 person on the site! – Chris Aldrich Jan 28 '14 at 13:41

Your Answer


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.