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 .

Analyzing workload interval summaries

Use the following procedure to analyze workload interval summaries.

(Analyzing-thread-history explains how to drill down through interval history views to detail threads, analyze detail thread records, and perform thread queries.)

To analyze workload interval summaries

  1. Choose an entry with recent data from the HTLOGS views.You can choose any earlier TLDS if you want to see past history. In this case, first sort the view by TRACE ID so that you can easily find all THRDHIST entries. (Enter SORT on the COMMAND line and place the cursor in the TRACE ID column.)

  2. Hyperlink from the Number Records column for your selected entry to begin analysis of the selected data source.The HTIQZ view provides a summary of the data in 15-minute intervals, and several navigation options in the header

    >W1 =HTIQZ=============DEFG=====*========ddmmmyyyy==16:52:47====MVDB2====D==230
    -  Thread Analysis - Query / Drilldown                           - Hide Header
       Trace ID/Title:  MVDB2DC,173       ROLLUP SUMMARY                           
       Hyperlinks         Row Hyperlinks  Total Records (at Entry):  77464          
       . THREAD QUERY       End Time      >> Thread Summary-5 Min.     
       . Trace Details      Date MM-DD    >> 15-Minute Details         
       . BBI2 LTRAC-MV      Connect Type  >> Thread Summary-Connect    
       . BBI2 TSTAT-MV      Thread Count  >> PACKAGE SUMMARY (15 Mins)
       . TLDS All Recs      Record Count  >> THREAD LIST (15 Mins)     
       . TLDS All Pkgs      15M QRY       >> THREAD QUERY (15 Mins)
                  
     End   Date  Connect   Thread  Record 15M     Total     Total     Total  TotInD
     Time  MM-DD  Type      Count   Count QRY   Elapsed       CPU      zIIP   Elaps
     14:45 03-03 CICS         767     767  *   1690.189     3.172     0.000    33.9
     14:30 03-03 CICS         835     835  *   1790.337     3.364     0.000    29.0
     14:15 03-03 CICS         831     831  *   1800.814     3.347     0.000    32.1
     14:00 03-03 CICS         832     832  *   1801.821     3.409     0.000    29.5
     13:45 03-03 CICS         823     823  *   1798.185     3.431     0.000    37.0
     13:30 03-03 CICS         821     821  *   1798.729     3.369     0.000    40.2
     13:15 03-03 CICS         817     817  *   1800.306     3.161     0.000    42.6
     13:00 03-03 CICS         808     808  *   1800.283     3.275     0.000    43.7
     12:45 03-03 CICS         817     817  *   1806.015     3.402     0.000    37.4
     12:30 03-03 CICS         817     817  *   1792.930     3.399     0.000    36.4

  3. Hyperlink on Trace Details to begin your analysis of the selected data source. The HTDTLZ view displays a detailed summary of all thread data. 

     W1 =HTDTLZ============DLY6=====*========ddmmmyyyy==06:56:38====MVDB2====D====
      Thread Interval Summary..                                                    
      Date..................... 2020-0****             >> Thread Summary by Hour   
      Start Time / End Time.... 03:30       21:30      >> Thread Summary - 15Min   
      Connect Type............. ********               >> Thread Summary - Connect
      Number of Records........        245             >> Interval/Connect Records
      Number of Threads........        333                                         
        DBAT Records...........         34                                         
        Parallelism............          2                                         
        Accelerator Threads....          0                                         
         Rollbacks.............          0                                         
        Nested Functions.......         71                                         
        LOB Access.............          6                                         
        XML Access.............          0                                         
        Fast INSERT (IAG2).....          0                                         
        Abnormal Terminations..         11                                         
        Rollbacks..............         13                                         
        Exceptions.............         20     
      Times....................      Total    Max/Thd    Avg/Thd            Average%     
      Total Elapsed............ 409976.130     171.0K   1231.159   100.0  ...50..100
        Total CPU..............     68.010      2.012      0.204     0.0            
        Total zIIP.............      0.000      0.000      0.000     0.0            
      In DB2 Elapsed...........    934.640    242.346      2.806     0.2            
        In DB2 CPU.............     38.508      1.768      0.115     0.0                                           

    You can use this detailed summary data to understand the characteristics of your Db2 workload for the time frame covered by that trace.

    The top portion shows a thread summary with elapsed and CPU time analysis. You can immediately see the number of threads, any unusual number of exceptions, the impact on the system (CPU usage), and whether the percentage of class 3 wait time, as either an average or a maximum, is out of normal guidelines.

  4. For more detail, scroll down to the bottom portion of the view. 

       In DB2 zIIP............      0.000      0.000      0.000     0.0           
       Class 3 Waits..........    498.377    159.880      1.496     0.1           
     Accelerator Elapsed......      0.000      0.000      0.000     0.0           
       Accelerator CPU........      0.000      0.000      0.000     0.0           
       Accelerator Wait.......      0.000      0.000      0.000     0.0           
     zIIP-eligible on CP......      0.000      0.000      0.000     0.0           
     Accel-eligible elapsed...      0.000      0.000      0.000     0.0           
       Accel-eligible CPU.....      0.000      0.000      0.000     0.0           
       Accel-eligible zIIP....      0.000      0.000      0.000     0.0           
     Activity.................      Total    Max/Thd    Avg/Thd                    
     Commits..................       1885        247          5                    
     Lock Suspends............       1547        188          4                    
     Getpages.................    1171114      97058       3516                    
     Sync Read I/O............      13226       4.3G         39                  
     Async Page I/O...........     326809      51990        981                  
     zHyperLink Sync Read I/O.          0          0          0                  
      Reads w/ Disk Cache Hit.       5064        992         15                  
     DDL & DCL SQL............       3604       4.3G         11                  
     DML SQL..................     484593      12177       1455                  
     Insert/Update/Delete SQL.       6055       4.3G         18                  
     Updates/Commit...........                 66.09                             
     Accelerator Commits......          0          0          0                  
      DDL SQL.................          0          0          0                  
      DML SQL.................          0          0          0                  
      Insert/Update/Delete SQL          0          0          0                  
       Updates/Commit.........                  0.00                             
      Insert/Update/Delete Row          0          0          0                  
       Rows Updated/Commit....                  0.00                             
      Rows Returned...........          0          0          0                  
     Records with Exceptions..                                                   
     Deadlocks................          1                                        
     Timeouts.................          0                                          
     Lock Escalations-Share...          0                                          
     Lock Escalations-Excl....          0                                          
     Claim failures...........          0                                          
     Drain failures...........          0                                          
     Incremental Bind Failures          3                                          
     RID Failures - Storage...          0                                          
     RID Failures - Limit.....          2                                          
     Stored Procedure Failures          0                                          
     Function Failures........          0                                          
     Trigger Failures.........          0                                          
     Accelerator Failures.....          0                                          
     Sparse Index Disabled....          0                                          
     Sparse Index Built Wkfile          0                                          
     Array Variable Expnd >32K          0                                          
     Parallel Fallbacks.......                                                     
       Ambiguous Cursor.......          0                                          
       No ESA Sort............          0                
       No Buffers.............          0            
       No Storage.............          0            
       RLF Limited............          0            
       No Enclave.............          0            
       System Stress Levels...          0            
       Autonomous Procedure...          0            
       Sysplex-No Coordinator.          0            
       Sysplex-RR/RS Isolation          0            
     Parallel Reduced.........                       
       Buffer Shortage........          0            
       System Stress Levels...          0            
       Sysplex Skip-No Buffers          0            
       Sysplex Declare TempTbl          0            
       Sysplex Reform.-Config.          0            
       Sysplex Reform.-No Buff          0                                      

    Here you can see key activity indicators (as totals, averages, and maximums) and a detailed breakdown of what kind of thread exceptions have occurred.

  5. Scroll back to the top of the view to access hyperlinks for further analysis by hour, interval, or connection type. Hyperlink from the Date field to see a summary of thread processing by hour. on the HTHOURZ view. 

     >W1 =HTDTLZ===HTHOURZ==DLY6=====*========ddmmmyyyy==06:56:38====MVDB2====D===26
     -  Thread Summary by Hour                                     - Hide Header    
        Row Hyperlinks                                                              
        Hour  >> Connect Summary - 15-Min. Intervals                                
      Hour Date   Number   Abn.  Roll-            IAG2    Total   Average   Average
      ---- MM-DD Records   Term  backs  Commits  Usage  Excepts   Elapsed       CPU
      06   04-01       4      0      0       10      0        0     0.223     0.112
      05   04-01       5      0      0       13      0        0     0.348     0.089
      18   03-31       1      0      0        0      0        0 34162.527     0.005
      13   03-31       8      0      0       20      0        0     0.170     0.100
      12   03-31       5      0      0       10      0        0 16936.673     0.117
      10   03-31       1      0      0       14      0        0  3384.554     0.337
      09   03-31      19      0      0       41      0        0     0.426     0.041
      08   03-31       1      0      0        0      0        0 31482.352     0.005 

    You can use this information to identify when workload processing problems or thread exceptions have occurred. You can sort it by any of the columns to look for particular problems like high class 3 wait times or too many exceptions. Type SORT A or SORT D on the COMMAND line and place the cursor in the column that you want to sort by.

  6. Hyperlink from the Hour column to see thread data for the selected time period by interval and connection type on the HTINTCZ view. 

     >W1 =HTDTLZ===HTINTVLZ=DLY6=====*========ddmmmyyyy==06:56:38====MVDB2====D===50
     -  Thread Summary by 15-Minute Intervals                         - Hide Header
        Row Hyperlinks                                                              
        End Time   >> Connect Summary - Interval                                    
      End   Date  Connect   Number   Abn.  Roll-            IAG2    Total   Average
      Time  MM-DD Type     Records   Term  backs  Commits  Usage  Excepts   Elapsed
      06:30 04-01 RRSAF          1      0      0        6      0        0     0.181
      06:15 04-01 DB2CALL        3      0      0        4      0        0     0.266
      05:45 04-01 RRSAF          1      0      0        3      0        0     0.134
      05:30 04-01 ********       4      0      0       10      0        0     0.455
      18:15 03-31 RRSAF          1      0      0        0      0        0 34162.527
      13:45 03-31 RRSAF          1      0      0        6      0        0     0.167
      13:30 03-31 DB2CALL        3      0      0        4      0        0     0.180
      13:15 03-31 ********       4      0      0       10      0        0     0.166
      13:00 03-31 RRSAF          1      0      0        0      0        0    118.6K
      12:45 03-31 RRSAF          1      0      0        6      0        0     0.634
      12:30 03-31 DB2CALL        3      0      0        4      0        0     0.687
      10:30 03-31 DB2CALL        1      0      0       14      0        0  3384.554 

    You can use this information to determine which connection types were experiencing problems during a selected interval.

  7. Hyperlink on the End Time field to see detailed information for the selected interval and connection type in 1-minute intervals in the HTINTVLC view.
  8. Hyperlink on the End Time field to see detailed information for the selected connection type and 1-minute interval in the HTINTVLD view.HTINTVLD looks the same as the HTDTLZ view that you saw earlier, except that HTINTVLD shows only the information of interest to help you analyze why the workload from this connection type was experiencing problems or an unusual pattern of activity.
  9. Press F3 until you return to HTDTLZ.

  10. Hyperlink on Start Time/End Time to see a summary of thread history data by interval in HTINTVLZ.This view is similar to HTHOURZ, but the data is summarized by 15-minute intervals.

    You can drill down on End Time to see the data for an interval broken down by connect type, if needed.

  11. Press F3 until you return to HTDTLZ.
  12. Hyperlink on Connect Type to see a summary of thread processing by connection type in HTCTYPZ.From here, you can drill down on one Connect Type to see the breakdown by interval.
  13. Press F3 until you return to HTLOGS.

Related topic

Accessing-thread-history-data

 

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

BMC AMI Ops Monitor for Db2 12.2