0

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.

Joshua Honig
  • 12,925
  • 8
  • 53
  • 75

2 Answers2

3

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.

bhamby
  • 15,112
  • 1
  • 45
  • 66
  • your link is broken – Hogan Feb 11 '19 at 16:43
  • @Hogan, I updated the links, but it appears that the article I referenced is now behind a paywall. I updated it to another Stack Overflow answer of mine that has most of the same information (and another source). – bhamby Aug 29 '19 at 22:40
0

I know this is an old thread; I was looking for this too and found the following query helped:

db2 "select substr(tabname, 1, 20) tabname,
     substr(tabschema,1,10) tabschema, 
     temptabtype,
     tab_organization,
     substr(tbspace,1,12)tbspace
     from 
       table (sysproc.admin_get_temp_tables(sysproc.mon_get_application_handle(),'','')) gtt,
       syscat.tablespaces tbspace where (tabname = 'TEMP_T1' ) 
       and
       gtt.tbsp_id = tbspace.tbspaceid"

Result:

TABNAME              TABSCHEMA  TEMPTABTYPE TAB_ORGANIZATION TBSPACE     
-------------------- ---------- ----------- ---------------- ------------
TEMP_T1              SESSION    D           C                SYSTOOLSTMPS

  1 record(s) selected.

Note that this may be dependant on the Db2 version. This info will only be valid in your session.

Mick
  • 1
  • 3