Skip to Content

Default language.

Analyzing page set usage and I/O

Use the following procedures to analyze and tune page set usage, and I/O.

Related topic

Analyzing-a-specific-Db2

The tuning of physical I/O and page set usage in the buffer pools is a critical success factor in Db2.

Db2 itself uses many techniques to reduce or defer I/O, but the setup, from DASD volumes to buffer pool allocation and thresholds, all plays a part.

To look at data object issues with the focus still on a single Db2

  1. From EZDB2, select the Page Set Menu option.The Db2 Object Menu (EZDPS) is displayed.

    Warning

    Important

     collects these page set statistics with low overhead from Db2 control blocks.

    The I/O counts for low activity page sets are retained as interval counts until they reach the threshold of one I/O per second for a STATIME interval.

                                   Db2 Object Menu                                
       Page Set Information                                I/O by Page Set (SSI)  
     . Status                  +----------------------+  . Total I/Os - Session   
     . Cache Statistics        |   Place Cursor On    |  . Total I/Os - Interval  
     . List Page Sets (/Db2)   |    Menu Item And     |  . Sync I/Os - Session    
     . List Page Sets (SSI)    |     Press ENTER      |  . Sync I/Os - Interval   
     . GBP-Dependent Status    +----------------------+  . Async I/Os - Session   
                               . Set Db2 Context         . Async I/Os - Interval  
                                                                                 
       Summaries                 Catalog Manager-Local     Status Summaries       
     . By Volume (SSI)         * Database List           . Restricted Objects     
     . By Volume (/Db2)        * TableSpace List         . Utility Jobs           
     . By Buffer Pool          * IndexSpace List         . Stored Procedures      
     . By Data Base                                                               
     . By Object                                                                  
                                                         . Return...

  2. To review the status of all open page sets, hyperlink on Status.A list sorted by object name (database, table space, partition) is displayed.

     W1 =PSSTAT==========DB2H=====*========ddmmmyyyy==16:11:08====MVDB2========77
     ------Page Set------- Bfrpl                     %       First   Defr      GBP
     Database Object   Prt  ID    Ty Users Size(K)  Used EXT Volume  WrtQ  Err Dep
     DB2HWORK DSN4K01  001 BP00   TS     0    7440  99.4   3 BAB310     0   N
     DSNDB01  DBD01    001 BP00   TS     0    1440 100.0   1 BAB310     1   N
     DSNDB01  DSNLLX01 001 BP00   IX     0     288  66.7   1 BAB310     0   N
     DSNDB01  DSNLLX02 001 BP00   IX     0     240  80.0   1 BAB312     0  Res
     DSNDB01  DSNLUX01 001 BP00   IX     0      48  33.3   1 BAB320     0   N
     DSNDB01  DSNLUX02 001 BP00   IX     0      48  33.3   1 BAB320     0  Adv
     DSNDB01  DSNSCT02 001 BP00   IX     0     144  11.1   1 BAB310     0   N
     DSNDB01  DSNSPT01 001 BP00   IX     0     240  40.0   1 BAB312     0   N
     DSNDB01  DSNSPT02 001 BP00   IX     0     432  44.4   1 BAB325     0   N
     DSNDB01  SCT02    001 BP00   TS     0   10080  14.3   1 BAB310     1   N
     DSNDB01  SPT01    001 BP00   TS     0    5760  25.0   1 BAB312     1   N
     DSNDB01  SYSLGRNX 001 BP00   TS     0    1440 100.0   1 BAB310     1   N
     DSNDB01  SYSUTILX 001 BP00   TS     0    1440 100.0   1 BAB320     1   N
     DSNDB06  DSNADH01 001 BP00   IX     0      48  33.3   1 BAB312     0   N
     DSNDB06  DSNAGH01 001 BP00   IX     0      96  16.7   1 BAB314     0   N
     DSNDB06  DSNAPH01 001 BP00   IX     0      96  16.7   1 BAB310     0   N
     DSNDB06  DSNATX01 001 BP00   IX     0     144  11.1   1 BAB312     0   N
     DSNDB06  DSNATX02 001 BP00   IX     0     480  20.0   1 BAB312     0   N
     DSNDB06  DSNATX03 001 BP00   IX     0     432  22.2   1 BAB312     0   N
     DSNDB06  DSNAUH01 001 BP00   IX     0      96  16.7   1 BAB310     0   N
     DSNDB06  DSNDCX01 001 BP00   IX     0    2160  66.7   1 BAB312     0   N
     DSNDB06  DSNDDH01 001 BP00   IX     0      48  33.3   1 BAB310     0   N
     DSNDB06  DSNDDX02 001 BP00   IX     0      48  33.3   1 BAB310     0   N
     DSNDB06  DSNDKX01 001 BP00   IX     0     192  50.0   1 BAB318     0   N
     DSNDB06  DSNDLX01 001 BP00   IX     0      48  33.3   1 BAB312     0   N
     DSNDB06  DSNDPX01 001 BP00   IX     0      96 100.0   1 BAB310     0   N
  3. Type SORT D and move to the EXT column by pressing the Tab key.Sort on the EXT column to identify the data sets with the highest number of extents (a potential performance impact).
  4. If you want to limit the view by selecting only certain page sets, use the WHERE command (which works like the SQL WHERE clause):
    1. For a column on which you want to filter the view, place the cursor in the column header and press F1.
    2. In the displayed Help, find the element name for that column.
    3. Enter WHERE on the COMMAND line.

    4. In the Where Condition field, enter the column's element name and the condition that you want to apply.

      Information
      Example

      To show only data sets that have more than five extents, use a column with the element name IO_EXT, enter IO_EXT > 5.

      To see only data sets in use by Database DSNDB06, use a column with the element name IO_DBTSP, enter IO_DBTSP = DSNDB06*.

      DSNDB06* refers to all data sets on the PSSTAT view that are in a page set that starts with DSNDB06 (which may be part or all of the Data Base name).

    5. Press F3 to return to PSSTAT.

    6. Repeat steps Step 4.a through Step 4.e for each column on which you want to filter the view.

      Success

      Tip

      For any view, you can determine which filters are in effect by using the SHOWFILT command.

  5. Hyperlink on the Page Set field to see complete details about the selected page set, including size, volume, buffer pool cache data, and detailed I/O counts and elapsed wait times.You might need to scroll down with F8 to see all of the data.
  6. Press F3 to return to PSSTAT.
  7. Choose one of the following actions:
    • To view the threads currently accessing this page set, hyperlink on the Users column.

    • To view possible I/O errors and page sets that are currently in restricted or advisory status, hyperlink on the Err column.

      The hyperlink displays the OBJDETL view to show more details about that object and its status.

  8. Press F3 to return to EZDB2.One of the most important issues to check periodically is data set placement and volume I/O response times.

