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.
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 ==> 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.