WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 table function - Return a list of activities

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_PARTITION_NUM),1,5) AS COORD,
       SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
       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_V97(1, -2)) AS WLOACTS
  ORDER BY PART, UOWID, ACTID

Sample output from the query is as follows:

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

The following query joins the WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES_V97 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_v97(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)