To view data set placement and volume I/O response times

  1. Hyperlink on Volume I/O Summary.A list of the volumes in use for Db2 databases is displayed.

    >W1 =PSVOLSZ===========DB2H=====*========ddmmmyyyy==11:21:48====MVDB2========11
             Db2       Nr.     Sync   I/O Sync Max  Sync Avg                Async
     Volume Target     PSs     I/Os    %  I/O Wait  I/O Wait 0...20...40     I/Os
     BAB309 DB2H         1        9   2.0       35        19 *****              1
     BAB310 DB2H        16      141  31.0      325        24 ********          15
     BAB312 DB2H        21      144  31.6      175        24 ********          43
     BAB314 DB2H         5       25   5.5      667        41 **********+       12
     BAB316 DB2H         4       14   3.1       38        14 ****               0
     BAB318 DB2H         9       50  11.0       73        22 ******            21
     BAB319 DB2H         1        2   0.4       31        17 *****              0
     BAB320 DB2H         5       24   5.3       51        19 *****              6
     BAB321 DB2H         1        3   0.7       32        22 ******             0
     BAB325 DB2H        13       40   8.8       47        20 ******             7
     BAB330 DB2H         1        3   0.7       40        20 *****              1
  2. To sort the volumes with the highest average delays to the top, type SORT D on the COMMAND line and use the Tab key to move to Sync Avg I/O Wait.These values are based on activity since Db2 startup.
  3. To see a 2-hour summary of activity for one volume broken down into 15-minute intervals, hyperlink on Sync I/Os.
  4. Press F3 to return to PSVOLSZ.
  5. To see a list of all page sets on that volume, hyperlink on a volume.You can scroll to the right to see the asynchronous I/Os made for prefetch because these I/Os have different access characteristics and delay times than do synchronous I/Os.
  6. Return to EZDPS:
    1. Press F3 until you return to EZDB2.
    2. Hyperlink on the Page Set Menu option.

  7. To view I/O counts and wait times per page set, hyperlink on any of the I/O by Page Set options.

    • To identify the highest average delays that can point out DASD response time problems, sort on the Avg I/O Wait column.
    • To identify occasional contention problems that are masked in the averages, sort on the Max I/O Wait column.
    Warning

    Important

    The maximum is calculated since Db2 startup, not per interval. This limitation does reduce its usefulness.

  8. Press F3 until you return to EZDPS.

