MON_GET_ACTIVITY table function - Return a list of activities

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

Authorization

Default PUBLIC privilege

None

Syntax

>>-MON_GET_ACTIVITY--(--application_handle--,--member--)-------><

The schema is SYSPROC.

Routine 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 active members. If the null value is specified, -1 is set implicitly.

Information returned

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(MON_GET_ACTIVITY(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 uses MON_GET_ACTIVITY to provide statement text for all SQL activities currently running on the system:

SELECT t.application_handle, t.uow_id, t.activity_id,
   VARCHAR(t.stmt_text, 256) as stmt_text
FROM table(MON_GET_ACTIVITY(NULL, -1)) as t 

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)