Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Ops Monitor for Db2 13.1 image-2024-5-19_8-5-1.png .

Summary SQL report by PGM/PKG with workloads (BSQSUMPW)

The following figure shows a sample of the summary SQL report by PGM/PKG with workloads (BSQSUMPW).

Warning

Note

This report is based on IFCIDs 3, 6-12, 17-20, 22, 44, 45, 53, 55, 58-62, 64-66, 68-75, 84-89, 92, 95-97, 108, 109, 125, 174, 175, 177, 183, 188, 213-216, 218, 221, 226, 227, 237, 239, 247, 305, and 324.

 1                       BSQSUMPW: SQL SUMMARY RPT BY PGM   W/WKL                         11/04/14 12:40:32    PAGE    21

    LOCATION: DGR                                                                                    DATA FROM: 05/28/2013 14:03:07
     D/S GRP: DSNDGR   MBR: DGR1                      SQL SUMMARY REPORT BY PGM/PKG                         TO: 08/15/2013 16:26:42
        SSID: DGR1     VER: 10.1
                                                PRIME AUTHID RDHJMB
                                                   PLAN NAME DGRRRS01                            THREAD COUNT:        6

    PACKAGE/PGM             TOT ELAPSED  TOT TCBTIME
     NAME            COUNT  AVG ELAPSED  AVG TCBTIME                               DETAILS
    ============= ======== ============ ============ ===============================================================================

     DGRRRS01            3 00:01.337225  00.142221 PKG:  DGR             . DGRRRS0           . DGRRRS01          . 196CEE740CA11731
                           00:00.445742  00.047407
                                                    ACQUIRE: U        REOPT: NOREOP      RELEASE: C       DYNAMICRULES: RUN
                                                    PREPARE: N          ISO: CS         PROTOCOL: D        KEEPDYNAMIC: N
                                                   IMMEDWRT: NO     OPTHINT:

                                                   SQL STATEMENT/EVENT        COUNT   AVG ELAPSED   AVG CPU TIME
                                                   ----------------------  --------  ------------   ------------
                                                   DESC/COMMIT/ROLLBK/ERR        84           N/A            N/A  (IFCID 53)

                           --- WORKLOAD HILITES --------------------------------------------------------------------------
                            SORTS    :       0  WKFILS/SORT:     N/P  RIDS UNUSED   :       0  LOB PAGES SCANNED:       0
                            RECS/SORT:     N/P  PASSES/SORT:     N/P  DEGREE REDUCED:       0  LOB PAGES UPDATED:       0
                            DTL TRC REC CNTS:   MINI BINDS :       0  HOST VARIABLES:     420  FUNCTION RESOLTNS:       0

1                       BSQSUMPW: SQL SUMMARY RPT BY PGM   W/WKL                         11/04/14 12:40:32    PAGE    22

    LOCATION: DGR                                                                                    DATA FROM: 05/28/2013 14:03:07
     D/S GRP: DSNDGR   MBR: DGR1                      SQL SUMMARY REPORT BY PGM/PKG                         TO: 08/15/2013 16:26:42
        SSID: DGR1     VER: 10.1
                                                PRIME AUTHID RDHJMB2
                                                   PLAN NAME DMRSEL01                            THREAD COUNT:        1

    PACKAGE/PGM             TOT ELAPSED  TOT TCBTIME
     NAME            COUNT  AVG ELAPSED  AVG TCBTIME                               DETAILS
    ============= ======== ============ ============ ===============================================================================
     N/A
                                                   EVENT                      COUNT   AVG ELAPSED   AVG CPU TIME
                                                   ----------------------  --------  ------------   ------------
                                                   CREATE THREAD                  1  00:00.003475   00:00.001266

                      --- WORKLOAD HILITES -----------------------------------------------------------------------------------------
                      SCANS   :     14   IO REQS :       30  SUSPENDS  :       38  EXIT CALLS :      168  AMS CALLS     :        0
                      ROWSPROC:    707   IO AVELA: 0.002315  SUSP AVELA:        0  EXIT AVELAP: 0.000002  AMS AVGELAPSED:      N/P
                      PAGSSCAN:    128                       DATACAPTUR: NO        CHKCONSTOK :        0  CHKCONSTREJECT:        0

                      --- EXITS ------------------------------------------------------------------
                       VALIDATION CALLS  AVG ELAPSED       EDIT EXIT CALLS  AVG ELAPSED
                       ----------------  -----------       ---------------  -----------
                                      0          N/P                   168     0.000002

                      --- I/O ACTIVITY --------------------------------------------------------------------------------------------
                       --IO REQUESTS--  --------READ REQUESTS (WITH OR WITHOUT I/O)------  -------------WRITE REQUESTS-------------
                         TOTAL AVGELAP    TOTAL  TYPE  AVGIOELAP     %IO PAGES/IO   %NOIO     TOTAL TYPE  CAST% AVGIOELAP PAGES/WRT
                       ------- -------  -------  ----  ---------  ------ --------  ------   ------- ---- ------ --------- ---------
                            30 00.0023       30  SYNC    00.0023  100.00     1.00    0.00         0 ASYN    N/P       N/P       N/P

                      --- LOCK SUSPENSION ACTIVITY --------------------------------------------------------------------------
                                     --------- SUSPEND REASON ----------  NORMAL RESUME    TIMEOUT RESUME   DEADLCK RESUME
                       TYPE             LOCAL LATCH IRLMQ GROUP NOTIF OTHER  COUNT AVGELAPS   COUNT AVGELAPS   COUNT AVGELAPS
                       -----------      ----- ----- ----- ----- ----- -----  ----- --------   ----- --------   ----- --------
                       NORMAL LOCK          0     0     0    32     2     0      0      N/P       0      N/P       0      N/P
                       DRAIN                0     0     0     6     0     0      0      N/P       0      N/P       0      N/P

                      --- SCAN ACTIVITY -----------------------------------------------------------------------------------------
                       ------ROWS------ --QUALIFIED AT--- ----------ROWS------------  -PAGES-  -------RI--------
                        SCANS   PROCESS  EXAMINE  STAGE 1  STAGE 2  INSERTS  UPDATES  DELETES  SCANNED     SCANS  DELETES
                       ------  -------- -------- -------- -------- -------- -------- -------- --------  -------- --------
                           14       707      707      707      707        0        0        0      128         0        0

