CA MICS Analyzer Option for DB2 (DB2) Queries › DB2T10 – DB2 Daily Top Ten Plans by Thread Count

DB2T10 – DB2 Daily Top Ten Plans by Thread Count

Overview

The DB2T10 query generates two daily charts for each DB2 Region. The charts shows resource usage for the top nn (default 10) DB2 Plans by count of threads initiated. Both charts show the number of threads initiated, using vertical bars. One of the charts shows the number of GETPAGE requests, and the other chart shows Class 1, Class 2, and Specialty engine CPU time use for the top ten plans.

The top example shows the daily GETPAGE and thread count for DB2 region D81A running on SYSID CA31. DB2 Plan "LATB140S" only had 27 threads initiated, but had almost eight million GETPAGE requests.

The second example shows the CPU time use for the same DB2 region. DB2 Plan "RCMOR121" used the most Class 1 CPU Time—3.65 minutes.

Uses

This query is useful to observe the resource utilization patterns for your top DB2 plans.

Data Extracts

DB2 Daily Top 10 Plans Threads and GPage

Left Y-axis

DSPNTHRD – Count of Threads Initiated

Right Y-axis

DSPSGPGR – Total GETPAGE Requests

X-axis

DB2PLAN – Plan Name

DB2 Daily Top 10 Plans Threads and CPU

Left Y-axis

DSPNTHRD – Count of Threads Initiated

Right Y-axis

DSPCP1TM – Total CP Class 1 CPU Time

DSPCP2TM – Total CP Class 2 CPU Time

DSPCPUZ1 – Accum CPU Tm used on z/OS Spec Eng

X-axis

DB2PLAN – Plan Name

Query Defaults

• DB2DSP DAYS timespan file: Cycle 01
• Derivations:

  DATE

  DATEPART(ENDTS)
  

  COUNT

  MAX(DSNPTHRD, SUM(DSPCOMM2,DSPABORT));
  

Modifications

The following modifications can be made to the query:

• You can add filter statements to select only a single SYSID or DB2ID, or select only specific production DB2 regions.
• Add additional chart views, updating the right Y-axis variable to show additional relationships for the top ten plan executions.

Query Defaults

• DB2DSP DAYS timespan file: Cycles 01 and 02
• User code selects yesterday’s data (IF DATEPART(ENDTS) EQ TODAY()-1 ;
• Derivations:

  DATE

  DATEPART(ENDTS) ;
  

  PLANEXEC

  MAX(DSNPTHRD,SUM(DSPCOMM2,DSPABORT));
  

  AVTCB

  DSPTCBTM / COUNT ;
  

  AVELAPSE

  DSPTELPT / COUNT ;
  

  AVGETPG

  DSPSGPGR / COUNT ;
  

  AVSYNCRD

  DSPSRIO / COUNT ;
  

  AVSYQPF

  DSPSPRFH / COUNT ;
  

  AVMXPGLK

  DSPMXPL / COUNT ;
  

  AVUPDPG

  DSPSSPRU / COUNT ;
  

Modifications

The following modifications can be made to the DB2T10 query:

• You can add filter statements to select only a single SYSID or DB2 Region (DB2ID).
• You can adjust the TOPnn value to something other than the default of 10.
• If you do not have the DAYS timespan DB2DSP file active, you can run the query against the DETAIL timespan, Cycles 01 and 02.