Check if declared global temporary table exists in

Question

How can I check if a declared global temporary table in DB2 exists or not in the current session?

I need to create the temporary table once for a user session and then be able to insert rows in it each time a report is executed in the case of my application. So I need to delete all the rows from this table when a report is executed for more than the first time and then re-populate it with new rows.

Right now the method creating the temporary table is throwing a 42710 SQLSTATE error the second time it is executed.

Does this statement work for my implementation: DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' SET DGTT_FOUND=1 and how can I use it in Java (executeUpdate() ?)

Answer 1

Depending on your DB2 version, which you conveniently not indicated, you can use the WITH REPLACE option when declaring the temporary table, which will not only succeed even though the table has been previously declared, but will also take care of deleting the previously inserted rows.

DECLARE GLOBAL TEMPORARY TABLE mytab (
  ...
) NOT LOGGED WITH REPLACE

Answer 2

Because declared temporary tables are not defined in the catalog, and they are only visible in your current session (each session could have a different definition of the same temporary table name), you could only try to query the table and analyze the answer.

First, try a

select count(0) from session.myTempTable

If DB2 says it does not exists, then you can define a new table.

If DB2 says it DOES exist, you can create it or drop it, in order to recreate it. I think this is your case, because you got a SQL State 42710, then you can create a handler for you code.

However, handlers can be used in SQL procedures, so you should create a SP, to be called before your query. That SP will check if the table exists, and you will put what you want to do when it exists and when it does not.

References:

Handles - http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0009025.html

Tables - http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054491.html