CA 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

• RC Compare for DB2 for z/OS
• Bind Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS
• SYSVIEW Performance Management Option for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS
• Database Analyzer for DB2 for z/OS
• Fast Unload for DB2 for z/OS
• Fast Check for DB2 for z/OS
• Fast Index for DB2 for z/OS
• Rapid Reorg for DB2 for z/OS

Issue/Introduction

Description:

CA Detector 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. Why is this the case?

Solution:

In the CA 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 ==> DB21
   
   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 ==> DB21
   
   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 ===> DB21
   
   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 & 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.

Environment

Release:
Component: PDT