To analyze page usage in the buffer pools by page set

  1. On EZDPS, hyperlink on Cache Statistics.The Page Set cache (PSCACHE) is displayed.

    >W1 =PSCACHE===========DB2H=====*========ddmmmyyyy==16:18:48====MVDB2========77
     ------Page Set------- Bfrpl     VP       VP        VP      VP     VPTot VPTot
     Database Object   Prt  ID     Current  Maximum  Changed Max Chng  % All   %BP
     DB2HWORK DSN4K01  001 BP00          0        2        0        2    0.0   0.0
     DSNDB01  DBD01    001 BP00          7       23        0        6    4.0   3.5
     DSNDB01  DSNLLX01 001 BP00          9       10        0        6    5.2   4.5
     DSNDB01  DSNLLX02 001 BP00          3        8        0        6    1.7   1.5
     DSNDB01  DSNLUX01 001 BP00          3        4        0        2    1.7   1.5
     DSNDB01  DSNLUX02 001 BP00          2        4        0        2    1.2   1.0
     DSNDB01  DSNSCT02 001 BP00          0        3        0        1    0.0   0.0
     DSNDB01  DSNSPT01 001 BP00          0        3        0        1    0.0   0.0
     DSNDB01  DSNSPT02 001 BP00          0        4        0        1    0.0   0.0
     DSNDB01  SCT02    001 BP00          1        5        0        3    0.6   0.5
     DSNDB01  SPT01    001 BP00          1        5        0        3    0.6   0.5
     DSNDB01  SYSLGRNX 001 BP00          8       13        0        7    4.6   4.0
     DSNDB01  SYSUTILX 001 BP00          3       12        0        5    1.7   1.5
     DSNDB06  DSNADH01 001 BP00          2        3        0        0    1.2   1.0
     DSNDB06  DSNAGH01 001 BP00          2        3        0        0    1.2   1.0
     DSNDB06  DSNAPH01 001 BP00          0        3        0        0    0.0   0.0
     DSNDB06  DSNATX01 001 BP00          2        3        0        2    1.2   1.0
     DSNDB06  DSNATX02 001 BP00          6        6        0        2    3.5   3.0
     DSNDB06  DSNATX03 001 BP00          3        4        0        2    1.7   1.5
     DSNDB06  DSNAUH01 001 BP00          2        3        0        0    1.2   1.0
     DSNDB06  DSNDCX01 001 BP00          7        7        0        4    4.0   3.5
     DSNDB06  DSNDDH01 001 BP00          2        3        0        0    1.2   1.0
     DSNDB06  DSNDDX02 001 BP00          0        3        0        0    0.0   0.0
     DSNDB06  DSNDKX01 001 BP00          2        3        0        1    1.2   1.0
     DSNDB06  DSNDLX01 001 BP00          2        3        0        1    1.2   1.0
     DSNDB06  DSNDPX01 001 BP00          2        3        0        1    1.2   1.0

    You can sort on the VP Current column (descending) to show page sets with the highest current storage usageat the top. The VP Changed and VP Max Chng columns identify page sets with update activity.

  2. Press F3 until you return to EZDPS.

  3. Hyperlink on Summary by Buffer Pool for assistance in balancing table space allocations to the proper buffer pools.

    >W1 =PSBPSZ============DB2H=====*========ddmmmyyyy==16:21:01====MVDB2====D====1
     Bfrpl   Db2        Nr.    VP        VP      Total    VP Max  VPMaxChg Tot I/O  
       ID    Target     PSs  Current  Changed     I/Os  (1 PSet)  (1 PSet)    %     
      BP0    DEDM        63     1988       20   105585      1772       314    72.2  
      BP0    DEEN        13      113        0      114        64         0     0.1  
      BP0    DEFG       273     7840        1    31478      3943       691    21.5

    The view PSBPSZ gives you a quick overview of how buffer pools are being used.

  4. Hyperlink on Bfrpl ID to see a list of all page sets allocated to that pool (PSBPS).Sort on the VP Current column (descending) to show page sets with the highest current storage usage at the top.

    The VP Changed and VP Max Chng columns identify those page sets with update activity.

  5. Press F3 until you return to PSBPSZ.
  6. Hyperlink on VP Current for a 2-hour history.
  7. Press F3 until you return to EZDPS.

To see if any objects are in restricted or advisory status, or are being accessed by a utility

  1. Hyperlink on Object Status Summary.

    W1 =OBJZ=============(DB2H=====*=======)ddmmmyyyy==19:02:44====MVDB2====D====1
    Db2      Db2      Access       Utility    Util   All          All             
    Group    Target   Restricted   Processing Thds   Restricted   Advisory        
    N/A      DB2H              0            0   Y            22         10

    If Util Thds is set to Y, you can hyperlink to the THDUTIL view to display the utility threads that are associated with the restricted objects.

  2. Press F3 until you return to EZDB2.

 

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

BMC AMI Ops Monitor for Db2 13.1