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.
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.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
SELECT * FROM SYSIBMADM.ADMINTEMPTABLES
SELECT APPLICATION_HANDLE, TABSCHEMA, TABNAME
FROM SYSIBMADM.ADMINTEMPTABLES
WHERE TEMPTABTYPE = 'C'
SELECT TABSCHEMA, TABNAME, ONCOMMIT, ONROLLBACK,
INSTANTIATION_TIME
FROM SYSIBMADM.ADMINTEMPTABLES
WHERE TEMPTABTYPE = 'D' AND INSTANTIATOR = SYSTEM_USER
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.
>>-ADMIN_GET_TEMP_TABLES--(--application_handle--,--tabschema--,--tabname--)-><
The schema is SYSPROC.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
SELECT TABSCHEMA, TABNAME, ONCOMMIT, ONROLLBACK, INSTANTIATION_TIME
FROM TABLE (SYSPROC.ADMIN_GET_TEMP_TABLES(NULL, 'DBUSER1', 'EMPLOYEE'))
AS T
SELECT TABSCHEMA, TABNAME, INSTANTIATION_TIME, TBSP_ID
FROM TABLE (SYSPROC.ADMIN_GET_TEMP_TABLES(APPLICATION_ID(), '', ''))
AS T
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) |
|
TEMPTABTYPE | CHAR(1) | Temporary table type:
|
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.
|
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.
|
ONROLLBACK | CHAR(1) | Specifies the action taken on this table when
a ROLLBACK operation is performed.
|
LOGGED | CHAR(1) | Specifies whether this table is logged.
|