1

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.

  • 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
6

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.

  • Works like a charm! – ajeh Jan 27 '14 at 21:10
  • 1
    Awesome! Nice to have an iSeries DB2 person on the site! – Chris Aldrich Jan 28 '14 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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