DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_SET_CONN_ENV - enable collection of activity data and measurement of section actuals

The WLM_SET_CONN_ENV procedure enables for a particular connection the collection of activity data and measurement of section actuals (runtime statistics measured during section execution).

Once applied, the settings made by the WLM_SET_CONN_ENV procedure continue to apply until explicitly overwritten by another call to the WLM_SET_CONN_ENV procedure, or until the connection is closed. After the connection is closed, any new connection that reuses the same application handle does not inherit the settings of the previous connection to use that application handle.

Note: If your database was created in Version 9.7 before Fix Pack 2, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database upgrade). If you downgrade to Version 9.7, this routine will no longer work.


>>-WLM_SET_CONN_ENV--(--application_handle--,--settings--)-----><

The schema is SYSPROC.

Authorization

EXECUTE privilege on the WLM_SET_CONN_ENV procedure.

Parameters

application_handle

An input argument of type BIGINT that specifies the application handle whose connection environment is to be modified. The application handle specified must refer to an existing application (otherwise, SQLSTATE 5U002 is returned). You can use a value of NULL to indicate that the connection whose environment is to be changed is the connection on which the procedure was invoked.

settings

An input argument of type CLOB(8K) that enables you to specify one or more monitor settings. Settings are specified as name value pairs using the format:

<setting name tag>value</setting name tag>

Each setting can be specified a maximum of one time. Setting names are case sensitive. A change to a setting takes effect on the next statement executed; it has no effect on statements already in progress.

The available setting name tags are as follows.

'<collectactdata>value</collectactdata>'

Specifies what activity data should be collected by the activity event monitor. The possible values are (variations in spaces between words are supported):

Value	Description
NONE	Activity data should not be collected
WITHOUT DETAILS	Data about each activity is sent to any active activities event monitor when the activity completes execution. Details about statement, compilation environment, and section environment data are not sent.
WITH DETAILS	Statement and compilation environment data is sent to any active activities event monitor, for those activities that have them. Section environment data is not sent.
WITH DETAILS, SECTION	Statement, compilation environment, section environment data, and section actuals are sent to any active activities event monitor, for those activities that have them. For section actuals to be collected, either `collectsectionactuals` must be set to BASE or the section_actuals database configuration parameter must be set to BASE. Section actuals are collected on any partition where the activity data is collected.
WITH DETAILS, SECTION AND VALUES	Statement, compilation environment, section environment data, section actuals, and input data values are sent to any active activities event monitor, for those activities that have them. For section actuals to be collected, either `collectsectionactuals` must be set to BASE or the section_actuals database configuration parameter must be set to BASE. Section actuals are collected on any partition where the activity data is collected.
WITH DETAILS AND VALUES	Statement, compilation environment, and input data values are sent to any active activities event monitor, for those activities that have them. Section environment data is not sent.

'<collectactpartition>COORDINATOR</collectactpartition>' or '<collectactpartition>ALL</collectactpartition>'
Specifies where activity data is collected, either just at the coordinator partition or at all partitions. If collectactpartition is not specified, the connection maintains its previous value for collectactpartition which by default is COORDINATOR.
'<collectsectionactuals>NONE</collectsectionactuals>' or '<collectsectionactuals>BASE</collectsectionactuals>'
Section actuals are collected if collectsectionactuals is set to BASE.

Example

The following examples both enable activity collection, without details, on the coordinator partition for the current connection:

CALL WLM_SET_CONN_ENV(NULL,'<collectactdata>WITHOUT DETAILS</collectactdata>')

CALL WLM_SET_CONN_ENV(NULL,'<collectactdata>WITHOUT 
   DETAILS</collectactdata><collectactpartition>COORDINATOR
   </collectactpartition>')

The next example enables collection of activity data with section environment data and section actuals , but no data values, on all partitions for the current connection:

CALL WLM_SET_CONN_ENV(NULL, '<collectactdata>WITH DETAILS, SECTION
   </collectactdata><collectactpartition>ALL</collectactpartition>')

The following example disables collection of activity data for the current connection.

CALL WLM_SET_CONN_ENV(NULL, '<collectactdata>NONE</collectactdata>')

Usage notes

The collectactdata setting only controls activity data collection at the connection level . An activity might have multiple activity data collection controls applied to it, for example, the connection might be mapped to a service class where the COLLECT ACTIVITY DATA clause has been applied. In a situation where multiple activity data collection controls are applied, the effective setting is the combination of all the settings. For example:

The connection level control is activity data without details.
The workload control is none.
The service class control is activity data with details and values.
When the activity completes execution, detailed information about the activity plus data values is sent to any active event monitors.

If a setting is not specified in the input of the WLM_SET_CONN_ENV procedure, it is not altered in the connection environment.

The effective setting for the collection of section actuals is the combination of the collectsectionactuals setting and the section_actuals database configuration parameter. For example, if collectsectionactuals is set to BASE and the section_actuals database configuration parameter value is NONE, the effective setting for the collection of section actuals is BASE (and vice versa). Do not use automatic statistics profiling (enabled using the auto_stats_prof database configuration parameter) if collectsectionactuals is set to BASE (otherwise, the warning SQLSTATE 01HN2 is returned).

Automatic client rerouting cannot be performed for a connection when activity data and section actuals are being collected (when collectactdata is set to any value other than NONE).