Skip to Content

Batch SQL Statement by Object report example


The following figure is limited to statements that accessed objects with table creator BENCH*. You can also run this report with EXPAND parameters to include detailed statistics for each statement.

REPORT(NAME(SQMBSBYO)  -
QUALIFIER(TBCREATOR(BENCH*))

Related topic

Batch-reports-description-and-examples

Batch SQL Statement by Object report (SQMBSBYO)

1                       SQMBSBYO: BATCH SQL STATEMENT BY OBJECT                          06/20/23 03:38:35    PAGE     1
                                  INTERVAL: 05/31/23 00:00:00  TO  05/31/23 23:59:59


  Qualified by:     Subsys: DLYE       AppGrp:                        User:                 ConnID:                 Plan:
                    CorrID:

  ---------------------------------------------------------------------------------------------------------------------------------

   Program........ SELECT02    Location...                     Collection ID... AFDALL                Contoken... 19D110C00C14F489
   Program Type... PKGE        Version....



     Statement Type...    STATIC
     Section Number...           0  SQL      +-------- Total IN-SQL Time --------+          +- Sync I/O --+ +- Async I/O -+ Lock
     Statement Number.           0  Calls    Elapsed       %   CPU            %    Getpage  Number Elapsed  Waits  Elapsed  Wait %
                                    -------- ------------------ ------------------ -------- ------ -------- ------ -------- ------
                                      200000 00:02.80838 13.4 % 00:01.62537 34.9 %   400009      4  00.0006      0  00.0000  0.0 %
                         Averages:           00:00.00001        00:00.00001               2      0  00.0000      0  00.0000
                    IN-Db2 totals   Elapsed: 00:17.65742    CP: 00:11.55830    zIIP:  00:00.00000
                    IN-Db2 averages          00:00.00009        00:00.00006           00:00.00000

     Unqualified Object Owner.... AFDQA             Connection Type.... N/P              Requesting Location.... N/P
     SQL Text:                                                 Latest Text Capture:  05/31/23  02:48:59 Key: D5545D8EEA09DCD33B4D
       SELECT   *
       INTO     :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H
       FROM     BENCH.QATBL10
       WHERE    SOURCE = :H

                          ------------------------------- DML Statistics -----------------------------
                          Statement Type    Total    Statement Type    Total   Statement Type    Total
                          -------------- --------    -------------- --------   -------------- --------
                          Select........   200000    Open..........        0   Insert........        0
                          Describe......        0    Close.........        0   Update........        0
                          Prepare.......        0    Fetch.........        0   Delete........        0

                                                      Savepoint Operations
                          ----------------------------------------------------------------------------
                          Request.......        0    Release.......        0   Rollback......        0

                                                          Object Statistics

   Creator   Name                            BPool  +------------- Getpage -------------+ +------- Sync I/O ------+ +- Async I/O -+
          Long Name              Type BPool  HRatio Total  Avg    Tot Time   %   Avg Time Number  Tot Time Avg Time Request Pages
   ----------------------------- ---- ------ ------ ------ ------ -------------- -------- ------- -------- -------- ------- -------
   BENCH     QATBL10              (total)     100 % 400000      2  00.2144  7.6%  00.0000       1  00.0001  00.0001       0       0
          QATBL10
    BENCH     QATBL10            T    BP0     100 % 400000      2  00.2144  7.6%  00.0000       1  00.0001  00.0001       0       0
          QATBL10

   --------------------------------------------------------------------------------------------------------------------------------

1                       SQMBSBYO: BATCH SQL STATEMENT BY OBJECT                          06/20/23 03:38:35    PAGE     2
                                  INTERVAL: 05/31/23 00:00:00  TO  05/31/23 23:59:59


  Qualified by:     Subsys: DLYE       AppGrp:                        User:                 ConnID:                 Plan:
                    CorrID:

  ---------------------------------------------------------------------------------------------------------------------------------

   Program........ DSNTEP91    Location...                     Collection ID... DSNTEP91              Contoken... 1952880D04E23AD4
   Program Type... PKGE        Version....



     Statement Type...    DYNAMIC
     Section Number...           1  SQL      +-------- Total IN-SQL Time --------+          +- Sync I/O --+ +- Async I/O -+ Lock
     Statement Number.        2078  Calls    Elapsed       %   CPU            %    Getpage  Number Elapsed  Waits  Elapsed  Wait %
                                    -------- ------------------ ------------------ -------- ------ -------- ------ -------- ------
                                           4 00:02.78866 13.3 % 00:00.24438  5.2 %    20006  20003  02.5325      0  00.0000  0.0 %
                         Averages:           00:00.69716        00:00.06109            5002   5001  00.6331      0  00.0000
                    IN-Db2 totals   Elapsed: 00:02.63854    CP: 00:00.24451    zIIP:  00:00.00000
                    IN-Db2 averages          00:00.65964        00:00.06113           00:00.00000

     Unqualified Object Owner....      N/P          Connection Type.... RRSAF ATTACH     Requesting Location.... DLY
     SQL Text:                                                 Latest Text Capture:  05/31/23  02:49:24 Key: BBABF9093EEA439CEAAA


                          ------------------------------- DML Statistics -----------------------------
                          Statement Type    Total    Statement Type    Total   Statement Type    Total
                          -------------- --------    -------------- --------   -------------- --------
                          Select........        0    Open..........        0   Insert........        0
                          Describe......        0    Close.........        0   Update........        0
                          Prepare.......        0    Fetch.........        4   Delete........        0

                                                      Savepoint Operations
                          ----------------------------------------------------------------------------
                          Request.......        0    Release.......        0   Rollback......        0

                                                          Object Statistics

   Creator   Name                            BPool  +------------- Getpage -------------+ +------- Sync I/O ------+ +- Async I/O -+
          Long Name              Type BPool  HRatio Total  Avg    Tot Time   %   Avg Time Number  Tot Time Avg Time Request Pages
   ----------------------------- ---- ------ ------ ------ ------ -------------- -------- ------- -------- -------- ------- -------
   BENCHQA   TB10K                (total)     0.0 %  20006   5002  02.6010 93.3%  00.6503   20003  02.6010  00.0001    2504       0
    BENCHQA   TB10K              T    BP10    0.0 %  20006   5002  02.6010 93.3%  00.6503   20003  02.6010  00.0001    2504       0

   --------------------------------------------------------------------------------------------------------------------------------

1                       SQMBSBYO: BATCH SQL STATEMENT BY OBJECT                          06/20/23 03:38:35    PAGE     3
                                  INTERVAL: 05/31/23 00:00:00  TO  05/31/23 23:59:59


  Qualified by:     Subsys: DLYE       AppGrp:                        User:                 ConnID:                 Plan:
                    CorrID:

  ---------------------------------------------------------------------------------------------------------------------------------

   Program........ SELECT02    Location...                     Collection ID... SELECT02              Contoken... 19D110C00C14F489
   Program Type... PKGE        Version....



     Statement Type...    STATIC
     Section Number...           1  SQL      +-------- Total IN-SQL Time --------+          +- Sync I/O --+ +- Async I/O -+ Lock
     Statement Number.         184  Calls    Elapsed       %   CPU            %    Getpage  Number Elapsed  Waits  Elapsed  Wait %
                                    -------- ------------------ ------------------ -------- ------ -------- ------ -------- ------
                                           1 00:00.03197  0.2 % 00:00.00225  0.0 %       24     13  00.0016      1  00.0001  0.0 %
                         Averages:           00:00.03197        00:00.00225              24     13  00.0016      1  00.0001
                    IN-Db2 totals   Elapsed: 00:00.03213    CP: 00:00.00237    zIIP:  00:00.00000
                    IN-Db2 averages          00:00.03213        00:00.00237           00:00.00000

     Unqualified Object Owner.... AFDQA             Connection Type.... N/P              Requesting Location.... DLY
     SQL Text:                                                 Latest Text Capture:  05/31/23  02:48:35 Key: D5545D8EEA09DCD33B4D
       SELECT   *
       INTO     :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H, :H
       FROM     BENCH.QATBL10
       WHERE    SOURCE = :H

                          ------------------------------- DML Statistics -----------------------------
                          Statement Type    Total    Statement Type    Total   Statement Type    Total
                          -------------- --------    -------------- --------   -------------- --------
                          Select........        1    Open..........        0   Insert........        0
                          Describe......        0    Close.........        0   Update........        0
                          Prepare.......        0    Fetch.........        0   Delete........        0

                                                      Savepoint Operations
                          ----------------------------------------------------------------------------
                          Request.......        0    Release.......        0   Rollback......        0

                                                          Object Statistics

   Creator   Name                            BPool  +------------- Getpage -------------+ +------- Sync I/O ------+ +- Async I/O -+
          Long Name              Type BPool  HRatio Total  Avg    Tot Time   %   Avg Time Number  Tot Time Avg Time Request Pages
   ----------------------------- ---- ------ ------ ------ ------ -------------- -------- ------- -------- -------- ------- -------
   BENCH     QATBL10              (total)    25.0 %      4      4  00.0004  1.2%  00.0004       3  00.0004  00.0001       0       0
          QATBL10
    BENCH     QATBL10            T    BP0    25.0 %      4      4  00.0004  1.2%  00.0004       3  00.0004  00.0001       0       0
          QATBL10

   --------------------------------------------------------------------------------------------------------------------------------

 

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

BMC AMI Apptune for Db2 13.1