Jun 1 ’11
A First Look: DB2 10 DSNZPARM Changes
If you’re planning an upgrade to DB2 10 for z/OS, you’ll want to be aware of the changes to DB2’s subsystem initialization parameters, better known as DSNZPARM members. The keywords on this set of macros control many aspects of DB2’s behavior. This article reviews DSNZPARM changes in DB2 10. DSNZPARMs should be reviewed as part of your upgrade plan to avoid any surprises with DB2’s behavior.
For insight on DSNZPARMs, how changes are made and implemented to them, and their evolution before DB2 10, see these previous z/Journal articles:
- “The Good, the Bad and the Really Ugly: DB2’s DSNZPARM Module” (www.mainframezone.com/applications-and-databases/the-good-the-bad-and-the-really-ugly-db2s-dsnzparm-module)
- “Just the Good This Time: More DB2 DSNZPARM Keywords” (www.mainframezone.com/applications-and-databases/just-the-good-this-time-more-db2-dsnzparm-keywords).
DSNZPARM keywords fall into three categories: externalized, hidden, and opaque:
- Externalized: Described in the Installation Guide (see sidebar), these keywords are by far the most documented. You’re notified if they’re added, deprecated or removed from DB2, their defaults change, or value characteristics are altered. When just about anything happens to an externalized DSNZPARM, it’s documented somewhere.
- Hidden: These DSNZPARMs are hidden for a reason; no one should be messing around with them without direction from IBM. They’re often put in place as service aids and can help DB2 Level 2 support gather information about a problem or devise a short-term solution until the Authorized Program Analysis Report (APAR) is closed and a Program Temporary Fix (PTF) is prepared.
- Opaque: Although these keywords aren’t completely documented in the manuals, they aren’t completely hidden, either. The term emerged because more DSNZPARMs were being delivered via APARs, often in the form of switches that enabled or disabled features. These APARs often explained much of the detail around the new DSNZPARM keyword. Like hidden DSNZPARMs, they aren’t meant for general use, but, like an external DSNZPARM, they’re well-documented.
The following subsystem initialization parameters have nothing in their future but to someday be removed from DB2:
- DISABSCL on DSN6SPRM macro by default (NO) sets SQLWARN1 and SQLWARN5 for non-scrollable cursors on OPEN and ALLOCTE CURSOR. It was introduced in DB2 Version 7 by APAR PQ65622 as an opaque DSNZPARM.
- OJPERFEH on DSN6SPRM macro by default (YES) enables several performance enhancements in outer joins. Overriding the default by specifying NO disables the enhancements. Almost always, this value should be set to YES. This opaque parameter was introduced using a hidden DSNZPARM in DB2 V5 and later updated to an opaque DSNZPARM. APARs PQ29780 and PQ48485 have additional details.
- OPTIOWGT on macro DSN6SPRM enables support for an improved formula for balancing the costs of I/O and CPU speeds. This support was added in DB2 9 via APAR PK61277. ENABLE is the default as of APAR PK75643.
- OPTIXIO on macro DSN6SPRM is an opaque parameter that can improve I/O with significantly less sensitivity to buffer pool and object size when the current default (ON) is used. This function was delivered in DB2 V8 via APAR PK12803; the default was changed to ON with APAR PK26613.
- PTCDIO, an opaque parameter on macro DSN6SPRM, is a switch to turn off a change made to determine the cost of using an index by APAR PQ86763 in DB2 V7. The actual DSNZPARM parameter was added via APAR PQ97866 with a default of OFF. You shouldn’t enable this parameter without guidance from IBM support.
- RETVLCFK is an opaque parameter on macro DSN6SPRM. If this parameter is set to its default, NO, VARCHAR data isn’t returned from an index. However, setting this parameter to YES enables index retrieval of VARCHAR data and that data is padded to the full column length. Although improved performance might be realized with index-only access if specifying YES, you would have to ensure all applications could handle the extra trailing characters. If running DB2 9 or later, an alternate solution is to use NOT PADDED indexes. This parameter was introduced in DB2 V5 by APAR PQ10465.
- SEQCACH on the macro DSN6SPRM controls whether DB2 prefetch uses sequential access for reading the cache on a 3990 controller. The default in DB2 10, SEQ, prompts use of sequential access. BYPASS tells DB2 prefetch to bypass the cache.
- SEQPRES on macro DSN6SPRM affects how long a utility scan leaves the data in the cache. The default value in DB2 10 is YES; this setting leaves DB2 utility prefetch reads in cache longer.
- SMSDCFL and SMSDCIX parameters on macro DSN6SPRM support specifying a DFSMS data class for a table space and indexes. The default is a blank string. These parameters were introduced in DB2 V7 by APAR PQ32414. As of DB2 9 NFM, DATACLAS, MGMTCLAS, and STORCLAS are included as syntax on the SQL statements CREATE/ALTER STOGROUP and should be used rather than the DSNZPARM parameter.
- The STATCLUS parameter, also on macro DSN6SPRM, specifies the type of clustering statistics RUNSTATS collects. The default is ENHANCED clustering statistics, which should result in an improved CLUSTERRATIO formula. STATCLUS was added to DB2 9 on installation panel DSNTIP6 but was removed in DB2 10, resulting in an opaque parameter.
When you upgrade to DB2 10, some DSNZPARMs will be removed from your DB2 subsystem. The following provides two lists of DSNZPARMs—one for a DB2 9 to DB2 10 upgrade and the second for a DB2 V8 to DB2 10 upgrade, listing which subsystem parameters are being removed. Be careful with a skip-level migration because you’re eliminating two sets of DSNZPARMs.
For a DB2 9 to DB2 10 upgrade, these subsystem parameters are removed:
- EDMBFIT is no longer needed. Since DB2 V7, the single Environmental Descriptor Manager (EDM) pool was divided into four separate pools. You should use the default, NO, for EDMBFIT and increase the EDM pool size to reduce latch class 24. This decreased the need to use EDMBFIT = YES, eliminating the need for this DSNZPARM.
- LOGAPSTG is the log apply buffer.
- MAX_UTIL_PARTS was introduced to DB2 V8 and DB2 9 by APAR PK51853 to control the number of compressed partitions LOAD or REORG can process. This subsystem parameter was removed in DB2 10 because the limit restriction was removed.
- OPTHYBCST (PK90334), OPTIXOPREF (PK68986), and OPTOIRCPF (PK89637) introduced optimization enhancements to DB2 V8 and DB2 9; the enhancements were incorporated into DB2 10. The APAR numbers provide details of what each parameter does.
- PARTKEYU provides the ability to update the partitioning key; it’s incorporated into DB2 10.
- PREVALKEEP was removed in DB2 10 to allow thread re-signon by a different user after COMMIT when NEXTVAL or PREVAL are used.
- REORG_IGNORE_ when set to YES, used 0 (zero) for PCTFREE and FREEPAGE when data was reloaded into a table space.
- SJMISSKY enabled a star join performance enhancement in previous versions; it’s included in DB2 10.
- XMLTABJPD is for an XML optimization enhancement delivered in DB2 9 by APAR PM05664; it’s incorporated in DB2 10; see the APAR to learn more.
A skip-level migration from DB2 V8 to DB2 10 will involve removal of these additional subsystem parameters:
- DBPROTCL is no longer supported; the DBPROTOCOL bind option is DRDA by default.
- MAX_OPT_ELAP specifies the maximum amount of elapsed time the DB2 optimizer can consume.
- MORE_UNION_DISTRIBUTION when set ON, can improve performance of queries using views defined with UNION ALL.
- RELCURHL is an option to hold a lock over a commit.
- STORPROC is the parameter for creating stored procedures.
- SUPPRESS_TS_CONV_WARNING is the option to turn off messages when DB2 converts a table space from index-controlled to table-controlled partitioning.
- TABLES_JOINED_THRESHOLD sets a limit (16) on table joins.
These ZPARMs would have been removed in a DB2 V8 to DB2 9 upgrade.
It’s important to be aware of which subsystem parameter default settings were changed in DB2 10. A failure to specify different options in the DSNZPARM parameters in your Assembler job means you accept the default settings. Consider whether there are benefits to using a different value. To facilitate your planning efforts, we’ve provided the default changes. See Figure 1 if you’re planning a skip-level migration from DB2 V8 to DB2 10. Refer to Figure 2 if you’re moving from DB2 9 to DB2 10.
You should also be aware of changes to maximum values for DSNZPARMs. For example, the big winners in DB2 10 are DSNZPARMs that deal with threads. The maximum number of user threads (CTHREAD), maximum batch threads (IDBACK), maximum foreground threads (IDFORE), and maximum DBATs or distributed threads (MAXDBAT) increased tenfold; CTHREAD, IDFORE, and IDBACK increased from 2,000 to 20,000, and MAXDBAT increased from 1,999 to 19,999.
These four increases are significant because they could reduce the dependency on data sharing. To achieve higher thread counts in previous releases, some users had to take advantage of data sharing. Those users may now be able to reconsider that decision.
MAXOFILR is another DSNZPARM that made a dramatic jump in size; the maximum number of data sets that can be open concurrently for processing Large Object (LOB) file references increased from 2,000 to 20,000.
DSSTIME, the interval in minutes DB2 waits before resetting the data set statistics for online performance monitors, dropped from a maximum of 1,440 to 60 minutes.
CACHEPAC and CACHERAC, the authorization caches for packages and routines, both increased from 5MB to 10MB, essentially doubling in size.
STATIME is the time interval in minutes between statistic records collection. The maximum value dropped from 1,440 to 60 minutes. That’s still a pretty large number, but the default was lowered to one. That’s good news because when statistics are collected at longer time intervals, you can’t report on any interval less than the interval used for collection. Sometimes, statistics collected at a granular interval are necessary to diagnose a performance problem.
STATIME and SYNCVAL, the interval used to sync up your data sharing members, in DB2 10 only apply to IFCIDs 0105, 0106, and 0199. IFCIDs 0001, 0002, 0202, 0217, 0225, and 0230 are now always written at a fixed, one-minute interval; they’re no longer affected by the STATIME value specified.
MONSIZE, the default buffer size used for monitor traces, was increased from 16MB to 64MB. The benefits should be apparent.
This article doesn’t cover the many new DSNZPARM keywords added in DB2 10; that will be the basis for a future article. Until then, enjoy your powerful DSNZPARMs. To learn more, visit the DB2 for z/OS Information Center at http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z10.doc/db2z_10_prodhome.htm.