'Check if declared global temporary table exists in current session
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() ?)
Solution 1:[1]
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:
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | AngocA |