ADMINTEMPTABLES administrative view and ADMIN_GET_TEMP_TABLES table function - Retrieve information for temporary tables

The ADMINTEMPTABLES administrative view and the ADMIN_GET_TEMP_TABLES table function provide methods to retrieve table attribute and instantiation time information for instances of created temporary tables and declared temporary tables.

Although the catalog views contain table attribute information for created temporary tables, they do not contain this information for declared temporary tables. In addition, the catalog views do not contain table instantiation time information for created temporary tables or declared temporary tables.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

ADMINTEMPTABLES administrative view
ADMIN_GET_TEMP_TABLES table function

ADMINTEMPTABLES administrative view

The ADMINTEMPTABLES administrative view returns table attribute and instantiation time information for instances of created temporary tables and declared temporary tables.

The schema is SYSIBMADM.

Refer to the Information returned for ADMINTEMPTABLES administrative view and ADMIN_GET_TEMP_TABLES table for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the ADMINTEMPTABLES administrative view
SELECTIN privilege on the schema of the ADMINTEMPTABLES administrative view
CONTROL privilege on the ADMINTEMPTABLES administrative view
DATAACCESS authority
DBADM authority
SQLADM authority
ACCESSCTRL authority
SECADM authority

One of the following is required to use the table function:

EXECUTE privilege on the ADMIN_GET_TEMP_TABLES table function
DATAACCESS authority

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

Examples

Example 1: Retrieve table attributes and instantiation time information for all instances of created temporary tables and declared temporary tables currently existing in the database.

SELECT * FROM SYSIBMADM.ADMINTEMPTABLES

Example 2: Determine which connections have an instance of a created temporary table.

SELECT APPLICATION_HANDLE, TABSCHEMA, TABNAME
   FROM SYSIBMADM.ADMINTEMPTABLES 
   WHERE TEMPTABTYPE = 'C'

Example 3: Retrieve table attributes and instantiation time information for all declared temporary tables declared for all the tables instantiated by the user that connected to the database.

SELECT TABSCHEMA, TABNAME, ONCOMMIT, ONROLLBACK, 
      INSTANTIATION_TIME 
   FROM SYSIBMADM.ADMINTEMPTABLES 
   WHERE TEMPTABTYPE = 'D' AND INSTANTIATOR = SYSTEM_USER

ADMIN_GET_TEMP_TABLES table function

The ADMIN_GET_TEMP_TABLES table function returns the same information as the ADMINTABINFO administrative view, but allows you to specify a schema name and a table name.

Refer to the Information returned for ADMINTEMPTABLES administrative view and ADMIN_GET_TEMP_TABLES table for a complete list of information that can be returned.

Syntax

The schema is SYSPROC.

Table function parameters

application_handle

An input argument of type BIGINT that specifies an application handle. If application_handle is specified, data is returned for the specified connection only; if application_handle is NULL, data is returned for all connections.

tabschema

An input argument of type VARCHAR(128) that specifies a schema name.

tabname

An input argument of type VARCHAR(128) that specifies a created temporary table name or a declared temporary table name.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the ADMIN_GET_TEMP_TABLES table function
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Examples

Example 1: Retrieve table attributes and instantiation time information for all instances of the created temporary table DBUSER1.EMPLOYEE for all connections.

SELECT TABSCHEMA, TABNAME, ONCOMMIT, ONROLLBACK, INSTANTIATION_TIME 
   FROM TABLE (SYSPROC.ADMIN_GET_TEMP_TABLES(NULL, 'DBUSER1', 'EMPLOYEE')) 
   AS T

Example 2: Retrieve the instantiation time and table space ID for all instances of user temporary tables for the current connection.

SELECT TABSCHEMA, TABNAME, INSTANTIATION_TIME, TBSP_ID 
   FROM TABLE (SYSPROC.ADMIN_GET_TEMP_TABLES(APPLICATION_ID(), '', ''))
   AS T

Usage notes

If both tabschema and tabname are specified, then information is returned for that specific temporary table only.
If tabschema is specified but tabname is NULL or the empty string ("), then information is returned for all tables in the given schema.
If tabschema is NULL or the empty string (") and tabname is specified, then an error is returned. To retrieve information for a specific temporary table, the table must be identified by both schema and table name.
If both tabschema and tabname are NULL or the empty string ("), then information is returned for all temporary tables for the connection or all connections, depending on the value of application_handle.
If tabschema or tabname do not exist, or tabname does not correspond to a temporary table name, or instances of the identified temporary table do not exist in the database, then an empty result set is returned.

Information returned

Table 1. Information returned for ADMINTEMPTABLES administrative view and ADMIN_GET_TEMP_TABLES
Column name	Data type	Description
APPLICATION_HANDLE	BIGINT	application_handle - Application handle monitor element
APPLICATION_NAME	VARCHAR(256)	appl_name - Application name monitor element
TABSCHEMA	VARCHAR(128)	table_schema - Table schema name monitor element
TABNAME	VARCHAR(128)	table_name - Table name monitor element
INSTANTIATOR	VARCHAR(128)	Authorization ID under which the created temporary table was instantiated or declared temporary table was declared.
INSTANTIATORTYPE	CHAR(1)	U = The instantiator is an individual user
TEMPTABTYPE	CHAR(1)	Temporary table type: C = Created temporary table D = Declared temporary table
INSTANTIATION_TIME	TIMESTAMP	Time at which the created temporary table instance was instantiated or the declared temporary table was declared.
COLCOUNT	SMALLINT	Number of columns, including inherited columns (if any).
TAB_FILE_ID	BIGINT	table_file_id - The file ID (FID) for the table.
TBSP_ID	BIGINT	tablespace_id - An integer that uniquely represents a table space used by the current database.
PMAP_ID	SMALLINT	Identifier for the distribution map that is currently in use by this table.
PARTITION_MODE	CHAR(1)	Indicates how data is distributed among database partitions in a partitioned database system. H = Hashing Blank = No database partitioning
CODEPAGE	SMALLINT	Code page of the object. This is the default code page used for all character columns and expression-generated columns.
ONCOMMIT	CHAR(1)	Specifies the action taken on this table when a COMMIT operation is performed. D = Delete rows P = Preserve rows
ONROLLBACK	CHAR(1)	Specifies the action taken on this table when a ROLLBACK operation is performed. D = Delete rows P = Preserve rows
LOGGED	CHAR(1)	Specifies whether this table is logged. N = Not logged Y = Logged
TAB_ORGANIZATION	CHAR(1)	tab_organization - Data organization in table monitor element