By Tran Lam, Akiko Hoshikawa, Haakon Roberts, Jim Pickel, Gayathiri Chandran, and Paul McWilliams
In a continuation of the simplification effort for Db2 subsystem parameters (ZPARMs) in Db2 12, Db2 13 introduces changes the default settings for various subsystem parameters (zPARMs) to better match current practices, and where appropriate, it removes obsolete and rarely use subsystem parameters to simplify installation, migration, and management of Db2 systems.
Db2 zPARM settings generally are not controlled by activation of a function level, so you should expect these changes to take effect immediately when you migrate to Db2 13 at function level 100. In most cases, use the following new settings in your Db2 12 subsystem before starting migration to Db2 13 for best results.
As with the previous ZPARM simplification efforts mentioned above, many of these changes are informed by the results of Chris Crone’s 2017 survey of customer ZPARM settings.
Updated subsystem parameter defaults in Db2 13
To better reflect current best practices and typical usage of Db2, the following zPARMs are changed to use the indicated default settings in Db2 13.
Specifies that the distributed data facility (DDF) is loaded at Db2 startup, and whether DDF starts automatically or by a command. The previous default setting was NO. However, the majority of Db2 customers now use DDF in their subsystems, so the new default reflects realistic usage of most customers.
EDM_SKELETON_POOL=81920 (81,920 KB)
Determines the maximum storage size to be used for the EDM skeleton pool. The previous default setting was 51200. The new default reflects how the majority of customers improve the performance of package allocation. The mean value was 256,000 in the 2017 customer ZPARM setting survey results.
EDMDBDC=40960 (40,960 KB)
Determines the maximum amount of EDM storage to be used for DBDs. The previous default setting was 23400. The new default value better supports the conversion of multi-table segmented table spaces to UTS. The new default reflects how the majority of customers improve the performance of DBD access. The mean value was 332,000 in the 2017 customer ZPARM setting survey results.
Specifies the fast index traversal (FTB) is enabled for non-unique indexes. The previous default setting was NO. The new default improves performance by taking advantage of expanded FTB eligibility for Db2 applications that access indexes in random order. The new default takes advantage of in-memory sort and avoiding work file accesses for improved performance of sort-intensive Db2 applications.
MAXSORT_IN_MEMORY=2000 (2 MB)
Specifies the maximum allocation of storage in kilobytes for a query that contains an ORDER BY clause, a GROUP BY clause, or both. The previous default setting was 1000.
Specifies the maximum number of inactive or new connection requests that can be queued waiting for a DBAT to process the request. The new default setting means that the MAXDBAT subsystem parameter setting specifies the limit. The previous default setting was OFF.
With the previous default setting, the connections waiting for a thread after hitting MAXDBAT still had to reach CONDBAT before failing and being allowed to retry on another member. However, many customers who use "connection pooling" and "Sysplex workload balancing" have increased their CONDBAT setting much higher than their MAXDBAT setting. Without setting MAXCONQW and MAXCONQN, there is no external way to timeout or control the number of connections waiting for thread until CONDBAT is reached, and connections are basically hung until a thread becomes available. Setting MAXCONQN provides more timely reroutes when a member has reached its limits.
Specifies the maximum length of time that a client connection waits for a DBAT to process the next unit-of-work or new connection request. The new default setting means that the IDHTOIN subsystem parameter setting specifies this time limit, the previous default setting was OFF.
With the previous default setting, the connections waiting for a thread after hitting MAXDBAT still had to reach CONDBAT before failing and being allowed to retry on another member. However, as mentioned above, many customers use a CONDBAT setting much higher than their MAXDBAT setting, and setting MAXCONQW provides more timely reroutes when a member reaches limits, and enables connections to timeout to meet SLAs.
Specifies the default maximum number of page, row, or LOB locks that an application can hold simultaneously in a table or table space. The previous default setting was 2000. 2000 locks per object is likely to be too small and cause premature lock escalation, so the default is increased. This change also reflect typical customers usage. The 2017 customer ZPARM setting survey results showed larger values (mean value of 72800) for most customers.
Specifies the maximum number of page, row, or LOB locks that a single application can hold concurrently for all table spaces. The previous default setting was 10000. This change also reflects typical customer usage. The 2017 survey results showed significantly larger values (mean value of 204,000) at the most of customers.
Specifies the size of the output buffer for writing active log data sets. The previous default setting was 4000.
Several customers were suffering output log buffer full situation. Increasing the default setting reduces access to active log datasets to help avoid the unavailable log output buffer condition that impacts the performance of update transaction significantly.
Specifies the default type of page numbering to use for new partition-by-range (PBR) table spaces. The previous default setting was ABSOLUTE.
The strategic position for Db2 is to use PBR with relative page numbers (RPN), not absolute. This change also makes the default behavior consistent for newly created table spaces and table spaces for tables that are converted with the new PBG to PBR conversion support in Db2 13.
Db2 12 introduced PBR RPN table spaces, but the default zPARM setting still specified APN in Db2 12. Creating a new table space as RPN, or converting directly from PBG to PBR RPN, avoids the need for a later costly conversion from APN to RPN, which always requires a REORG of the entire table space.
Specifies the amount of storage size in kilobytes to be used for the sort pool. The previous default setting was 10000 (10 MB). The new default value setting best practices and typical usage.
STATIME_MAIN=10 (10 Seconds)
Specifies the time interval in seconds, for collection of interval-driven statistics not collected at the interval specified by the STATIME subsystem parameter. The previous default setting was 60 seconds.
With the previous default of 60s, DBAs and Db2 system programmers cannot identify true workload peaks, when using Db2 statistics for subsystem level performance tuning and planning. Having a default of 10s, we will collect the necessary frequency of data rather than ask the customer for a recreate of the problem and help to speed up diagnosis by having data readily available.
Extended range for DSMAX in DB2 13
The range of the following subsystem parameter is increased in Db2 13:
DSMAX=400,000 (new maximum value, with existing default 20000)
Specifies the maximum number data sets that can be open at one time. This change supports the increased theoretical maximum number of concurrently open data set in z/OS 2.5
Removed subsystem parameters in Db2 13
To simplify, installation, migration, and configuration of Db2 systems, Db2 13 always operates as if these removed ZPARMs have the following settings.
Specifies the size (in bytes per plan) of the authorization cache that is to be used if no CACHESIZE is specified on the BIND PLAN subcommand.
The plan authorization cache is enhanced in Db2 13 to cache the successful execute on plan authorization checks when access control authorization exit is used for access control. With this enhancement, Db2 provides a consistent behavior for plan authorization caching, regardless of the access control mechanism used. Hence the AUTHCACH zparm is removed and set to the max value of 4K.
You can specify the authorization cache value for a plan by using the CACHESIZE option on the BIND PLAN subcommand. The plan-level CACHESIZE value takes precedence over the default limit of 4K for that plan.
This obsolete zPARM controlled certain characteristics of a connection between a client application and a Db2 for z/OS data server.
This rarely used zPARM controlled whether Db2-managed data sets that are created by CREATE TABLESPACE or CREATE INDEX statements are to have variable VSAM control intervals.
This rarely used zPARM limited the number of extra DRDA query blocks that Db2 is to request from a remote DRDA server.
This rarely used zPARM limited the number of extra DRDA query blocks that Db2is to return to a DRDA client.
This obsolete zPARM specified whether Db2 honored the LOAD and REORG parameter KEEPDICTIONARY when tables were converted between basic row format and reordered row format.
This rarely used zPARM determined when updates to group buffer pool-dependent buffers were written to the coupling facility.
This obsolete zPARM specified whether to exclude trailing columns from the table-controlled partitioning keys when tablespaces are converted from index-controlled partitioning to table-controlled partitioning.
This rarely used zPARM controlled the maximum number of archive log volumes that were recorded in the BSDS.
This rarely used zPARM determined the number of inactive TYPE1 DBATs that Db2 allowed.
This rarely used zPARM specified whether Db2explicitly blocked sort operations when the OPTIMIZE FOR 1 ROW clause was specified on a query. This setting was difficult for customers to use other than in serviceability scenarios.
This obsolete zPARM specified the types of SQL statements for applying the PLANMGMT subsystem parameter setting. STATIC was the only supported value.
This rarely used zPARM controlled the efficiency that Db2 assumes for parallelism when Db2 chose an access path. This setting was difficult for customers to use and the default value is sufficient.
This obsolete zPARM specified whether Db2 should manage real storage consumption. Unlike the other ZPARMs mentioned here, it is best to leave this setting unchanged in Db2 12, even if you're not currently using the default value AUTO. Db2 13 introduces real storage management improvements that eliminates the need for setting this parameter, which are not yet available in Db2 12. For the full story, check out earlier blog entry about REALSTORAGE_MANAGEMENT.
This rarely used zPARM specified whether to enable or disable multiple index access on some non-Boolean uncorrelated subquery predicates. This setting was difficult for customers to use outside of serviceability scenarios.
This rarely used zPARM specified whether to start the global trace automatically when Db2 is started. The existing trace commands provide sufficient control over the global trace.