1                       BSQSUMPW: SQL SUMMARY RPT BY PGM   W/WKL                         11/04/14 12:40:32    PAGE    23

    LOCATION: DGR                                                                                    DATA FROM: 05/28/2013 14:03:07
     D/S GRP: DSNDGR   MBR: DGR1                      SQL SUMMARY REPORT BY PGM/PKG                         TO: 08/15/2013 16:26:42
        SSID: DGR1     VER: 10.1
                                                PRIME AUTHID RDHJMB2
                                                   PLAN NAME DMRSEL01                            THREAD COUNT:        1

    PACKAGE/PGM             TOT ELAPSED  TOT TCBTIME
     NAME            COUNT  AVG ELAPSED  AVG TCBTIME                               DETAILS
    ============= ======== ============ ============ ===============================================================================

     DMRCAT1             1 00:04.698310  00.037609 PKG:  DGR             . DMRQA             . DMRCAT1           . 196C9F9E052047FD
                           00:04.698310  00.037609
                                                    ACQUIRE: U        REOPT: NOREOP      RELEASE: C       DYNAMICRULES: RUN
                                                    PREPARE: N          ISO: RS         PROTOCOL: D        KEEPDYNAMIC: N
                                                   IMMEDWRT: NO     OPTHINT:

                                                   SQL STATEMENT/EVENT        COUNT   AVG ELAPSED   AVG CPU TIME
                                                   ----------------------  --------  ------------   ------------
                                                   FETCH                        172  00:00.026921   00:00.000086
                                                   OPEN                           4  00:00.000120   00:00.000112
                                                   CLOSE                          4  00:00.000067   00:00.000064

                           --- WORKLOAD HILITES --------------------------------------------------------------------------
                            SORTS    :       0  WKFILS/SORT:     N/P  RIDS UNUSED   :       0  LOB PAGES SCANNED:       6
                            RECS/SORT:     N/P  PASSES/SORT:     N/P  DEGREE REDUCED:       0  LOB PAGES UPDATED:       0
                            DTL TRC REC CNTS:   MINI BINDS :       0  HOST VARIABLES:       0  FUNCTION RESOLTNS:       0


     DMRCAT2             1 00:00.587414  00.013209 PKG:  DGR             . DMRQA             . DMRCAT2           . 196C9FA21D5BEB1D
                           00:00.587414  00.013209
                                                    ACQUIRE: U        REOPT: NOREOP      RELEASE: C       DYNAMICRULES: RUN
                                                    PREPARE: N          ISO: RS         PROTOCOL: D        KEEPDYNAMIC: N
                                                   IMMEDWRT: NO     OPTHINT:

                                                   SQL STATEMENT/EVENT        COUNT   AVG ELAPSED   AVG CPU TIME
                                                   ----------------------  --------  ------------   ------------
                                                   FETCH                         60  00:00.009258   00:00.000088
                                                   OPEN                           4  00:00.000071   00:00.000069
                                                   CLOSE                          4  00:00.000076   00:00.000059


     DMRCAT3             1 00:00.803675  00.078221 PKG:  DGR             . DMRQA             . DMRCAT3           . 196C9FA30EC5BAB9
                           00:00.803675  00.078220
                                                    ACQUIRE: U        REOPT: NOREOP      RELEASE: C       DYNAMICRULES: RUN
                                                    PREPARE: N          ISO: RS         PROTOCOL: D        KEEPDYNAMIC: N
                                                   IMMEDWRT: NO     OPTHINT:

                                                   SQL STATEMENT/EVENT        COUNT   AVG ELAPSED   AVG CPU TIME
                                                   ----------------------  --------  ------------   ------------
                                                   FETCH                        557  00:00.001228   00:00.000029
                                                   OPEN                           4  00:00.000067   00:00.000061
                                                   CLOSE                          3  00:00.000056   00:00.000050

                           --- WORKLOAD HILITES --------------------------------------------------------------------------
                            SORTS    :       0  WKFILS/SORT:     N/P  RIDS UNUSED   :       0  LOB PAGES SCANNED:       2
                            RECS/SORT:     N/P  PASSES/SORT:     N/P  DEGREE REDUCED:       0  LOB PAGES UPDATED:       0
                            DTL TRC REC CNTS:   MINI BINDS :       0  HOST VARIABLES:       0  FUNCTION RESOLTNS:       0


Related topic

SQL-activity-reports

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Ops Monitor for Db2 12.2