MON_CAPTURE_ACTIVITY_IN_PROGRESS procedure - Collect activity information for activities event monitor

The MON_CAPTURE_ACTIVITY_IN_PROGRESS procedure gathers information about a specified activity and writes the information to the active activities event monitor.

When you apply this procedure to an activity with child activities, the procedure recursively generates a record for each child activity. This information is collected and sent when you call the procedure; the procedure does not wait until the parent activity completes execution. The record of the activity in the event monitor is marked as a partial record.

Authorization

One of the following authorizations is required:

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

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Procedure parameters

If you do not specify all of the following parameters, no activity is found, and an SQL0171N error with SQLSTATE 42815 is returned.

application_handle

An input argument of type BIGINT that specifies the handle of the application whose activity information is to be captured.

uow_id

An input argument of type INTEGER that specifies the unit of work ID of the activity whose information is to be captured.

activity_id

An input argument of type INTEGER that specifies the activity ID that uniquely identifies the activity within the unit of work whose information is to be captured.

Usage notes

If there is no active activities event monitor, an SQL1633W error with SQLSTATE 01H53 is returned.
Activity information is collected only on the coordinator member for the activity.

Example

Assume that a user complains that stored procedure MYSCHEMA.MYSLOWSTP seems to be running slower than usual. The administrator wants to investigate the cause of the slowdown. Investigating while the stored procedure is running is not practical, so the administrator decides to capture information about the stored procedure activity and all of the activities nested within it.

An event monitor for database activities named DB2ACTIVITIES has been activated. The administrator uses the MON_GET_ACTIVITY function to obtain the application handle, unit of work ID, and activity ID for the call of this stored procedure. Assuming that the activity is identified by an application handle of 1, a unit of work ID of 2, and an activity ID of 3, the administrator can now issue the call to MON_CAPTURE_ACTIVITY_IN_PROGRESS as follows:

  CALL MON_CAPTURE_ACTIVITY_IN_PROGRESS(1,2,3)

After the procedure is completed, the administrator can use the following table function to find out where the activity spent its time. The function retrieves the information from the DB2ACTIVITIES event monitor.

 CREATE FUNCTION SHOWCAPTUREDACTIVITY(APPHNDL BIGINT,
                                     UOWID INTEGER,
                                     ACTIVITYID INTEGER)
  RETURNS TABLE (UOW_ID INTEGER, ACTIVITY_ID INTEGER, STMT_TEXT VARCHAR(40),
    LIFE_TIME DOUBLE)
  LANGUAGE SQL
  READS SQL DATA
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN WITH RAH (LEVEL, APPL_ID, PARENT_UOW_ID, PARENT_ACTIVITY_ID,
          UOW_ID, ACTIVITY_ID, STMT_TEXT, ACT_EXEC_TIME) AS
  (SELECT 1, ROOT.APPL_ID, ROOT.PARENT_UOW_ID,
          ROOT.PARENT_ACTIVITY_ID, ROOT.UOW_ID, ROOT.ACTIVITY_ID,
          ROOTSTMT.STMT_TEXT, ACT_EXEC_TIME
   FROM ACTIVITY_DB2ACTIVITIES ROOT, ACTIVITYSTMT_DB2ACTIVITIES ROOTSTMT
   WHERE ROOT.APPL_ID = ROOTSTMT.APPL_ID AND ROOT.AGENT_ID = APPHNDL
     AND ROOT.UOW_ID = ROOTSTMT.UOW_ID AND ROOT.UOW_ID = UOWID
     AND ROOT.ACTIVITY_ID = ROOTSTMT.ACTIVITY_ID AND ROOT.ACTIVITY_ID = ACTIVITYID
  UNION ALL
   SELECT PARENT.LEVEL +1, CHILD.APPL_ID, CHILD.PARENT_UOW_ID,
          CHILD.PARENT_ACTIVITY_ID, CHILD.UOW_ID,
          CHILD.ACTIVITY_ID, CHILDSTMT.STMT_TEXT, CHILD.ACT_EXEC_TIME
   FROM RAH PARENT, ACTIVITY_DB2ACTIVITIES CHILD,
        ACTIVITYSTMT_DB2ACTIVITIES CHILDSTMT
   WHERE PARENT.APPL_ID = CHILD.APPL_ID AND
         CHILD.APPL_ID = CHILDSTMT.APPL_ID AND
         PARENT.UOW_ID = CHILD.PARENT_UOW_ID AND
         CHILD.UOW_ID = CHILDSTMT.UOW_ID AND
         PARENT.ACTIVITY_ID = CHILD.PARENT_ACTIVITY_ID AND
         CHILD.ACTIVITY_ID = CHILDSTMT.ACTIVITY_ID AND
         PARENT.LEVEL < 64
  )
SELECT UOW_ID, ACTIVITY_ID, SUBSTR(STMT_TEXT,1,40),
                ACT_EXEC_TIME AS
                LIFE_TIME
FROM RAH

The following sample query uses the table function:

  SELECT * FROM TABLE(SHOWCAPTUREDACTIVITY(1, 2, 3))
    AS ACTS ORDER BY UOW_ID, ACTIVITY_ID