In our example, DSNZP001 would be reloaded and become the active ZPARM module. The keyword STARTUP is your lifeboat. When issuing the -SET SYSPARM STARTUP command, the original ZPARM load module, loaded at DB2 start-up and cached when the first -SET SYSPARM command was issued, now becomes the active ZPARM module again. This lets you get back to wherever you started should you run into problems with the modified ZPARM module. You always want to leave yourself some way to get back to a correctly working condition.
It’d be wonderful if every DSNZPARM keyword could be changed and then activated using the -SET SYSPARM command. Unfortunately, that isn’t yet the case. Each ZPARM macro has a subset of keywords that can be activated by the -SET SYSPARM command after being changed. A list of all ZPARMs is included in the appendix of all DB2 Installation Guides since DB2 V7. This list identifies which keywords can be activated when issuing the -SET SYSPARM command. Although this list of online changeable keywords has increased significantly, it’s still a subset of the total keywords available and there still exist many parms that can’t be modified by the -SET SYSPARM process. If an attempt is made to issue the -SET SYSPARM command for a ZPARM load module that contains disallowed modified keywords, the changed parameter is ignored. In addition to those keywords that can’t be changed, some aren’t immediately activated by -SET SYSPARM even though they’re eligible for modification.
DB2 supplies several methods to determine the value of the currently active ZPARM module. A sample program, DSN8ED7 (prepared by IVP job DSNTEJ6Z), is shipped with DB2 and can be used to generate a formatted listing of all current DSNZPARM settings. This program, provided in DB2’s SDSNSAMP library, calls the DB2 supplied stored procedure, DSNWZP, which Visual Explain and the Control Center use for the same purpose. Visual Explain, DB2 PM, and other tools can report the current DSNZPARM settings. This is important when you need to validate what keyword values DB2 is using. You can’t always count on the DSNZPARM source to be the most accurate place to check keyword settings.
If you enjoy doing things at the trace level, IFCID 106 will contain a structure for each of the macros that compose ZPARM and DSNHDECP when the trace containing IFCID 106 is started. The 106 record is recorded when activating accounting class 1 (a default accounting trace class), statistics class 1 (also a default statistics trace class), performance trace with classes 1 through 14 activated, monitor class 1, and global trace with class 2 or 3 started.
The authid issuing the -SET SYSPARM command must have in its set of privileges the SYSADM, SYSCTRL, or SYSOPR privilege. In V8, the Installation SYSADM or Installation SYSOPR IDs became eligible for online update. In addition, this authid must be associated with the Install SYSADM authority to change either the Installation SYSADM or Installation SYSOPR IDs in DSNZPARM. If an ID without the necessary authority issues the -SET SYSPARM command, the original SYSADM or SYSOPR DNSZPARM values remain intact.
What Can You Change?
There are many externalized DSNZPARM keywords and, fortunately, they’re all well-documented in the DB2 Installation Guide , the DB2 Administration Guide, numerous Redbooks, and the DB2-L listserver. That’s why they’re called externalized. Nevertheless, let’s review the “other” keywords.
DB2 has always had ZPARMs that are externalized through the installation panels and a set of hidden and undocumented keywords put in place as service-ability tools for Level 2 support. Back around DB2 V5, a third type of keyword was introduced: the “opaque” ZPARM. Opaque keywords can’t be initially set or changed through the installation panels, are often not described in the installation manual (although that has recently started to change) and can easily disappear in a subsequent release of DB2. They aren’t considered hidden parameters because they’re well-documented in the APAR that introduces them to DB2. They’re often used to activate functions a small subset of DB2 customers need while preventing those new functions from negatively affecting the rest of the DB2 community. The following is a brief discussion of a few of the many opaque DSNZPARMs.
The DSNZPARM’s SMSDCFL and SMSDCIX were introduced to DB2 via APAR PQ32414 and allow control over whether System Managed Storage (SMS) data set class names are used for DB2’s tablespaces and indexes. If specified, DB2 passes that value to Access Method Services to define a tablespace or index. If left blank, DB2 by default doesn’t assign a data class at data set creation. You can activate changes to SMSDCFL and SMSDCIX using the -SET SYSPARM command with V8.
Like so many opaque parameters before it, the DSN6SPRM macro’s keyword NPGTHRSH also became part of DB2 via an APAR, PQ33429. The parameter was initially added to improve access path selection for Enterprise Resource Planning (ERP) applications that referenced tables with inaccurate statistics. Certain ERP applications use DB2 tables as a type of work file. When the application isn’t running, the tables are empty, or nearly so. However, when the application is in use, there could be tens of thousands, even hundreds of thousands, of rows in them. Because there’s no way to gather statistics about these tables while they’re running, DB2 will usually choose a tablespace scan.