DB2 & IMS

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:

Keyword Categories

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.

Deprecated DSNZPARMs

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.

Removed DSNZPARMs

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:  

2 Pages