Recommended changes - Documentation for BMC AMI Pool Advisor for Db2 12.1

When analysis is complete, a list of the changes recommended by the configuration advisor is displayed.

PMDEBPA1/I Configuration Analysis Results Summary LINE 1 OF 999
Command ====> _______________________________________________ Scroll ===> CSR_
DB2: DHB1 Rel: 8.1 Data sharing: Yes, Merged=Yes Configuration: 1 Compatibility: 90
Actions: <accept <discard>
SET BP0 SZ= 8070 PFIX=N PSA= LRU
SET BP0 SST= 50 DWT= 15 VDWT= 3, 0
SET BP0 PMDPARMS: DHB1@PMD_SIZE_BP0_MIN 7505
SET BP0 PMDPARMS: DHB1@PMD_SIZE_BP0_MAX 8796
SET BP0 PMDPARMS: DHB1@PMD_SIZE_BP0_INC 807
SET BP0 PMDPARMS: DHB1@PMD_SIZE_BP0_DEC 807
SET BP1 SZ= 49590 PFIX=N PSA= LRU
SET BP1 SST= 50 DWT= 0 VDWT= 0, 256
SET BP1 PMDPARMS: DHB1@PMD_SIZE_BP1_MIN 46118
SET BP1 PMDPARMS: DHB1@PMD_SIZE_BP1_MAX 54053
SET BP1 PMDPARMS: DHB1@PMD_SIZE_BP1_INC 4959
SET BP1 PMDPARMS: DHB1@PMD_SIZE_BP1_DEC 4959
SET BP2 SZ= 63000 PFIX=N PSA= LRU
SET BP2 SST= 50 DWT= 0 VDWT= 0, 256
SET BP2 PMDPARMS: DHB1@PMD_SIZE_BP2_MIN 58590
SET BP2 PMDPARMS: DHB1@PMD_SIZE_BP2_MAX 68670
SET BP2 PMDPARMS: DHB1@PMD_SIZE_BP2_INC 6300
SET BP7 PMDPARMS: DHB1@PMD_SIZE_BP7_DEC 54
SET BP25 SZ= 12310 PFIX=N PSA= LRU
SET BP25 SST=100 DWT= 10 VDWT= 2, 0
SET BP25 PMDPARMS: DHB1@PMD_SIZE_BP25_MIN 11448
SET BP25 PMDPARMS: DHB1@PMD_SIZE_BP25_MAX 13417
SET BP25 PMDPARMS: DHB1@PMD_SIZE_BP25_INC 1231
SET BP25 PMDPARMS: DHB1@PMD_SIZE_BP25_DEC 1231
SET BP31 SZ= 18680 PFIX=N PSA= LRU
SET INDEX PMDDBVAR.IXVARR1R BUFFERPOOL BP49
SET TABLESPACE PMDDBVAR.TSVAR2 BUFFERPOOL BP2
SET INDEX PMDDBVAR.IXVARR2R BUFFERPOOL BP1
SET TABLESPACE PMDDBVAR.TSVAR3 BUFFERPOOL BP2
SET INDEX PMDDBVAR.IXVARR3R BUFFERPOOL BP49
SET TABLESPACE PMDDBVAR.TSVAR8 BUFFERPOOL BP2
SET INDEX PMDDBVAR.IXVARR8R BUFFERPOOL BP49
SET TABLESPACE PMDDBVAR.TSVAR20 BUFFERPOOL BP35
SET INDEX PMDDBVAR.IXVARR20 BUFFERPOOL BP49
SET TABLESPACE DDTQDB18.DDTQTS18 BUFFERPOOL BP2
SET TABLESPACE DDTQDB19.DDTQTS19 BUFFERPOOL BP35
SET TABLESPACE DDTQDB20.DDTQTS20 BUFFERPOOL BP35
SET TABLESPACE DDTQDB21.DDTQTS21 BUFFERPOOL BP2
SET TABLESPACE DDTQDB22.DDTQTS22 BUFFERPOOL BP2
SET TABLESPACE DDTQDB23.DDTQTS23 BUFFERPOOL BP35

When only a few changes are recommended, the recommendations are fairly easy to understand. However, if the change list is extensive, you might find it difficult to see the big picture represented by the new configuration.

If you see undesirable changes in the results, you can discard the results and try again with different options. But if you are not sure, you can accept the results and continue to the Buffer Pool Configuration Analysis panel (see the following figure) to see how the changes are represented in the configuration. This panel makes it easier to see and explore the new configuration.

