DB2 Version 9.7 for Linux, UNIX, and Windows
DB2 Version 9.7 for Linux, UNIX, and Windows
The CURRENTSQL procedure generates a text-formatted report that summarizes currently running activities.
The CURRENTSQL procedure is available starting with DB2® Version 9.7 Fix Pack 1.
call monreport.currentsql;call monreport.currentsql(4); --------------------------------------------------------------------------------
Monitoring report - current SQL
--------------------------------------------------------------------------------
Database: WLMDISP1
Generated: 07/09/2010 12:51:57
-- Command options --
MEMBER: All
================================================================================
Part 1 - Summaries by 'top' metrics
Top 10 current activities by TOTAL_CPU_TIME
--------------------------------------------------------------------------------
ACTIVITY UOW_ID APPLICATION TOTAL_CPU STMT_TEXT
_ID _HANDLE _TIME
-------- ------ ----------- --------- --------------------------------------
3 4 1311047 59751047 MERGE INTO BEVERAGES.ALIAS56 USING ( se
4 1 1311151 44575834 INSERT INTO BEVERAGES.TABLE1555 (D53,CL
4 1 1311223 29313151 MERGE INTO BEVERAGES."Table 262" USING
4 1 1311232 26011687 MERGE INTO BEVERAGES.ATABLE22 USING ( s
2 10 1311194 18035723 MERGE INTO BEVERAGES."Table 43" USING (
3 1 1311258 16329733 INSERT INTO BEVERAGES.TABLE1187 (CLOB47
2 20 1311195 11195594 MERGE INTO BEVERAGES."Alias 40" USING (
5 3 1311173 6908942 MERGE INTO BEVERAGES.ALIAS15 USING ( se
3 2 1311279 5482031 (SELECT BEVERAGES."Table 60_0_2"."VarCh
6 9 1310992 5065525 MERGE INTO BEVERAGES.ATABLE221 USING (
Top 10 current activities by ROWS_READ
--------------------------------------------------------------------------------
ACTIVITY UOW_ID APPLICATION ROWS_READ STMT_TEXT
_ID _HANDLE
-------- ------ ----------- ------------- ----------------------------------
3 4 1311047 5603181 MERGE INTO BEVERAGES.ALIAS56 USING
4 1 1311223 2792712 MERGE INTO BEVERAGES."Table 262" US
4 1 1311232 2054767 MERGE INTO BEVERAGES.ATABLE22 USING
2 20 1311195 1047090 MERGE INTO BEVERAGES."Alias 40" USI
5 3 1311173 744899 MERGE INTO BEVERAGES.ALIAS15 USING
6 9 1310992 481934 MERGE INTO BEVERAGES.ATABLE221 USIN
3 2 1311287 166066 DELETE FROM BEVERAGES.TABLE1185 COR
3 2 1311279 135854 (SELECT BEVERAGES."Table 60_0_2"."V
4 1 1311151 9357 INSERT INTO BEVERAGES.TABLE1555 (D5
3 1 1311258 8097 INSERT INTO BEVERAGES.TABLE1187 (CL
Top 10 current activities by DIRECT READS + DIRECT WRITES
--------------------------------------------------------------------------------
ACTIVITY UOW_ID APPLICATION DIRECT_READS + STMT_TEXT
_ID _HANDLE DIRECT_WRITES
-------- ------ ----------- -------------- ---------------------------------
2 3 1311230 7604 SELECT CORR_1."Bigint 74" AS "Big
1 5 656020 354 CALL monreport.currentsql()
3 4 1311047 0 MERGE INTO BEVERAGES.ALIAS56 USING
3 2 1311279 0 (SELECT BEVERAGES."Table 60_0_2"."
4 1 1311151 0 INSERT INTO BEVERAGES.TABLE1555 (D
6 9 1310992 0 MERGE INTO BEVERAGES.ATABLE221 USI
5 3 1311173 0 MERGE INTO BEVERAGES.ALIAS15 USING
2 20 1311195 0 MERGE INTO BEVERAGES."Alias 40" US
4 1 1311232 0 MERGE INTO BEVERAGES.ATABLE22 USIN
4 1 1311223 0 MERGE INTO BEVERAGES."Table 262" U
...
798 record(s) selected.
Return Status = 0================================================================================
Part 2 - Overall ranking of activities
ACTIVITY UOW_ID APPLICATION TOTAL_CPU ROWS_READ DIRECT_READS +
_ID _HANDLE _TIME DIRECT_WRITES
-------- ------ ----------- --------- ------------- ----------------------
3 4 1311047 2 1 9
4 1 1311223 6 2 9
4 1 1311232 7 3 9
2 20 1311195 12 5 9
5 3 1311173 14 6 9
6 9 1310992 16 7 9
4 1 1311151 4 20 9
3 2 1311279 15 11 9
3 1 1311258 10 21 9
3 2 1311287 22 10 9
2 10 1311194 9 28 9
2 29 1311224 17 29 9
3 10 1311033 25 36 9
2 3 1311230 41 32 1
5 1 1311113 37 35 9
3 21 1311172 36 40 9
3 1 1311256 34 43 9
3 1 1311113 39 39 9
3 1 1311276 31 49 9
3 1 1311286 47 33 9
2 9 1311293 49 34 9
2 7 1311278 35 49 9
1 5 656020 49 41 4
3 14 1310972 38 49 9
2 4 1311234 40 49 9
2 3 1311291 46 44 9
2 15 1311252 44 46 9
3 16 1311247 42 48 9
4 1 1311266 43 49 9
2 8 1311281 45 49 9
3 6 1311294 48 48 9
62 5 656020 49 49 9
================================================================================
Part 3 - Complete statement text for activities
...
--------------------------------------------------------------------------------
ACTIVITY_ID/UOW_ID/APPLICATION_HANDLE = 4/1/1311266
UPDATE BEVERAGES.ATABLE209 SET "Decimal 151" = ?, F226 = ?, D135 = ? WHERE NOT HE
X(((((CAST(NULL AS INTEGER)))))) IN (BEVERAGES.ATABLE209.C25, BEVERAGES.ATABLE209.
"VarChar 166", UCASE(BEVERAGES.ATABLE209.BC229), NULLIF(BEVERAGES.ATABLE209.C184,R
TRIM(BEVERAGES.ATABLE209.BVC102))) OR NOT BEVERAGES.ATABLE209.BIG13 IN BEVERAGES.AT
ABLE209."Decimal 19"
--------------------------------------------------------------------------------
ACTIVITY_ID/UOW_ID/APPLICATION_HANDLE = 2/8/1311281
SELECT ((VARCHAR_FORMAT('4931-02-13 23:44:42','YYYY-MM-DD HH24:MI:SS'))) C147,-2
5358 AS S136,TANH (.498504640) F132 FROM FINAL TABLE (UPDATE BEVERAGES."Table 42
2" CORR_1 SET BVC131 = ?, "Time 144" = '19.59.37', TM258 = ?, S73 = ? WHERE CORR
_1."Char 188"<>CORR_1.C138 OR CAST(? AS CHAR(120))!=CORR_1.VC109) "Corr.1" WHERE
BIGINT("Corr.1".BIG217)!=IDENTITY_VAL_LOCAL() AND NOT IDENTITY_VAL_LOCAL()>="Co
rr.1"."Small 242" AND NOT VARCHAR_FORMAT('5266-03-31 13:32:55','YYYY-MM-DD HH24:
MI:SS')="Corr.1"."VarChar 267" FOR FETCH ONLY
--------------------------------------------------------------------------------
ACTIVITY_ID/UOW_ID/APPLICATION_HANDLE = 3/6/1311294
SELECT ALL (CORR_1.BLOB86) BLOB80,CORR_1.TS85 TS172 FROM BEVERAGES."Table 60_1_1
" AS CORR_1 WHERE NOT CORR_1.DT157 NOT IN CAST(? AS DATE) FOR UPDATE
...