How can I check using SQL (in a SQL stored procedure) if a temporary table exists?

are you refering GTT
i suspect there would be any info about them on system level tables as they are valid only for session

You can set up an appropriate CONDITION HANDLER for SQLSTATE 42704.

Then you can just issue a select on the temp table.

Andy

yes, it is a global temporary table. I have been browsing though the sys tables and couldn't find anything like it. However, maybe I missunderstand the concept of session but I do connect from a .NET client to call a stored procedure that creates a table and it seems that the table is persistent between calls.
Is there any way that I can check if it exists already so that I don't try to redeclare it? (as that fails).
Also, is there anything wrong in this pattern?
Basically I declare a temp table to create some data, open a cursor with return on it (as I need to return that data to the caller) and therefore I cannot drop the table (as I have an open cursor on it). I thought it would dissapear in between calls but it doesn't.

I actually need to know of its existence when I declare it, not when I select from it.

Your first post did not explain why you wanted to check if it existed.

When you declare it, you can use the clause "WITH REPLACE' which will either create a new one if it does not exist or replace the definition if it exists. All Global Temp Tables will persist until it is either explicitly dropped (DROP TABLE SESSION.MyTempTable), or the connection is terminated.

Andy

that makes sense.
Thanks

Global temp tables are "declared", which indicates that information about those tables is not stored in the system catalog. Things are different with "created" temp tables (which DB2 LUW does not yet provide).

Still, you could first select from it, and it that *fails* all is fine (since the table does not exist) but if it *succeeds* you know that the declaration will fail.