The COMPILATION_ENV table function returns the elements of a compilation environment.
The schema is SYSPROC.
The function returns a table of two columns (see Table 1): NAME VARCHAR(256) and VALUE VARCHAR(1024). The possible values for the compilation environment element names are described in Table 2.
The origin of the element values depends primarily on whether the SQL statement is issued dynamically or bound as part of a package.
The number and types of entries in a compilation environment can change over time as capabilities are added to the DB2® database manager. If the compilation environment is from a different DB2 database manager level than the level on which this function is executing, only those elements that are recognized by the level of the function are returned. The descriptions of the elements might also vary from release to release.
SELECT NAME, VALUE
FROM TABLE(SYSPROC.COMPILATION_ENV(:hv1)) AS t
SELECT NAME, VALUE
FROM TABLE(SYSPROC.COMPILATION_ENV(:hv1)) AS t
WHERE NAME = 'SCHEMA'
SELECT EXECUTABLE_ID, VARCHAR{STMT_TEXT, 100)
FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS t
EXECUTABLE_ID 2
------------------------------------------------------------------- --------------------------------------------------
x'0100000000000000010000000000000000000000020020090914151405241700' select count(*) from syscat.tables
...
SELECT VARCHAR(NAME, 30), VARCHAR(VALUE, 50)
FROM TABLE(COMPILATION_ENV((SELECT COMP_ENV_DESC FROM TABLE
(MON_GET_PKG_CACHE_STMT(NULL,
x'0100000000000000010000000000000000000000020020090914151405241700',
NULL, -1)) AS t))) AS s
1 2
------------------------------ --------------------------------------------------
ISOLATION CS
QUERY_OPTIMIZATION 5
MIN_DEC_DIV_3 NO
DEGREE 1
SQLRULES DB2
REFRESH_AGE +00000000000000.000000
RESOLUTION_TIMESTAMP 2009-09-14-15.14.05.000000
FEDERATED_ASYNCHRONY 0
PATH "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","SWALKTY"
MAINTAINED_TABLE_TYPE SYSTEM
10 record(s) selected.
Column name | Data type | Description |
---|---|---|
NAME | VARCHAR(256) | Element of compilation environment. See Table 2 for more details. |
VALUE | VARCHAR(1024) | Value of the element. |
Element name | Description |
---|---|
ISOLATION | The isolation level passed to the SQL compiler. The value is obtained from either the CURRENT ISOLATION special register or the ISOLATION bind option of the current package. |
QUERY_OPTIMIZATION | The query optimization level passed to the SQL compiler. The value is obtained from either the CURRENT QUERY OPTIMIZATION special register or the QUERYOPT bind option of the current package. |
MIN_DEC_DIV_3 | The requested decimal computational scale passed to the SQL compiler. The value is obtained from the min_dec_div_3 database configuration parameter. |
DEGREE | The requested degree of intra-parallelism passed to the SQL compiler. The value is obtained from either the CURRENT DEGREE special register or the DEGREE bind option of the current package. |
SQLRULES | The requested SQL statement behaviors passed to the SQL compiler. The value is derived from the setting of the LANGLVL bind option of the current package. The possible values are 'DB2' or 'SQL92'. |
REFRESH_AGE | The allowable data latency passed to the SQL compiler. The value is obtained from either the CURRENT REFRESH AGE special register or the REFRESHAGE bind option of the current package. |
SCHEMA | The default schema passed to the SQL compiler. The value is obtained from either the CURRENT SCHEMA special register or the QUALIFIER bind option of the current package. |
PATH | The function path passed to the SQL compiler. The value is obtained from either the CURRENT PATH special register or the FUNC_PATH bind option of the current package. |
TRANSFORM_GROUP | The transform group information passed to the SQL compiler. The value is obtained from either the CURRENT DEFAULT TRANSFORM GROUP special register or the TRANSFORMGROUP package bind option. |
MAINTAINED_TABLE_TYPE | An indicator of what table types can be considered for optimization, passed to the SQL compiler. The value is obtained from the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION special register. |
RESOLUTION_TIMESTAMP | The timestamp that is to be used by the SQL compiler for resolving items such as function and data type references in an SQL statement. This timestamp is either the current timestamp or the timestamp of the last explicit bind operation for the current package. |
FEDERATED_ASYNCHRONY | The requested degree of federated asynchrony parallelism passed to the SQL compiler. The value is obtained from either the CURRENT FEDERATED ASYNCHRONY special register or the FEDERATED_ASYNCHRONY bind option of the current package. |