-1

Is there any way in DB2 to find temp table from the session ?

I have created a temp table pertaining to session

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE_NAME
(   
        COL_1 VARCHAR(11) NOT NULL,
        COL_2 VARCHAR(10)
) ON COMMIT PRESERVE ROWS;

When I am trying to create query 

select * from sysibm.systables where owner='SESSION' and name='TEMP_TABLE_NAME'

yields 0 rows.

Am I looking at the incorrect table to find temp tables ?

Thanks !

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
user1993412
  • 802
  • 2
  • 15
  • 29
  • I suspect this is the case of an X-Y problem. If you want to access the temp table in the same session that created it, you already know it exists and its name. If you are in a different session, you won't be able to access another session's temp table. What is it that you are trying to achieve? – mustaccio Jun 12 '19 at 18:35

2 Answers2

3

A declared global temporary table ( DGTT) will not appear in the catalog, this is the design - so you will not find a DGTT in sysibm.systables. A DGTT cannot be used by any other program except the one that declares it - it is specific to that session, hence there's no value to having it in the catalogue.

If you are using Db2 for z/OS (v10 or higher), or Db2-LUW, you may need instead, a "CREATED global temporary table" (CGTT) which uses a different syntax create global temporary table ... These are catalogued, but you need relevant permissions to create them.

See the Db2-LUW documentation. or for Db2 for z/OS here.

mao
  • 11,321
  • 2
  • 13
  • 29
0

Look at the SYSIBMADM.ADMINTEMPTABLES administrative view.
If you want to see all the DGTTs created in your session, then:

SELECT TABNAME
FROM SYSIBMADM.ADMINTEMPTABLES
WHERE TEMPTABTYPE='D'
AND APPLICATION_HANDLE=mon_get_application_handle();
Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16