PMDEBPCC/I Buffer Pool Configuration Summary LINE 1 OF 17
Command ====> _______________________________________________ Scroll ===> CSR_
DB2: DHB1 Rel: 8.1 Data sharing: Yes, Merged=Yes Configuration: 1 Compatibility: 90
Actions: < REANALYZE > < UNDO > < GENERATE JCL >
Type one or more of the following action codes. Then press Enter. S - Details/set attributes L - Lock pool U - Unlock pool P - List page sets M - Lock page sets V - Unlock page sets -Attributes- Size Pag Page -Thresholds- PoolID T P S A U Z D PgSets Cmp (pages) fix stl Seq DWT VDW Chg ------ ------------- ------ --- --------- --- ---- --- --- --- ---
_ BP0 S H M H H L Y 168 69 8070 N LRU 50 15 3 Y
_ BP1 I H M H H L N 828 94 49590 N LRU 50 0 0 Y
_ BP2 T M M H H L N 1591 87 63000 N LRU 50 0 0 Y
_ BP7 W H M M H L N 14 85 540 N LRU 50 75 25 Y
_ BP25 I H H H H L Y 72 96 12310 N LRU 100 10 2 Y
_ BP31 I H L H H L N 488 92 18680 N LRU 10 5 1 Y
_ BP32 I M M H M L N 297 94 17730 N LRU 50 0 0 Y
_ BP33 I L M H M M N 747 90 29470 N LRU 50 0 0 Y
_ BP34 I H M M M L N 525 93 16530 N LRU 50 0 0 Y
_ BP35 T H M H H L N 1330 91 65890 N LRU 50 0 0 Y
_ BP49 I M M M H L N 1108 91 37490 N LRU 50 0 0 Y

The Buffer Pool Configuration Analysis panel is identical to the Buffer Pool Configuration Summary panel (see above figure) with the following exceptions:

• The Configuration field is incremented each time analysis is performed.
• The button options have changed.

If you decide to discard the recommended changes and start over, click the UNDO button.

If you want to adjust the new configuration by changing the control options, click REANALYZE and modify values until you reach a satisfactory arrangement. The difference between various starting points in the analysis process is that there is a built-in bias towards the current setting. Changes are not recommended unless they appear to produce a better compatibility rating, even though there might be many arrangements that produce similar compatibility ratings.

You can exert more influence over the configuration process by setting and locking values for the buffer pools and the page sets.

You can manually set values for buffer pools on Buffer Pool Configuration Summary panel (see the following figure) and the Buffer Pool Attributes panel.

From the Buffer Pool Configuration Summary panel, you can choose to lock one or more buffer pools. When you lock a buffer pool, the configuration advisor does not change its size or parameters, but page sets might be moved to or from the buffer pool. In the example below, by placing an L next to BP0, you indicate that the configuration advisor should retain these settings for this buffer pool, and after pressing Enter, the value in the Chg field changes to N.

PMDEBPCA/I Buffer Pool Configuration Summary LINE 1 OF 19
Command ====> _______________________________________________ Scroll ===> CSR_
DB2: DHB1 Rel: 8.1 Data sharing: Yes, Merged=Yes Configuration: 0 Compatibility: 76
Actions: < ANALYZE >
Type one or more of the following action codes. Then press Enter. S - Details/set attributes L - Lock pool U - Unlock pool P - List page sets M - Lock page sets V - Unlock page sets -Attributes- Size Pag Page -Thresholds- PoolID T P S A U Z D PgSets Cmp (pages) fix stl Seq DWT VDW Chg ------ ------------- ------ --- --------- --- ---- --- --- --- ---
L BP0 S H M H H L Y 336 54 2000 N LRU 50 60 12 Y
_ BP1 I H M H H L N 51 72 480 N LRU 50 75 25 Y
_ BP2 T M M H H L N 5 77 30 N LRU 10 50 23 Y
_ BP7 W H M M H L N 11 76 50 N LRU 50 75 24 Y
_ BP25 I H H H H L Y 62 86 370 N LRU 0 75 24 Y
_ BP31 I H M H H L N 1494 74 5770 N LRU 50 20 4 Y
_ BP32 I H M H H L N 1495 81 5840 N LRU 50 20 4 Y
_ BP33 I H M H H L N 1546 80 6000 N LRU 50 20 4 Y
_ BP34 I H M H H L N 1529 72 5960 N LRU 50 20 4 Y
_ BP35 T H M H H L N 585 83 2260 N LRU 50 55 11 Y
_ BP49 I H H H H L Y 54 79 1060 N LRU 0 75 24 Y

Select a buffer pool with the S action code to display the Buffer Pool Attributes panel (see the following figure).

