DB2 SQL记录方式
-
打开DB2环境CMD窗口输入db2 回车
-
开启时间监视器DB2=>Update monitor switches using statement on
-
创建SQL记录表 DB2=>create event monitor sql_trace for statements write totable
-
开启SQL监控 DB2=>Set event monitor sql_trace state=1
监控会话处于开大状态,直到这些数据库活动完成,才会记录到stmt_sql_trace数据表中。
-
关闭SQL监控 DB2=>Set event monitor sql_trace state=0
DB2=>terminate
-
按照执行时间降序排列执行耗时最长的SQL语句。
Select stmt_text,(stop_time-start_time) asexecutiontime from stmt_sql_trace where stmt_operation not in (7,8,9,19) orderby decimal(executiontime) desc fetch first 10 rows only;
按照频率降序排列执行次数最多的SQL语句。
Select distinct(stmt_text),count(*) countfrom stmt_sql_trace where stmt_operation not in (7,8,9,19) group by stmt_text order by count(*) descfetchfirst 10 row only
按照CPU时间降序排列最耗CPU时间的SQL语句。
Select stmt_text,user_cpu_time asusercpu from stmt_sql_trace wherestmt_operation not in (7,8,9,19) order by user_cpu_time fetch first 10 rowsonly;
按照总排序时间降序排列排序时间最长的SQL语句。
Select stmt_text,total_sort_time astotalsorttime from stmt_sql_trace where stmt_operation not in (7,8,9,19) orderby decimal(total_sort_time) desc fetch first 10 rows only;
注意事项:可根据特定的时间进行监控,不需要一直打开。
DB2 SQL 快照抓取(抓取当前执行SQL)
-
打开CMD窗口 输入 db2pd –db omkdms –apinfoall >>e:\\****.txt
-
打开****.txt 查看相关SQL语句
Application :
Address : 0x000007FFFE240080
AppHandl[nod-index] : 3085 [000-03085]
TranHdl : 274
Application PID: 0
Application NodeName : zd-PC
IP Address: 192.168.16.214
Connection StartTime : (1422853141)Mon Feb 02 12:59:012015
Client User ID: n/a
System Auth ID: DMS
Coordinator EDUID : 7124
CoordinatorPartition : 0
Number of Agents: 1
Locks timeoutvalue : NotSet
Locks Escalation: No
Workload ID: 1
WorkloadOccurrence ID : 1022
Trusted Context: n/a
Connection TrustType : non trusted
Role Inherited: n/a
ApplicationStatus : UOW-Waiting
Application Name: db2jcc_application
Application ID: 192.168.16.214.59360.1502020459
ClientUserID: n/a
ClientWrkstnName: zd-PC
ClientApplName: n/a
ClientAccntng: n/a
CollectActData: N
CollectActPartition: C
SectionActuals: N
List of activestatements :
UOW-ID : 2
Activity ID: 1
Package Schema: NULLID
Package Name: SYSSH200
Package Version:
Section Number: 1
SQL Type : Dynamic
Isolation: CS
Statement Type: DML, Select (blockable)
Statement: select WAYS ,GYW ,GCPX
,GJSSXH ,GFDJXH,GHQXH ,GLTGG ,GQDXS from ( select a.product_id,c.SALE_ORG||c.SALEWAY||c.PRODUCT_GROUP as WAYS ,max(case when
property_code='GYW' then VALUE_CODE else '' end) as GYW ,max(case whenproperty_code='GCPX' then VALUE_CODE else '' end) as
GCPX ,max(casewhen property_code='GJSSXH' then VALUE_CODE else '' end) as GJSSXH ,max(casewhen property_code='GFDJXH' then
VALUE_CODE else'' end) as GFDJXH ,max(case when property_code='GHQXH' then VALUE_CODE else ''end) as GHQXH ,max(case when
property_code='GLTGG' then VALUE_CODE else '' end) as GLTGG ,max(casewhen property_code='GQDXS' then VALUE_CODE else '' end)
as GQDXS fromtm_product_value a,tm_product b,TM_PRODUCT_SALES_VIEW c,OB_SALES_ORG d wherea.product_id = b.product_id and
a.product_id =c.product_id and c.SALE_ORG = d.SALE_ORG and b.product_group = c.product_groupand d.COMPANY_CODE='2240'group by a.product_id,d.COMPANY_CODE,c.SALE_ORG,c.SALEWAY,c.PRODUCT_GROUP) x group by WAYS ,GYW ,GCPX ,GJSSXH ,GFDJXH ,GHQXH ,GLTGG
,GQDXS
欢迎加群交流 JAVA开发交流 246844233