Detector for Db2 for z/OS Standard Activity commit and abort counts are not displayed within a SQL query of the Detector archive tables.

Products

• Detector for DB2 for z/OS

Issue/Introduction

Detector for Db2 for z/OS (PDT) Standard Activity commit and abort counts are not displayed within a SQL query of Detector
archive tables even though they appear in the Detector datastore.

Resolution

In the Detector datastore the commit and abort counts appear only on the Plan or Key Summary screens.

Below is an example of the counts displayed by Plan:

  R11.05 > --------- DETECTOR Planname Summary Display -------- 09/12/03 08:19
   DB2 SSID ==> ssid
   
   View By ==> P * -Plan G -Prog S -SQL Q -DSQL F -Prof K -Key Total/Avg ==>
   
   PLANNAME   COMMIT   ABORT 
   ---------- -------- ------ 
   _ PLAN1      17       3 
   _ PLAN2      12       8
   _ PLAN3       1       9 
   _ PLAN4       0      11

Even when the counts are displayed by Key, they have previously been computed by Plan, as the following screens show.

If USER1 is selected:

 R11.05 > --------- DETECTOR Key Summary Display -------- 09/12/03 08:19
   DB2 SSID ==> ssid
   
   View By ==> K P -Plan G -Prog S -SQL Q -DSQL F -Prof * -Key Total/Avg ==>
   
   KEY          COMMIT   ABORT   SQL 
   ------------ -------- ------  ---------
   s USER1        30       31    714553 
   _ USER2       541       10     11893
   _ USER3        55      107     27489

..Key Commit and Abort total counts that were displayed on the previous screen were computed using the counts below:

 R11.05 > ----------- DETECTOR Key Planname Display ---------- 09/12/03 08:51
   
   Userid ==> USER1 DB2 SSID ===> ssid
   
   PLANNAME   COMMIT   ABORT 
   ---------- -------- ------ 
   _ PLAN1     17        3 
   _ PLAN2     12        8
   _ PLAN3      1        9 
   _ PLAN4      0       11

Similarly, on the Detector Standard Activity archive table, the rows with a RECTYPE equal to 'PLAN' contain the commit and abort counts.
The rows for Plans 1 through 4 would appear as follows in an Interactive SQL report:

 RECTYPE PLANNAME PROGRAM COMMIT ABORT
   PLAN    PLAN1            17      3 
   PLAN    PLAN2            12      8
   PLAN    PLAN3             1      9
   PLAN    PLAN4             0     11

The remaining record types do not contain these counts. The remaining rows for PLAN1 along with their record types appear below:

 RECTYPE PLANNAME PROGRAM COMMIT ABORT
   PKGP   PLAN1    PLAN1    0      0 
   PGM    PLAN1    PLAN1    0      0 
   STMT   PLAN1    PLAN1    0      0

..so, RECTYPE PLAN must be used to retrieve the commit and abort counts.