PMDEBPC2/I Buffer Pool Attributes 17:11:24
Command ====> ________________________________________________________________
DB2: DHB1 Rel: 8.1 Data sharing: Yes, Merged=Yes Configuration: 1 Buffer pool: BP0 Description ________________________________________
Can these attributes be changed during analysis? Y (Y,N)
Can additional page sets be moved into this pool? Y (Y,N)
Performance Attributes................... Pool size in pages Object type SYS (TBL,IDX,SYS,WRK,LOB) Tgt _____8070 ( 32 MB) Priority HI_ (LOW,MED,HI) Min _____7505 ( 30 MB) Access type MED (LOW,MED,HI) Max _____8796 ( 35 MB) Actvity lvl HI_ (LOW,MED,HI) Update rate HI_ (LOW,MED,HI) Object size LRG (SML,MED,LRG) Data sharing YES (YES,NO)
Sequential steal threshold . . . . _50 % (0-100%)
Deferred write threshold . . . . . _15 % (0-90%)
Vertical deferred write threshold __3 % , ___0 (0-90% or 0-9999 pages)
Page steal attribute . . . . . . _LRU (LRU,FIFO)
Page fixed storage . . . . . . . N (Y=fixed, N=pageable)

From the Buffer Pool Attributes panel, you can manually specify the performance attributes that you want the pool to support, as well as the various sizes and thresholds. By so doing, you define a different starting point for the analysis, which the configuration advisor does not change unless it calculates a better arrangement.

You can also lock your settings into place by specifying N in the Can these attributes be changed during analysis? field. You can prevent page sets from being moved into the buffer pool by specifying N in the Can additional page sets be moved into this pool? field. The configuration advisor searches for the best configuration within the defined constraints.

Important

( SPE2101 ) With the Page fixed storage enhancement, you can now modify your settings in real time by specifying N for pageable and Y for fixed.

( SPE2101 ) The generated Configuration JCL comprised of various ALTER BUFFERPOOL commands make changes to the buffer pools. The analysis engine should consider the possibility of recommending fixed pages utilizing, the PGFIX(YES) keyword. For the pools that meet the necessary criteria, the use of 1M or 2G real frames is considered for optimal performance improvements. The decision to use the 1M or 2G real frames depends upon the availability of the large frames on the LPAR with the IEASYSxx LFAREA keyword parameter. When the configuration advisor determines that either the large frames are not available or there is not an ample supply, the decision to use 4K real frame will be taken.

( SPE2101 ) The Configuration Advisor depends on a configuration set, that is produced during Pageset Profiling activity, for the data points required for analysis. The configuration set consists of a subset of data elements that are collected and the analysis of this set is reduced to a set of buffer pool attributes. These attributes are used to determine candidates for page fixing. The criteria used to determine when a page fixing is appropriate is centered around levels of activity, sequential access, and updates. The following table defines the mandatory criteria:

You can manually set values for page sets (see the Buffer Pool Configuration Summary panel and the Page Set Attributes panel (see the following figure)).

From the Buffer Pool Configuration Summary panel, you can lock all of the page sets in a buffer pool. The configuration advisor will not recommend moving any of the page sets in the buffer pool but is free to recommend changes to the size or parameters of the pool.

PMDEBPP2/I Page Set Attributes 17:20:12
Command ====> ________________________________________________________________
DB2: DHB1 Rel: 8.1 Data sharing: Yes, Merged=Yes Configuration: 1 Buffer pool: BP0 Description: Page set: DSNDB01 . DSNLLX01 Type: INDEX Page set Pool
Attributes (measured) (configured)
------------------------------ ---------- ------------
Object type (TBL,IDX,SYS,WRK,LOB) SYS SYS
Priority . . . . . . (LOW,MED,HI) MED HI
Sequential access . (LOW,MED,HI) HI MED
Actvity level . . . (LOW,MED,HI) LOW HI
Update rate . . . . (LOW,MED,HI) HI HI
Object size . . . . (SML,MED,LRG) LRG LRG
Data Sharing . . . . (Yes,No ) NO YES
Set the priority for page set ___ (LOW,MED,HI)
Move page set to buffer pool ______ (Pool name, ex: BP8, BP32K2)
Can the pool for this page set be changed during analysis? Y (Y,N)

From the Page Set Attributes panel, you can set the priority for page sets. Priority is always user defined. The configuration advisor never makes recommendations for priority. You can also specify a different buffer pool assignment. If the setting is not locked, the configuration advisor might not recommend the same buffer pool that you specify, but only if it finds a better fit than the one you chose. If the setting is locked, the configuration advisor does not attempt to reassign the page set or delete the indicated buffer pool.

Important

If a buffer pool is defined with zero pages, the buffer pool and all its page sets are treated as "dead" objects, and the configuration advisor ignores them even if they are not locked. If you want page sets to be eligible for configuration changes, they must reside in a buffer pool defined with a size greater than zero.