You can monitor dynamic SQL statements by using SYSIBMADM.TOP_DYNAMIC_SQL view but you need an event monitor to monitor SQL statements inside a DB2 Stored Procedures as those statemets are static in nature.
Let us understand this through a simple exercise:
Step-A: Create a Table and a Stored Procedure
Create a table MONITOR_TAB and a stored procedure MONITOR_SP to insert and update data in this table.
db2set db2_compatibility_vector=ORA db2set db2_sqlroutine_prepopts= db2 UPDATE DB CFG FOR SAMPLE USING logprimary 30 logsecond 10 logfilsiz 5000 softmax 10 db2stop force db2start db2 CONNECT TO SAMPLE db2 SET SQLCOMPAT PLSQL db2 -tf sptabs.txt db2 SET SQLCOMPAT DB2 db2 CONNECT RESET
Contents of sptabs.sql
-- Create buffer pools, table spaces for the table
CREATE BUFFERPOOL DATA IMMEDIATE SIZE 50 PAGESIZE 16 K
/
CREATE BUFFERPOOL IDX IMMEDIATE SIZE 50 PAGESIZE 16 K
/
CREATE TABLESPACE DATA PAGESIZE 16 K BUFFERPOOL DATA
/
CREATE TABLESPACE IDX PAGESIZE 16 K BUFFERPOOL IDX
/
-- Create table MONITOR_TAB
CREATE TABLE MONITOR_TAB
(
MON_ID INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY
(START WITH 100000,
INCREMENT BY 5
),
SSN CHAR(11) NOT NULL,
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(20) NOT NULL,
JOB_CODE CHAR(4) NOT NULL,
DEPT SMALLINT NOT NULL,
SALARY DECIMAL(12,2) NOT NULL,
DOB DATE NOT NULL WITH DEFAULT,
CONSTRAINT PK_STRAWMAN PRIMARY KEY (MON_ID)
) IN DATA INDEX IN IDX
/
SET SERVEROUTPUT ON
/
-- Notice: This creates a PL/SQL Procedure MONITOR_SP
CREATE OR REPLACE PROCEDURE MONITOR_SP
(
v_total OUT INTEGER
)
AS
v_rows INTEGER := 0;
BEGIN
-- INSERT 25000 rows in the MONITOR_TAB table
DBMS_OUTPUT.PUT_LINE('Before inserting data in MONITOR_TAB table');
INSERT INTO MONITOR_TAB (SSN,FIRST_NAME,LAST_NAME,job_code,dept,salary,DOB)
WITH temp1 (s1,r1,r2,r3,r4) AS
(VALUES (0
,RAND(2)
,RAND()+(RAND()/1E5)
,RAND()* RAND()
,RAND()* RAND()* RAND())
UNION ALL
SELECT s1 + 1
,RAND()
,RAND()+(RAND()/1E5)
,RAND()* RAND()
,RAND()* RAND()* RAND()
FROM temp1
WHERE s1 < 24999
)
SELECT SUBSTR(DIGITS(INT(r2*988+10)),8) ||'-'||
SUBSTR(DIGITS(INT(r1*88+10)),9) || '-' ||
TRANSLATE(SUBSTR(DIGITS(s1),7),'9873450126','0123456789'),
CHR(INT(r1*26+65))|| CHR(INT(r2*26+97))|| CHR(INT(r3*26+97))||CHR(INT(r4*26+97))||
CHR(INT(r3*10+97))|| CHR(INT(r3*11+97)),
CHR(INT(r2*26+65))||TRANSLATE(CHAR(INT(r2*1E7)),'aaeeiibmty','0123456789'),
CASE
WHEN INT(r4*9) > 7 THEN 'MGR'
WHEN INT(r4*9) > 5 THEN 'SUPR'
WHEN INT(r4*9) > 3 THEN 'PGMR'
WHEN INT(R4*9) > 1 THEN 'SEC'
ELSE 'WKR'
END,
INT(r3*98+1),
DECIMAL(r4*99999,7,2),
DATE('1930-01-01') + INT(50-(r4*50)) YEARS + INT(r4*11) MONTHS + INT(r4*27) DAYS
FROM temp1;
v_rows := SQL%ROWCOUNT;
v_total := v_rows;
DBMS_OUTPUT.PUT_LINE('After inserting data in MONITOR_TAB table # of rows inserted = ' || v_rows);
UPDATE MONITOR_TAB SET salary = 1.1 * salary where salary < 30000;
v_rows := SQL%ROWCOUNT;
v_total := v_total + v_rows;
DBMS_OUTPUT.PUT_LINE('10% salary increase affected ' || v_rows || ' people');
UPDATE MONITOR_TAB SET salary = 1.2 * salary where salary between 30001 and 40000;
v_rows := SQL%ROWCOUNT;
v_total := v_total + v_rows;
DBMS_OUTPUT.PUT_LINE('20% salary increase affected ' || v_rows || ' people');
UPDATE MONITOR_TAB SET salary = 1.3 * salary where salary between 40001 and 50000;
v_rows := SQL%ROWCOUNT;
v_total := v_total + v_rows;
DBMS_OUTPUT.PUT_LINE('30% salary increase affected ' || v_rows || ' people');
UPDATE MONITOR_TAB SET salary = 1.5 * salary where salary < 50001;
v_rows := SQL%ROWCOUNT;
v_total := v_total + v_rows;
DBMS_OUTPUT.PUT_LINE('50% salary increase affected ' || v_rows || ' people');
EXCEPTION
WHEN OTHERS THEN
-- RAISE_APPLICATION_ERROR(-20000,'Error inserting/updating in table MONITOR_TAB');
DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;
/
-- Call the stored procedure MONITOR_SP
CALL MONITOR_SP(?)
/
COMMIT
/
Step-B: Create an Event Monitor
The db2evtbl command line tool can help you generate event monitor command scripts to store data in target DB2 tables. In the DB2 install path, under the folder MISC, there is an example script for building DB2 Workload Manager event monitors called: wlmevmon.ddl
db2 CONNECT TO SAMPLE db2 -tvf evmon.txt db2 SET EVENT MONITOR pot_evemon STATE 1 db2 TRUNCATE TABLE MONITOR_TAB db2 "CALL MONITOR_SP(?)" db2 SET EVENT MONITOR pot_evemon STATE 0 db2 COMMIT
Contents of evmon.sql
CREATE EVENT MONITOR pot_evemon FOR STATEMENTS WRITE TO TABLE
CONNHEADER (TABLE pot_evemon_head
INCLUDES (AGENT_ID,
APPL_ID,
APPL_NAME,
AUTH_ID,
CLIENT_DB_ALIAS,
CLIENT_NNAME,
CLIENT_PID,
CLIENT_PLATFORM,
CLIENT_PRDID,
CLIENT_PROTOCOL,
CODEPAGE_ID,
CONN_TIME,
CORR_TOKEN,
EXECUTION_ID,
SEQUENCE_NO,
TERRITORY_CODE ) ),
STMT (TABLE pot_evemon_stmt
INCLUDES (AGENT_ID,
AGENTS_TOP,
APPL_ID,
BLOCKING_CURSOR,
CONSISTENCY_TOKEN,
CREATOR,
CURSOR_NAME,
EVMON_FLUSHES,
FETCH_COUNT,
INT_ROWS_DELETED,
INT_ROWS_INSERTED,
INT_ROWS_UPDATED,
PACKAGE_NAME,
PACKAGE_VERSION_ID,
PARTIAL_RECORD,
POOL_DATA_L_READS,
POOL_DATA_P_READS,
POOL_INDEX_L_READS,
POOL_INDEX_P_READS,
POOL_TEMP_DATA_L_READS,
POOL_TEMP_DATA_P_READS,
POOL_TEMP_INDEX_L_READS,
POOL_TEMP_INDEX_P_READS,
POOL_TEMP_XDA_L_READS,
POOL_TEMP_XDA_P_READS,
POOL_XDA_L_READS,
POOL_XDA_P_READS,
ROWS_READ,
ROWS_WRITTEN,
SECTION_NUMBER,
SEQUENCE_NO,
SORT_OVERFLOWS,
SQL_REQ_ID,
SQLCABC,
SQLCAID,
SQLCODE,
SQLERRD1,
SQLERRD2,
SQLERRD3,
SQLERRD4,
SQLERRD5,
SQLERRD6,
SQLERRM,
SQLERRP,
SQLSTATE,
SQLWARN,
START_TIME,
STATS_FABRICATE_TIME,
STMT_OPERATION,
STMT_TEXT,
STMT_TYPE,
STOP_TIME,
SYNC_RUNSTATS_TIME,
SYSTEM_CPU_TIME,
TOTAL_SORT_TIME,
TOTAL_SORTS,
USER_CPU_TIME ) ),
CONTROL (TABLE pot_evemon_ctrl
INCLUDES (EVENT_MONITOR_NAME,
MESSAGE,
MESSAGE_TIME ) )
BUFFERSIZE 16 BLOCKED MANUALSTART
/
CREATE PROCEDURE MONITOR_RS
(
IN v_spName VARCHAR(128)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE pkg_name VARCHAR(128);
DECLARE c1 CURSOR WITH RETURN FOR
SELECT (DAYS(STOP_TIME) - DAYS(START_TIME)) * 86400 +
(MIDNIGHT_SECONDS(STOP_TIME) - MIDNIGHT_SECONDS(START_TIME)) +
(MICROSECOND (STOP_TIME) - MICROSECOND (START_TIME))/1000000 ELAPSED_TIME,
ST.TEXT,
ROWS_READ,
ROWS_WRITTEN,
SYSTEM_CPU_TIME,
USER_CPU_TIME
FROM POT_EVEMON_STMT EV, SYSCAT.STATEMENTS ST
WHERE EV.PACKAGE_NAME = ST.PKGNAME
AND ST.PKGSCHEMA = USER
AND EV.SECTION_NUMBER = ST.SECTNO
AND EV.PACKAGE_NAME = pkg_name
ORDER BY ELAPSED_TIME DESC;
SELECT DEPS.BNAME PACKAGE
INTO pkg_name
FROM SYSIBM.SYSDEPENDENCIES DEPS,
SYSIBM.SYSROUTINES PROCS
WHERE DEPS.DTYPE = 'F'
AND DEPS.BTYPE = 'K'
AND PROCS.SPECIFICNAME = DEPS.DNAME
AND PROCS.ROUTINESCHEMA = DEPS.DSCHEMA
AND PROCS.ROUTINESCHEMA = USER
AND PROCS.ROUTINENAME = v_spName;
OPEN c1;
END
/
COMMIT
/
Step-C: Run Stored Procedure and Monitor
You can check MONITOR_SP and MONITOR_RS stored procedures from the Data Studio. We will run MONITOR_RS procedure to pull information from event monitor tables in which we captured data from the MONITOR_SP procedure.
db2 connect to sample
db2 "call MONITOR_RS('MONITOR_SP')"
db2 connect reset
You can also run the above procedure MONITOR_RS from the Data Studio and you will see the results as shown. This way, you can customize this information as per your requirements.
Results of the SP Monitoring