By Sueli Almeida, John Campbell, and Paul McWilliams.
If you have recently looked at the topic where we summarize the
Recent enhancements to Db2 12, perhaps you noticed several entries for Db2 subsystem parameters (ZPARMs) that have been changed or removed in Db2 12.
The goal of these changes is to simplify installation and administration of Db2 by reducing the number of "switches and knobs" that you must manage to install, migrate, and administer Db2, and to better align ZPARM default settings with current best practices. Many of these changes were informed by the results of a
2018 survey of clients' ZPARM settings.
We are delivering these changes in four parts.
Part 1: Default settings and ranges adjusted for current best practices (APAR PH21370)
We delivered the first of four planned APARs for this effort in March 2020 with
APAR PH21370, which updates default settings and adjusts the ranges of the following ZPARMs. If you use a different value than these in your current Db2 environment, you’ll need to make appropriate adjustments to your settings before you apply this APAR.
CHKFREQ=3
The default value for the
CHKFREQ ZPARM is changed to 3 minutes. The range of accepted values is also reduced to 1 - 5 minutes. The previous default value was 5 minutes, and values as great as 60 minutes were accepted. The goal of this change is to enforce a best practice to use a CHKFREQ value that ensures reasonable Db2 restart times.
At each system checkpoint, Db2 writes changed pages to the CF or DASD to ensure that Db2 can recover in a reasonable amount of time if necessary. Setting a reasonable interval for checkpoints is an important aspect of overall health of Db2, and taking frequent Db2 system checkpoints is a very important element of designing for fast Db2 crash restart because it bumps forward the start point for log scan during restart after a Db2 crash.
A myth exists that system checkpoints are expensive, and they can be if system checkpoints are taken very infrequently! However, more frequent system checkpoints require less work at each checkpoint. Setting the system checkpoint frequency based on time also simplifies administration (set and forget). Whereas, a setting based on the number of log records requires an ongoing commitment to proactive tuning of the setting.
Using frequent time-based system checkpoints on all Db2 members also offers the following advantages:
- Bumps the start point forward for log scan for GRECP recovery
- Even idling Db2 members continue taking checkpoints
- Brings predictability to tracking and reporting of long running URs based on URCHKTH
Prior to Version 10, you could set the checkpoint frequency based on either time, or the number of log records processed. The thought was that a time-based frequency was reasonable if your workload was balanced throughout the day, but if you had periods (such as batch) where a large amount of work was done, checkpoints based on log records could provide a more predictable recovery time. You could choose time or log records—whatever worked better for your shop. Version 10 introduced the ability to specify checkpoints based on both time and the number log records in combination. Db2 would take checkpoints based on whichever threshold (time or number of log records) was reached first. The nice part of this change was that under normal circumstances, Db2 would predictably take time-based checkpoints, and if you had workload spikes or peaks, Db2 would also checkpoint based on log records, adding a secondary check to help ensure reasonable recovery times. However, in the ZPARM survey, we found that the majority of clients (80%) take Db2 checkpoints using only a time-based frequency, with values of 3 or 5 minutes being most common. The statistical mode (most often specified value) was 5 minutes. The Db2 SWAT team works extensively with customers on best practices and recommends a value of 3 minutes.
CHKMINS range (1 - 5)
The range of accepted values for the
CHKMINS zparm is also changed to
1 - 5 minutes. The previous accepted range was 1 -1439 minutes. The goal of this change is the same as for CHKFREQ. When the CHCKTYPE zparm is set to BOTH, the CHKMINS specifies the number of minutes between checkpoints (and CHKFREQ specifies the number of log records between checkpoints).
PCLOSET=45 (and subsequent removal of PCLOSEN)
The
PCLOSET ZPARM is one of two ZPARMs (PCLOSEN is the other) that have controlled how long page sets remain read-write after the last update.
PCLOSET specifies the number of minutes that a page set or partition remains in read-write state after the last update. It also specifies the duration for the deferred close of GBP-dependent objects (pageset/partition) that are defined with the CLOSE YES attribute. If there is no read or write activity to objects for the duration of PCLOSET, the object is physically closed, the associated page set P-lock is removed, and the object can cease to be GBP-dependent.
Converting page sets to read-only too frequently can incur significant costs from expensive SYSLGRNX processing, growth in SYSLGRNX page sets, frequent dataset close and re-open, and ping-ponging in and out of GBP dependency. For that reason, we have increased the default setting to 45 minutes, which aligns with best practices, based on many client engagements.
The PCLOSEN setting specifies a threshold for the read-only conversions based on the number of consecutive checkpoints. However, it will be removed by the future fourth APAR in this effort for the following reasons:
- It is a multiplier of the checkpoint frequency, which many clients also specify in minutes, which makes it redundant with the also time-based PCLOSET.
- If the checkpoint frequency is based on the number of log records and not proactively managed, certain activities that cause many checkpoints, such as implicit checkpoints triggered by active log switching or the use SQL for ETL operations, can cause too-frequent read-only conversion.
- Many clients already disable it by setting it to a high value, even maximum 32767.
Most changes to default values only affect new Db2 installations, unless you proactively adopt the new values. However, if you currently use PCLOSEN as the dominant setting for controlling read-only conversions, you'll need to determine an appropriate PCLOSET value that approximates the timeframe for read-only conversions in your installation.
MAXRBLK=1000000
The
MAXRBLK ZPARM specifies the storage size in kilobytes for the RID pool. The new default setting, which is of 1,000,000 kilobytes, is more appropriate for today's data values and the increased availability of storage than the previous default setting of 400,000 kilobytes. Also, the introduction of partition-by-range UTS with relative page numbering (PBR RPN) in Db2 12 increased the size of each RID.
NPGTHRSH=1
The
NPGTHRSH ZPARM specifies that Db2 favors matching index access, through the index with the most matching columns, for tables or partitions that have fewer data pages than the specified value. The previous default of 0 meant that Db2 always chose the access path based on cost estimates, regardless of the number of data pages that statistics indicate for the table or partition.
INLISTP=1000
The
INLISTP ZPARM specifies the maximum number of elements in an IN-list for certain IN predicate optimizations to occur. The previous default value 50 did not reflect current best practices.
Part 2: Removal of ZPARMs for SPT01 directory table space settings (PH24358)
The second part of this effort was delivered in June 2020 with
APAR PH24358, which removes two ZPARMs related to the SPT01 directory table space.
Db2 12 always operates as if the following removed ZPARMs have the indicated setting. If you use a different value than these in your current Db2 environment, you’ll need to make appropriate adjustments to your setting before you apply this APAR.
COMPRESS_SPT01=YES
Db2 12 always compresses the SPT01 directory table space to align with current best practices. Compression is low cost and a best practice for scalability. This is a change from the previous default behavior, which is uncompressed.
SPT01_INLINE_LENGTH=32138
Db2 12 always maintains the maximum supported length for LOB column data in the SPT01 directory table space inline in the base table to align with current best practices. All clients surveyed already use the maximum value.
Part 3: Removal of ZPARMs related to conversion to extended 10-byte RBA and LRSN log record formats (PH26317)
The third part of this effort was delivered in July 2020 with
APAR PH26317, which removes two parameters related to migration to the
extended format introduced in Db2 11. Use of the extended format, which uses 10-byte RBA and LRSN values, is consistent with Db2 strategy, and it is necessary to support LRSN and Log RBA values beyond the 6-byte limit.
Db2 12 operates as if these removed ZPARMs have the following settings:
OBJECT_CREATE_FORMAT=1 (extended)
Db2 12 creates all new objects with the extended log record format to align with current best practices. The basic 6-byte RBA and LRSN log record format is deprecated.
UTILITY_OBJECT_CONVERSION=NOBASIC
Db2 12 requires that utilities convert all objects that still use the deprecated 6-byte RBA and LRSN log record format to use the extended 10-byte format and prevents utilities from converting any objects to use the deprecated format. The previous default setting was NONE.
Part 4: Removal of more obsolete ZPARMs (stay tuned for future APAR PH28280)
Stay tuned for the future fourth APAR for this effort PH28280, which will remove more obsolete ZPARMs. Here is a preview of the settings that Db2 12 is expected to operate with after the PTF for this future APAR is applied:
CACHEPAC=10485760
Db2 12 always allocates 10,485,760 bytes of storage for caching package authorization information for all packages on each Db2 member. (However, if
COMCRIT=YES, the amount of storage is set to 0 bytes.) This is the previous maximum value and an increase over the previous default value, which was 5,242,880 bytes. Most clients accept the current default size, and Db2 can allocate the maximum size with minimal incremental storage use.
CACHERAC=10485760
Db2 12 always allocates 10,485,760 bytes of storage for caching routine authorization information for all routines on each Db2 member. (However, if
COMCRIT=YES, the amount of storage is set to 0 bytes.) This is the previous maximum value and an increase over the previous default value, which was 5,242,880 bytes. Most clients accept the current default size, and Db2 can allocate the maximum size with minimal incremental storage use.
IRLMAUT=YES
Db2 12 always automatically starts and stops the IRLM. All customers surveyed already use this setting.
IRLMSWT=120
Db2 12 always waits a maximum of two minutes for IRLM to start automatically. This has been the default setting since the release of Version 10, and it is considered best practice.
PCLOSEN=32767
Db2 no longer converts a read-write page set or partition to read-only based on the number of checkpoints. The previous default for PLCOSEN was 10 checkpoints. As described above, the
PCLOSET setting now always specifies the interval for the read-only conversion in minutes. The default PCLOSET setting is also increased to 45 minutes, from the previous default of 10 minutes.
MGEXTSZ=YES
Db2 12 always uses a sliding scale for sizing secondary extent allocations for Db2-managed data sets. The sliding scale for secondary extent sizing was introduced in Version 8 to improve availability, it is proven, and most surveyed clients use it.
CHGDC=NO and EDPROP=NO(DPROP SUPPORT field set to 1)
Few clients use the related DATA CAPTURE product.
Sueli Almeida is Db2 for z/OS DevOps and Cloud Provisioning Technical Leader, John Campbell is a Distinguished Engineer for Db2 for z/OS development and Paul McWilliams is an Information Developer for Db2 for z/OS documentation.
Sign in and subscribe to always get the latest news about Db2 for z/OS from the IBM lab: http://ibm.biz/db2znews-subscribe#Db2forz/OS