WLM_GET_CONN_ENV - get settings for activity data collection for a connection

The WLM_GET_CONN_ENV table function returns for a particular connection the values of settings that control collection of activity data and section actuals.

You can use this table function to check the current values of the settings applied by the WLM_SET_CONN_ENV stored procedure.

Authorization

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

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority
WLMADM authority

Default PUBLIC privilege

None

Syntax

Parameters

application_handle: An input argument of type BIGINT that specifies the application handle for the connection for which information is to be returned. You can use a value of NULL to indicate the connection on which the procedure was invoked.

Information returned

Table 1. Information returned for WLM_GET_CONN_ENV
Column name	Data type	Description
APPLICATION_HANDLE	BIGINT	application_handle - Application handle
DETAILS	BLOB(8M)	XML document that contains connection environment details. See Table 2 for a description of the elements in this document.

Detailed settings returned

Table 2. Detailed metrics returned for WLM_GET_CONN_ENV
Element name	Data type	Description or corresponding monitor element
collectactdata	xs:string(255)	Specifies what kind of activity data is being collected, if any. Possible values are: NONE WITHOUT DETAILS WITH DETAILS WITH DETAILS, SECTION WITH DETAILS, SECTION AND VALUES WITH DETAILS AND VALUES See information about the WLM_SET_CONN_ENV procedure for details about these options.
collectactpartition	xs:string(255)	Specifies where activity data is being collected. Possible values are: COORDINATOR ALL See information about the WLM_SET_CONN_ENV procedure for details about these options.
collectsectionactuals	xs:string(255)	Specifies whether section actuals are being collected. Possible values include: NONE BASE See information about the WLM_SET_CONN_ENV procedure for details about these options.

Usage notes

The WLM_GET_CONN_ENV table function provides maximum flexibility for formatting output because it returns WLM environment information for a connection as an XML document. The output can be parsed directly by an XML parser, or it can be converted to relational format by the XMLTABLE function.

The schema for the XML document that is returned in the DETAILS column is available in the file sqllib/misc/DB2MonRoutines.xsd. Further details can be found in the file sqllib/misc/DB2MonCommon.xsd.

Example

The following query checks whether activities are being collected for the current connection.

SELECT application_handle, 
       xmlparse(document details preserve whitespace)
FROM TABLE (
   WLM_GET_CONN_ENV(
      cast(NULL as bigint))
   ) connenv

The following is an example of output from this query.

APPLICATION_HANDLE   DETAILS
-------------------- ------------------------------------------------------
                   7 <wlm_conn_env
                     xmlns=http://www.ibm.com/xmlns/prod/db2/mon
                     release="9070100">
                     <collectactdata>NONE</collectactdata>
                     <collectactpartition>COORDINATOR</collectactpartition>
                     <collectsectionactuals>NONE</collectsectionactuals>
                     </wlm_conn_env>