DB2 10.5 for Linux, UNIX, and Windows

ADMINTEMPCOLUMNS administrative view and ADMIN_GET_TEMP_COLUMNS table function - Retrieve column information for temporary tables

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.

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

ADMINTEMPCOLUMNS administrative view
ADMIN_GET_TEMP_COLUMNS table function

ADMINTEMPCOLUMNS administrative view

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 Information returned for ADMINTEMPCOLUMNS administrative view and ADMIN_GET_TEMP_COLUMNS table for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the ADMINTEMPCOLUMNS administrative view
CONTROL privilege on the ADMINTEMPCOLUMNS administrative view
DATAACCESS authority
DBADM authority
SQLADM authority

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

EXECUTE privilege on the ADMIN_GET_TEMP_COLUMNS 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 column attribute information for all instances of created temporary tables and all declared temporary tables currently existing in the database.

SELECT * FROM SYSIBMADM.ADMINTEMPCOLUMNS

Example 2: Determine which temporary tables active in the database are using the user-defined data type of USMONEY.

SELECT APPLICATION_HANDLE, TABSCHEMA, TABNAME
   FROM SYSIBMADM.ADMINTEMPCOLUMNS 
   WHERE TYPENAME = 'USMONEY'

Example 3: Retrieve table schema, table name, and the column names for all declared temporary tables declared by the SYSTEM_USER.

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

ADMIN_GET_TEMP_COLUMNS table function

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 Information returned for ADMINTEMPCOLUMNS administrative view and ADMIN_GET_TEMP_COLUMNS table for a complete list of information that can be returned.

Syntax


>>-ADMIN_GET_TEMP_COLUMNS--(--application_handle--,--tabschema--,--tabname--)-><

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_COLUMNS 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 column information for the declared temporary table TEMPEMPLOYEE for the current connection.

SELECT * 
   FROM TABLE (
      SYSPROC.ADMIN_GET_TEMP_COLUMNS(
         APPLICATION_ID(), 'SESSION', 'TEMPEMPLOYEE')) 
   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 ADMINTEMPCOLUMNS administrative view and ADMIN_GET_TEMP_COLUMNS
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 columns for DECFLOAT(16) and 16 columns 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. "Y" = Column is nullable "N" = Column is not nullable The value can be "N" for a view column that is derived from an expression or function. Nevertheless, such a column allows null values when the statement using the view is processed with warnings for arithmetic errors.
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. "Y" = Column is logged "N" = Column is not logged
COMPACT	CHAR(1)	Applies only to columns whose type is LOB or distinct based on LOB; blank otherwise. "Y" = Column is compacted in storage "N" = Column is not compacted
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)	"Y" = Identity column "N" = Not an identity column
GENERATED	CHAR(1)	Type of generated column. "A" = Column value is always generated "D" = Column values is generated by default Blank = Column is not generated