Search code examples
db2temp-tables

How to list declared temporary tables in DB2

You can created session temporary tables in DB2 using the DECLARE GLOBAL TEMPORARY TABLE statement. Is there any way to list all the tables that currently exist in the session's SESSION schema? Note declared global temporary tables do not exist in SYSIBM.SYSTABLES.

Solution

  • The short answer (as far as I know) is no. Declared Global Temporary Tables (DGTT) do not show up in the catalogs (as you mentioned).

    Created Global Temporary Tables do, but they are slightly different (have a look at this other Stack Overflow answer of mine for differences).

    If you simply need to know if the table exists or not (for example, if you're trying to create the DGTT later in an application), you could use a CONTINUE HANDLER:

    DECLARE CONTINUE HANDLER FOR SQLSTATE '42710' SET DGTT_FOUND=1;

    Depending on your platform, the SQLSTATE keyword may be optional.