COMPILATION_ENV table function - Retrieve compilation environment elements

The COMPILATION_ENV table function returns the elements of a compilation environment.

Syntax

The schema is SYSPROC.

Table function parameter

compilation-env

An input argument of type BLOB(2M) that contains a compilation environment obtained from the comp_env_desc (compilation environment) monitor element.

Authorization

One of the following authorities is required to execute the function:

EXECUTE privilege on the 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.

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 database manager. If the compilation environment is from a different 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.

Examples

Example 1: Request all the elements of a specific compilation environment that was previously captured by a deadlock event monitor. A deadlock event monitor that is created specifying the WITH DETAILS HISTORY option will capture the compilation environment for dynamic SQL statements. This captured environment is what is accepted as input to the table function.

   SELECT NAME, VALUE
     FROM TABLE(SYSPROC.COMPILATION_ENV(:hv1)) AS t

Example 2: Request a specific element (the default schema) of a compilation environment.

   SELECT NAME, VALUE
     FROM TABLE(SYSPROC.COMPILATION_ENV(:hv1)) AS t
     WHERE NAME = 'SCHEMA'

Example 3: Display the compilation environment for a specific statement in the package cache.

Obtain the executable ID, which is used to identify the statement of interest, using the following statement:

SELECT EXECUTABLE_ID, VARCHAR(STMT_TEXT, 100)
  FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-1)) AS t

The following is an example output after executing the preceding statement:

EXECUTABLE_ID                                                       2
------------------------------------------------------------------- --------------------------------------------------
x'0100000000000000010000000000000000000000020020090914151405241700' select count(*) from syscat.tables                
...

Investigate the compilation environment for the statement (identified using the executable ID) and format the compilation environment using the COMPILATION_ENV table function. The following statement is an example of how this can be done:

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

The following is an example output after executing the preceding statement:

1                              2                                                 
------------------------------ --------------------------------------------------
ISOLATION                      CS                                                
QUERY_OPTIMIZATION             5                                                                                                
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                                            

  9 record(s) selected.

Information returned

Table 1. Information returned by the COMPILATION_ENV table function
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.

Table 2. Elements of a compilation environment returned by the COMPILATION_ENV table function
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.
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.