The ADMINTEMPCOLUMNS administrative view and the ADMIN_GET_TEMP_COLUMNS table function provide methods to retrieve column attribute information for created temporary tables and declared temporary tables.
Although the catalog views contain column attribute information for instances of created temporary tables, they do not have this information for declared temporary tables.
The ADMINTEMPCOLUMNS administrative view returns column attribute information for instances of created temporary tables and declared temporary tables.
The schema is SYSIBMADM.
Refer to the ADMINTEMPCOLUMNS administrative view and ADMIN_GET_TEMP_COLUMNS table function metadata table for a complete list of information that can be returned.
SELECT * FROM SYSIBMADM.ADMINTEMPCOLUMNS
SELECT APPLICATION_HANDLE, TABSCHEMA, TABNAME
FROM SYSIBMADM.ADMINTEMPCOLUMNS
WHERE TYPENAME = 'USMONEY'
SELECT T.TABSCHEMA, T.TABNAME, C.COLNAME
FROM SYSIBMADM.ADMINTEMPCOLUMNS C, SYSIBMADM.ADMINTEMPTABLES T
WHERE T.TEMPTABTYPE = 'D'
AND T.INSTANTIATOR = SYSTEM_USER
AND T.TABSCHEMA = C.TABSCHEMA
AND T.TABNAME = C.TABNAME
The ADMIN_GET_TEMP_TABLES table function returns the same information as the ADMINTEMPCOLUMNS administrative view, but allows you to specify a schema name and a table name.
Refer to the ADMINTEMPCOLUMNS administrative view and ADMIN_GET_TEMP_COLUMNS table function metadata table for a complete list of information that can be returned.
>>-ADMIN_GET_TEMP_COLUMNS--(--application_handle--,--tabschema--,--tabname--)-><
The schema is SYSPROC.
EXECUTE privilege on the ADMIN_GET_TEMP_COLUMNS table function.
SELECT *
FROM TABLE (
SYSPROC.ADMIN_GET_TEMP_COLUMNS(
APPLICATION_ID(), 'SESSION', 'TEMPEMPLOYEE'))
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 |
COLNAME | VARCHAR(128) | Name of the column. |
COLNO | SMALLINT | Number of this column in the table (starting with 0). |
TYPESCHEMA | VARCHAR(128) | Schema name of the data type for the column. |
TYPENAME | VARCHAR(128) | Unqualified name of the data type for the column. |
LENGTH | INTEGER | Maximum length of the data. 0 for distinct types. The LENGTH column indicates precision for DECIMAL fields, and indicates the number of bytes of storage required for decimal floating-point columns; that is, 8 bytes for DECFLOAT(16) and 16 bytes for DECFLOAT(34). |
SCALE | SMALLINT | Scale if the column type is DECIMAL; or the number of digits of fractional seconds if the column type is TIMESTAMP; 0 otherwise. |
DEFAULT | VARCHAR(254) | Default value for the column of a table expressed as a constant, special register, or cast-function appropriate for the data type of the column. Can also be the keyword NULL. Values might be converted from what was specified as a default value. For example, date and time constants are shown in ISO format, cast-function names are qualified with schema names, and identifiers are delimited. Null value if a DEFAULT clause was not specified or the column is a view column. |
NULLS | CHAR(1) | Nullability attribute for the column.
|
CODEPAGE | SMALLINT | Code page used for data in this column; 0 if the column is defined as FOR BIT DATA or is not a string type. |
LOGGED | CHAR(1) | Applies only to columns whose type is LOB or
distinct based on LOB; blank otherwise.
|
COMPACT | CHAR(1) | Applies only to columns whose type is LOB or
distinct based on LOB; blank otherwise.
|
INLINE_LENGTH | INTEGER | Maximum size in bytes of the internal representation of an instance of an XML document or a structured type that can be stored in the base table; 0 when not applicable. |
IDENTITY | CHAR(1) |
|
GENERATED | CHAR(1) | Type of generated column.
|