SET COMPILATION ENVIRONMENT statement

The SET COMPILATION ENVIRONMENT statement changes the current compilation environment in the connection to match the values contained in the compilation environment provided by an event monitor.

The compilation environment contains information like schema, isolation level, query degree or function path under which a SQL statement has been compiled (prepared). It allows you to run and explain SQL statements within a given environment. You can run the db2caem command or the db2support command with the -compenv parameter to specify a file containing a BLOB data type with the compilation environment. Follow these steps to create the file:

Set the environment (for example, to SET CURRENT SCHEMA, CHANGE ISOLATION, SET CURRENT DEGREE, or SET PATH.
Run a SQL statement

Export the compilation environment into the lob file in directory lobs:

EXPORT TO compenv.ixf OF IXF LOBS TO lobs
SELECT COMP_ENV_DESC
FROM TABLE (MON_GET_PKG_CACHE_STMT('d', null, null, -1)) AS tf
WHERE STMT_TEXT = '' ;

Use compilation environment BLOB file for other SQL statements for db2caem and db2support and specify:
```
-compenv lobs/compenv.ixf.001.lob
```

For more information, see COMPILATION_ENV table function

This statement changes the values of one or more special registers; these changes, in turn, will affect the compilation of any subsequent dynamic SQL statement.

This statement is not under transaction control.

Invocation

The statement can be embedded in an application program. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Description

host-variable

A variable of type BLOB containing a compilation environment provided by an event monitor. It cannot be set to null. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815). If the format of the compilation environment is incorrect, an error is returned, and the connection settings remain unmodified (SQLSTATE 51040).

Notes

To reset the compilation environment to the original default values, terminate and then restart the connection. You can achieve the same effect by issuing this statement within an SQL routine, so that any special register changes are not reflected in the connection upon return from that routine.
Use the COMPILATION_ENV table function to look at the individual elements that are contained within the compilation environment.

Example

Set the current session's compilation environment to the values contained in a 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 statement.

   SET COMPILATION ENVIRONMENT = :hv1