WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES table function - Return a list of activities

Refer to Table 1 for a complete list of information that can be returned.

Syntax

>>-WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES--(--application_handle--,-->

>--member--)---------------------------------------------------><

The schema is SYSPROC.

Table function parameters

application_handle

An input argument of type BIGINT that specifies an application handle for which a list of activities is to be returned. If the argument is null, the data is retrieved for all the applications in the database.

member

An input argument of type INTEGER that specifies a valid member number in the same instance as the currently connected database. Specify -1 for the current member, or -2 for all members. If the null value is specified, -1 is set implicitly.

Authorization

Default PUBLIC privilege

None

Examples

Activities currently running with a known application handle

After you identify the application handle, you can look up all the activities currently running in this application. For example, suppose that an administrator wants to list the activities of an application whose application handle, determined by using the LIST APPLICATIONS command, is 1. The administrator runs the following query:

  SELECT SUBSTR(CHAR(COORD_MEMBER),1,5) AS COORD,
       SUBSTR(CHAR(MEMBER),1,4) AS MEMB,
       SUBSTR(CHAR(UOW_ID),1,5) AS UOWID,
       SUBSTR(CHAR(ACTIVITY_ID),1,5) AS ACTID,
       SUBSTR(CHAR(PARENT_UOW_ID),1,8) AS PARUOWID,
       SUBSTR(CHAR(PARENT_ACTIVITY_ID),1,8) AS PARACTID,
              ACTIVITY_TYPE AS ACTTYPE,
       SUBSTR(CHAR(NESTING_LEVEL),1,7) AS NESTING
  FROM TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES(1, -2)) AS WLOACTS
  ORDER BY MEMB, UOWID, ACTID

Sample output from the query is as follows:

COORD MEMB UOWID ACTID PARUOWID PARACTID ACTTYPE  NESTING
----- ---- ----- ----- -------- -------- -------- -------
0     0    2     3     -        -        CALL     0
0     0    2     5     2        3        READ_DML 1

Activities currently running on the system

The following query joins the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES output with the MON_GET_PKG_CACHE_STMT output on EXECUTABLE_ID to provide statement text for all the SQL activities currently running on the system:

SELECT t.application_handle, 
       t.uow_id, 
       t.activity_id, 
       varchar(p.stmt_text, 256) as stmt_text 
FROM table(wlm_get_workload_occurrence_ACTIVITIES(NULL, -1)) as t,
     table(mon_get_pkg_cache_stmt(NULL, NULL, NULL, -1)) as p 
WHERE t.executable_id = p.executable_id 

Sample output is as follows:

APPLICATION_HANDLE   UOW_ID      ACTIVITY_ID    STMT_TEXT
------------------ ----------- -------------- ------------------------------
1                  1           1              SELECT * FROM SYSCAT.TABLES
47                 1           36             INSERT INTO T1 VALUES(123)

Information returned