How to see the times statements take to be executed in a stored procedure DB2-CodePudding

i'm trying to increase the performance of a query - Stored procedure in DB2 that scans a lot of tables and a lot of selects.

What i am trying to do is to find which of these statements (insert select statements) lasts more and how time it takes each to be completed.

CodePudding user response：

If static statements have been run by the routine are still in the package cache, then:

SELECT 
  S.STMTNO -- Line number in SP
, P.NUM_EXEC_WITH_METRICS
, P.STMT_EXEC_TIME
-- Huge number of other counters from the package cache here:
--, P.* 
, P.STMT_TEXT
FROM SYSCAT.ROUTINES R
JOIN SYSCAT.ROUTINEDEP D ON (D.ROUTINESCHEMA, D.SPECIFICNAME, D.BTYPE) = (R.ROUTINESCHEMA, R.SPECIFICNAME, 'K')
JOIN SYSCAT.STATEMENTS S ON (S.PKGSCHEMA, S.PKGNAME) = (D.BSCHEMA, D.BNAME)
LEFT JOIN TABLE (MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) P ON 
  (P.PACKAGE_SCHEMA, P.PACKAGE_NAME, P.PACKAGE_VERSION_ID, P.SECTION_NUMBER) 
= (D.BSCHEMA, D.BNAME, S.VERSION, S.SECTNO)
WHERE 
--(R.ROUTINESCHEMA, R.ROUTINENAME) = ('MY_ROUTINE_SCHEMA', 'MY_ROUTINE_NAME')
(R.ROUTINESCHEMA, R.SPECIFICNAME) = ('MY_ROUTINE_SCHEMA', 'MY_ROUTINE_SPECIFICNAME')
ORDER BY P.STMT_EXEC_TIME DESC

No way to bound dynamic statements from the package cache to some routine. That is, if your routine runs some dynamic statements, you may find the corresponding statistics in the case only manually, say, comparing the statement texts visually / manually...

Update: Event monitor for activities use
CREATE EVENT MONITOR (activities) statement
An event monitor for activities acts as a "logger" for statements. You may turn it on temporarily for some session, for example.

-- Creation
CREATE EVENT MONITOR ACT FOR ACTIVITIES 
WRITE TO TABLE
  ACTIVITY        (TABLE ACTIVITY_ACT        IN SYSTOOLSPACE)
, ACTIVITYMETRICS (TABLE ACTIVITYMETRICS_ACT IN SYSTOOLSPACE)
, ACTIVITYSTMT    (TABLE ACTIVITYSTMT_ACT    IN SYSTOOLSPACE)
, ACTIVITYVALS    (TABLE ACTIVITYVALS_ACT    IN SYSTOOLSPACE)
, CONTROL         (TABLE CONTROL_ACT         IN SYSTOOLSPACE)
MANUALSTART
;

-- Start the event monitor
SET EVENT MONITOR ACT STATE 1;

-- Turn the collection on for your current session
CALL WLM_SET_CONN_ENV(MON_GET_APPLICATION_HANDLE(), '<collectactdata>WITH DETAILS AND VALUES</collectactdata>');

-- Run your statement(s)
CALL MY_ROUTINE ...;

-- Turn the collection off for your current session
CALL WLM_SET_CONN_ENV(MON_GET_APPLICATION_HANDLE(), '<collectactdata>NONE</collectactdata>');

-- Stop the event monitor (optionally)
SET EVENT MONITOR ACT STATE 0;

-- Analysis
SELECT 
  A.TIME_STARTED, A.TIME_COMPLETED
, TIMESTAMPDIFF(2, CHAR(A.TIME_COMPLETED - A.TIME_STARTED)) TIME_S
, A.ADDRESS, A.APPL_NAME, A.APPL_ID, A.UOW_ID, A.ACTIVITY_ID
--, V.STMT_VALUE_INDEX, V.STMT_VALUE_ISREOPT
--, V.STMT_VALUE_INDEX, VARCHAR(V.STMT_VALUE_DATA, 50) STMT_VALUE_DATA
, S.CREATOR, S.PACKAGE_NAME
, S.STMTNO -- Line number in SP
, M.STMT_EXEC_TIME, M.TOTAL_ACT_WAIT_TIME
, 100 * M.TOTAL_ACT_WAIT_TIME / NULLIF (M.STMT_EXEC_TIME, 0) AS WAIT_PST
, M.ROWS_READ, M.ROWS_RETURNED, M.ROWS_MODIFIED
-- Other metrics if needed
-- , M.*
, VARCHAR(S.STMT_TEXT, 256) stmt_text
FROM ACTIVITY_ACT A
JOIN ACTIVITYSTMT_ACT S ON 
  (S.PARTITION_NUMBER, S.APPL_ID, S.UOW_ID, S.ACTIVITY_ID, S.ACTIVITY_SECONDARY_ID) 
= (A.PARTITION_NUMBER, A.APPL_ID, A.UOW_ID, A.ACTIVITY_ID, A.ACTIVITY_SECONDARY_ID)
JOIN ACTIVITYMETRICS_ACT M ON 
  (M.PARTITION_NUMBER, M.APPL_ID, M.UOW_ID, M.ACTIVITY_ID, M.ACTIVITY_SECONDARY_ID) 
= (A.PARTITION_NUMBER, A.APPL_ID, A.UOW_ID, A.ACTIVITY_ID, A.ACTIVITY_SECONDARY_ID)
/*
-- Statement parameters
LEFT JOIN ACTIVITYVALS_ACT V ON 
  (V.PARTITION_NUMBER, V.APPL_ID, S.UOW_ID, V.ACTIVITY_ID, V.ACTIVITY_SECONDARY_ID) 
= (A.PARTITION_NUMBER, A.APPL_ID, A.UOW_ID, A.ACTIVITY_ID, A.ACTIVITY_SECONDARY_ID)
*/
WHERE A.APPL_ID = MON_GET_APPLICATION_ID ()
ORDER BY A.TIME_STARTED, A.APPL_ID, A.UOW_ID, A.ACTIVITY_ID;

Refer to the Logical data groups and event monitor output tables link for the description of event monitor tables and their contents.