DB2TOP 查询正在执行的SQL 语句
db2top方法：
1. db2top -db sample
2. 输入l选项，查看application执行状态，找到UOW Executing 状态的application行，得到Application Handle
3. 输入a选项，输入获取的Application Handle
4. 显示出application的状态信息及statement
例子：
1）通过l命令选项查看session信息，得到正在执行的Application Handle，以下为1390
[]16:10:59,refresh=2secs(0.059) Sessions AIX,part=[8/8],DB2INST1:BSSDB
[d=Y,a=N,e=N,p=ALL] [qp=off]
Please enter agent id: 1390
lqqqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqk
Local sessions...: 3 x x 25%x 50%x 75%x 100%x Local in exec....: 0
Remote sessions..: 40 xActSessions x x Remote in exec...: 0
FCM buffers sent.: 169073450 xSys/Usr Cpu% x x Active utilities.: 0
FCM buffers rec..: 129353297 xr+w/sec% x x FCM buffers LWM..: 268
Piped sorts req..: 163,863 mqqqqqqqqqqqqqqvqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj Piped sorts acc..: 163,863
Max Agents.......: 600 Agents stolen....: 18,644 Agent overflows..: 0 Connections HWM..: 91
Sheapthres.......: 0 Max Sort Heap....: 0 Sort heap alloc..: 0 Private memory...: 28.3M
Dynamic SQL stmts: 310 Static SQL stmts.: 244 Post hash joins..: 0 Post sorts.......: 59
Bufferpool reads.: 0 Bufferpool writes: 0 Direct reads.....: 0 Direct writes....: 0
Application Cpu% IO% Mem% Application Application Delta Delta Delta Delta Delta Sess
Handle(Stat) Total Total Total Status Name RowsRead/s RowsWritten/s IOReads/s IOWrites/s TQr+w/s Memory
------------ ------- ------- ------- ----------------------------------- --------------- -------------- -------------- -------------- -------------- -------------- --------
1390(*) 0.00% 0.00% 11.32% UOW Executing Toad.exe 0 0 0 0 0 7.7M
52(i) 0.00% 0.00% 3.38% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 2.3M
53(c) 0.00% 0.00% 0.18% Connected db2taskd 0 0 0 0 0 128.0K
54(c) 0.00% 0.00% 1.46% Connected db2evmg_DB2DETA 0 0 0 0 0 1.0M
85(i) 0.00% 0.00% 0.46% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 320.0K
152(i) 0.00% 0.00% 5.02% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 3.4M
477(i) 0.00% 0.00% 11.42% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 7.8M
478(i) 0.00% 0.00% 11.23% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 7.6M
613(i) 0.00% 0.00% 0.46% UOW Waiting in the application Toad.exe 0 0 0 0 0 320.0K
622(i) 0.00% 0.00% 0.37% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 256.0K
628(i) 0.00% 0.00% 0.73% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 512.0K
634(i) 0.00% 0.00% 1.37% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 960.0K
656(i) 0.00% 0.00% 2.65% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 1.8M
666(i) 0.00% 0.00% 16.71% UOW Waiting in the application Toad.exe 0 0 0 0 0 11.4M
667(i) 0.00% 0.00% 0.64% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 448.0K
668(i) 0.00% 0.00% 1.37% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 960.0K
690(i) 0.00% 0.00% 0.91% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 640.0K
695(i) 0.00% 0.00% 2.19% UOW Waiting in the application WIReportServer. 0 0 0 0 0 1.5M
697(i) 0.00% 0.00% 0.55% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 384.0K
719(i) 0.00% 0.00% 1.37% UOW Waiting in the application Toad.exe 0 0 0 0 0 960.0K
720(i) 0.00% 0.00% 1.92% UOW Waiting in the application db2bp 0 0 0 0 0 1.3M
754(i) 0.00% 0.00% 0.55% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 384.0K
893(i) 0.00% 0.00% 0.18% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 128.0K
905(i) 0.00% 0.00% 1.46% UOW Waiting in the application Toad.exe 0 0 0 0 0 1.0M
Quit: q, Help: h 1 active session(s) out of 43 db2top 2.
2）输入a命令选项，输入agentid：1390，得到正在执行的1390 agentid的信息：
(*]16:11:21,refresh=22!secs(0.014) Sessions AIX,part=[8/8],DB2INST1:BSSDB
[d=Y,a=N,e=N,p=ALL] [qp=off]
130.30.3.195.22748.110809064856 (130.30.3.195 56408], UOW Executing
ConnTime..: 14:49:18.028 UOW Start.: 16:01:44.758 Appl name.: Toad.exe DB2 user..: PAPP OS user...: HUWJ
Agent id..: 1390 Coord DBP.: 0 Coord id..: 421910 Client pid: 3608 Hash joins: 91
Hash loops: 0 HJoin ovf.: 0 SQL Stmts.: 15 Sorts.....: 151 Sort time.: 10.338
Sorts ovf.: 23 Rows Read.: 57,119,653 Rows Sel..: 12,215 Read/Sel..: 4,676 Rows Wrtn.: 51,584
Rows Ins..: 0 Rows Upd..: 0 Rows Del..: 0 Locks held: 84 Trans.....: 81
Open Curs.: 0 Rem Cursor: 6 Memory....: 7.7M Dyn. SQL..: 133 Static SQL: 25
Cpu Time..: 115.607018 AvgCpuStmt: 0.731
Dynamic statement [Cursor SQL_CURSH200C9] - Last operation Fetch
Sub Cpu Cpu Row Rows Rows TqRows TqRows Tq Exec # of SubSection Waiting
Sec (Sys+Usr) Skew Skew Read Written Read Written Spills Memory Time DBP Ag. Status TQueue(s)
--- ------------ ---- ---- ------------ ------------ ------------ ------------ ---------- -------- ------ ---- --- -------------- ----------
0 0.103 0% 0% 0 0 1,872 0 0 0 545 1 0 E1
1 2.771 19% 0% 0 0 65,103 4,294 0 2.1M 545 7 7 S7 q0
2 13.131 1% 1% 8,829,126 0 140 65,103 0 0 2 7 0 C7
3 0.495 0% 0% 23 0 0 20 0 0 0 1 0 C1
SELECT t1.chnl_cde ,t1.svc_type_cde ,case when t2.SVC_CLS_CDE='01' then t1.BRAND_TYPE_CDE when t2.SVC_CLS_CDE in ('
03','04','05','06','07','08') then '-1' end ,sum(case when t1.LOGON_USER_FLAG='1' OR (t1.deal_date=20110831 and substr(t1
.USER_STAT_CDE,2,1) in('B','D','L') AND substr(t1.STAT_MODF_DATE,1,6)='201104') then 1 else 0 end) as logout_user_num ,sum(case when t1.NEW_US
ER_FLAG='1' then 1 else 0 end) as new_user_num ,sum(case when t1.INNET_FLAG='1' then 1 else 0 end) as online_user_num FROM PDW.T_DWU_PAR_ALL_USER_STA
T_D t1, pcde.t_dim_loc_svc_type t2 where t1.svc_type_cde=t2.svc_type_cde and t1.chnl_type_cde in('8300','9101') and t2.SVC_CLS_CDE in ('01
','03','04','05','06','07','08') group by t1.chnl_cde ,t1.svc_type_cde ,case when t2.SVC_CLS_CDE='01' then t1.BRAND_TYPE_CDE
when t2.SVC_CLS_CDE in ('03','04','05','06','07','08') then '-1' end
Dynamic statement - Last operation Execute Immediate
--- ------------ ---- ---- ------------ ------------ ------------ ------------ ---------- -------- ------ ---- --- -------------- ----------
INSERT INTO MARTRPT.T_RPT_GRP_MGR_CHNL_D( deal_date --处理日期
,region_cde --地市
,city_cde --区县
,svc_type_cde --业务类型
,chnl_type_name --渠道类型描述
,brand_cde --业务小类
Quit: q, Help: h Tot cpu 66.000075, associated memory 6.0M (total 7.7M), enter to refresh db2top 2.
c）可输入h获取命令选项，查看完整语句或进行语句执行计划的查看等，如L命令选项，看到具体查询文本（如上只显示部分语句问题）。
1. db2top -db sample
2. 输入l选项，查看application执行状态，找到UOW Executing 状态的application行，得到Application Handle
3. 输入a选项，输入获取的Application Handle
4. 显示出application的状态信息及statement
例子：
1）通过l命令选项查看session信息，得到正在执行的Application Handle，以下为1390
[]16:10:59,refresh=2secs(0.059) Sessions AIX,part=[8/8],DB2INST1:BSSDB
[d=Y,a=N,e=N,p=ALL] [qp=off]
Please enter agent id: 1390
lqqqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqk
Local sessions...: 3 x x 25%x 50%x 75%x 100%x Local in exec....: 0
Remote sessions..: 40 xActSessions x x Remote in exec...: 0
FCM buffers sent.: 169073450 xSys/Usr Cpu% x x Active utilities.: 0
FCM buffers rec..: 129353297 xr+w/sec% x x FCM buffers LWM..: 268
Piped sorts req..: 163,863 mqqqqqqqqqqqqqqvqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj Piped sorts acc..: 163,863
Max Agents.......: 600 Agents stolen....: 18,644 Agent overflows..: 0 Connections HWM..: 91
Sheapthres.......: 0 Max Sort Heap....: 0 Sort heap alloc..: 0 Private memory...: 28.3M
Dynamic SQL stmts: 310 Static SQL stmts.: 244 Post hash joins..: 0 Post sorts.......: 59
Bufferpool reads.: 0 Bufferpool writes: 0 Direct reads.....: 0 Direct writes....: 0
Application Cpu% IO% Mem% Application Application Delta Delta Delta Delta Delta Sess
Handle(Stat) Total Total Total Status Name RowsRead/s RowsWritten/s IOReads/s IOWrites/s TQr+w/s Memory
------------ ------- ------- ------- ----------------------------------- --------------- -------------- -------------- -------------- -------------- -------------- --------
1390(*) 0.00% 0.00% 11.32% UOW Executing Toad.exe 0 0 0 0 0 7.7M
52(i) 0.00% 0.00% 3.38% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 2.3M
53(c) 0.00% 0.00% 0.18% Connected db2taskd 0 0 0 0 0 128.0K
54(c) 0.00% 0.00% 1.46% Connected db2evmg_DB2DETA 0 0 0 0 0 1.0M
85(i) 0.00% 0.00% 0.46% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 320.0K
152(i) 0.00% 0.00% 5.02% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 3.4M
477(i) 0.00% 0.00% 11.42% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 7.8M
478(i) 0.00% 0.00% 11.23% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 7.6M
613(i) 0.00% 0.00% 0.46% UOW Waiting in the application Toad.exe 0 0 0 0 0 320.0K
622(i) 0.00% 0.00% 0.37% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 256.0K
628(i) 0.00% 0.00% 0.73% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 512.0K
634(i) 0.00% 0.00% 1.37% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 960.0K
656(i) 0.00% 0.00% 2.65% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 1.8M
666(i) 0.00% 0.00% 16.71% UOW Waiting in the application Toad.exe 0 0 0 0 0 11.4M
667(i) 0.00% 0.00% 0.64% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 448.0K
668(i) 0.00% 0.00% 1.37% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 960.0K
690(i) 0.00% 0.00% 0.91% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 640.0K
695(i) 0.00% 0.00% 2.19% UOW Waiting in the application WIReportServer. 0 0 0 0 0 1.5M
697(i) 0.00% 0.00% 0.55% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 384.0K
719(i) 0.00% 0.00% 1.37% UOW Waiting in the application Toad.exe 0 0 0 0 0 960.0K
720(i) 0.00% 0.00% 1.92% UOW Waiting in the application db2bp 0 0 0 0 0 1.3M
754(i) 0.00% 0.00% 0.55% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 384.0K
893(i) 0.00% 0.00% 0.18% UOW Waiting in the application db2jcc_applicat 0 0 0 0 0 128.0K
905(i) 0.00% 0.00% 1.46% UOW Waiting in the application Toad.exe 0 0 0 0 0 1.0M
Quit: q, Help: h 1 active session(s) out of 43 db2top 2.
2）输入a命令选项，输入agentid：1390，得到正在执行的1390 agentid的信息：
(*]16:11:21,refresh=22!secs(0.014) Sessions AIX,part=[8/8],DB2INST1:BSSDB
[d=Y,a=N,e=N,p=ALL] [qp=off]
130.30.3.195.22748.110809064856 (130.30.3.195 56408], UOW Executing
ConnTime..: 14:49:18.028 UOW Start.: 16:01:44.758 Appl name.: Toad.exe DB2 user..: PAPP OS user...: HUWJ
Agent id..: 1390 Coord DBP.: 0 Coord id..: 421910 Client pid: 3608 Hash joins: 91
Hash loops: 0 HJoin ovf.: 0 SQL Stmts.: 15 Sorts.....: 151 Sort time.: 10.338
Sorts ovf.: 23 Rows Read.: 57,119,653 Rows Sel..: 12,215 Read/Sel..: 4,676 Rows Wrtn.: 51,584
Rows Ins..: 0 Rows Upd..: 0 Rows Del..: 0 Locks held: 84 Trans.....: 81
Open Curs.: 0 Rem Cursor: 6 Memory....: 7.7M Dyn. SQL..: 133 Static SQL: 25
Cpu Time..: 115.607018 AvgCpuStmt: 0.731
Dynamic statement [Cursor SQL_CURSH200C9] - Last operation Fetch
Sub Cpu Cpu Row Rows Rows TqRows TqRows Tq Exec # of SubSection Waiting
Sec (Sys+Usr) Skew Skew Read Written Read Written Spills Memory Time DBP Ag. Status TQueue(s)
--- ------------ ---- ---- ------------ ------------ ------------ ------------ ---------- -------- ------ ---- --- -------------- ----------
0 0.103 0% 0% 0 0 1,872 0 0 0 545 1 0 E1
1 2.771 19% 0% 0 0 65,103 4,294 0 2.1M 545 7 7 S7 q0
2 13.131 1% 1% 8,829,126 0 140 65,103 0 0 2 7 0 C7
3 0.495 0% 0% 23 0 0 20 0 0 0 1 0 C1
SELECT t1.chnl_cde ,t1.svc_type_cde ,case when t2.SVC_CLS_CDE='01' then t1.BRAND_TYPE_CDE when t2.SVC_CLS_CDE in ('
03','04','05','06','07','08') then '-1' end ,sum(case when t1.LOGON_USER_FLAG='1' OR (t1.deal_date=20110831 and substr(t1
.USER_STAT_CDE,2,1) in('B','D','L') AND substr(t1.STAT_MODF_DATE,1,6)='201104') then 1 else 0 end) as logout_user_num ,sum(case when t1.NEW_US
ER_FLAG='1' then 1 else 0 end) as new_user_num ,sum(case when t1.INNET_FLAG='1' then 1 else 0 end) as online_user_num FROM PDW.T_DWU_PAR_ALL_USER_STA
T_D t1, pcde.t_dim_loc_svc_type t2 where t1.svc_type_cde=t2.svc_type_cde and t1.chnl_type_cde in('8300','9101') and t2.SVC_CLS_CDE in ('01
','03','04','05','06','07','08') group by t1.chnl_cde ,t1.svc_type_cde ,case when t2.SVC_CLS_CDE='01' then t1.BRAND_TYPE_CDE
when t2.SVC_CLS_CDE in ('03','04','05','06','07','08') then '-1' end
Dynamic statement - Last operation Execute Immediate
--- ------------ ---- ---- ------------ ------------ ------------ ------------ ---------- -------- ------ ---- --- -------------- ----------
INSERT INTO MARTRPT.T_RPT_GRP_MGR_CHNL_D( deal_date --处理日期
,region_cde --地市
,city_cde --区县
,svc_type_cde --业务类型
,chnl_type_name --渠道类型描述
,brand_cde --业务小类
Quit: q, Help: h Tot cpu 66.000075, associated memory 6.0M (total 7.7M), enter to refresh db2top 2.
c）可输入h获取命令选项，查看完整语句或进行语句执行计划的查看等，如L命令选项，看到具体查询文本（如上只显示部分语句问题）。
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞4Ctrl+Enter 发表
作者其他文章
评论 1 · 赞 0
评论 0 · 赞 0
评论 3 · 赞 2
评论 0 · 赞 0
评论 2 · 赞 0
添加新评论4 条评论
2018-08-09 20:29
2018-08-09 16:59
2013-07-25 10:15
2013-07-19 23:05