DB2_SQL监控

DB2 SQL记录方式

1. 1. 打开DB2环境CMD窗口输入db2 回车

   2. 开启时间监视器DB2=>Update monitor switches using statement on

   3. 创建SQL记录表 DB2=>create event monitor sql_trace for statements write totable

   4. 开启SQL监控 DB2=>Set event monitor sql_trace state=1

      监控会话处于开大状态，直到这些数据库活动完成，才会记录到stmt_sql_trace数据表中。

   5. 关闭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)

1. 打开CMD窗口 输入 db2pd –db omkdms –apinfoall >>e:\\****.txt

2. 打开****.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