Apr 1 ’05
The Good, the Bad and the Really Ugly: DB2’s DSNZPARM Module
Don’t worry, this isn’t a movie review of a 1966 spaghetti western and you’re still reading the right magazine. That style of movie disappeared almost 20 years before DB2 was even announced. This article examines a small part of DB2—a single load module (and the macros that compose it) called DSNZPARM, which is near and dear to many, yet also challenging and sometimes frustrating. This article discusses:
- The “good” you can accomplish by recognizing which DSNZPARM parameters can and should be changed
- The “bad” that used to be associated with the effort it took to change a DSNZPARM parameter and activate that change
- The “really ugly” stuff that can happen if you touch things you don’t understand or shouldn’t be touching.
DSNZPARM, also affectionately called ZPARM, refers to the data-only subsystem parameter load module that contains DB2’s execution time parameters. When the macros that comprise DSNZPARM are assembled and link-edited into a load module, you have other naming choices. The accepted format is DSNZPxxx, where xxx is some string of characters that will make your ZPARM member names unique. The initial name chosen will be specified on the installation panel DSNTIPO. The ZPARM load module, regardless of what you call it, will reside in the hlq.SDSNEXIT load library.
In a data-sharing environment, SDSNEXIT is a shared resource across the data-sharing group. Each individual DB2 member in the group will have its own unique DSNZPARM load module. The name(s) chosen for the DSNZPARM load module can be optionally specified as a parameter of the DB2 START command. This lets you start DB2 using different copies of DSNZPARM.
The assembly of DSNZPARM requires some source code where DB2’s processing values are set. The source is initially created via installation of CLIST DSNTINST and is placed in the DSNTIJUZ installation job stream. Once the source is created, how are changes to the keywords on the DSNZPARM macros handled?
The recommended approach for modifying DSNZPARMs is via the DSNTINST installation CLIST. Updates are made through the panels and DSNTIJUZ is replaced in hlq.SDSNSAMP1 or hlq.NEW.SDSNSAMP. This job can be submitted and one of the outputs is a new DSNZPARM module. This may seem pretty straightforward until you have to change one of the “opaque” or “hidden” ZPARMs (because of Level 2 Support recommendations or because a PTF has been applied). Now you’ve created an entry in ZPARMs that can’t, and won’t, be updated by the install process. When the DSNTIJUZ job is replaced by the installation CLIST, you’ll probably lose your changes.
These are examples of why a process is needed to prevent inadvertent loss of changes to DSNTIJUZ. Actually, changes are often made directly to DSNTIJUZ values rather than using the formally described DB2 methods. Why? For some, it will seem easier; for others, it will be out of necessity. Which is better? Both have their place. Using the CLIST ensures the DSNTIDXA input parameter member is updated. This member contains the default installation parameter values. DSNTIDXA is then used as input to the install CLIST and lets the install CLIST initialize all the ZPARM values in the panels to their last values. This module is also used during DB2 release migration. But editing DSNTIJUZ directly allows the modification of any ZPARM keyword, even those not supported by the install CLIST or supplied by an APAR during the maintenance process. Sometimes, editing DSNTIJUZ is the only way to change certain values.
Whichever method you use, be very careful and ensure backups are made before either process is allowed to modify the DSNZPARM values. Even something as simple as ISPF’s compare option (option 3.13) can be used to check for changes between old and new copies of DSNTIJUZ. There are also DB2 and vendor-supplied methods of verifying the values in DSNZPARMs. Remember that if you’re running all the steps created in the DSNTIJUZ job stream, this job will also run DSNTIMQ and assemble and link the DSNHDECP module.
The installation panels are used to set more than the DSNZPARM values. Other values set up by the install process include values for the IRLM procedure and the data-only modules DSNHDECP and DSNHMCID.
And on the subject of macros and load modules, let’s consider “who” these macros are, how they’re used, and the steps associated with becoming a functioning DSNZPARM load module.
DSNZPARM results from expanding seven macros: DSN6ENV, DSN6ARVP, DSN6LOGP, DSN6FAC, DSN6GRP, DSN6SYSP, and DSN6SPRM. Each has multiple keywords that can be “set” to some value, or allowed to default to a predefined value, that controls DB2’s behavior:
- The DSN6ENV macro describes the DB2 environment and sets global variables the other DSN6 macros use.
- DSN6ARVP contains the parameters used to control selected archive log functions.
- DB2’s log manager externalizes its initialization parameters through the DSN6LOGP macro.
- DSN6FAC controls the start options of the Distributed Data Facility (DDF) and other related distributed functions.
- DSN6GRP includes miscellaneous initialization parameters for group functions in a data-sharing environment.
- DSN6SYSP contains miscellaneous system initialization parameters.
The largest number of parameters externalized is for the database services address space, also called the DBM1 or ADMF address space. These parameters show themselves on the DSN6SPRM macro.
The Bad (But Improving)
Before DB2 V7, the IBM supported method for making changes to DSNZPARM was straightforward and simple. The keywords on the macros were updated to their new values, the macros were assembled, and then link-edited into a new load module. DB2 would then have to be stopped and restarted to load the newly created load module into storage so changes could be activated. This process caused a DB2 outage.
Enter DB2 V7 with a long-awaited alternative, the DB2 -SET SYSPARM command. By issuing this command, you could load into storage a new parameter load module whose name you specified on the command. This made the process of changing DSNZPARM’s keywords more acceptable.
Often, the DB2-SET SYSPARM command is described as a way to dynamically change DSNZPARMs. That’s not entirely accurate. The -SET SYSPARM command allows for replacing the data-only load module currently being referenced by DB2 with a different load module. Generally, individual ZPARM keywords still can’t be dynamically changed. You must still edit the DSNZPARM source, modify the macro’s keywords to a different value, assemble the modified source, and link-edit a new load module.
The -SET SYSPARM command only allows the dynamic replacement of that load module. Even though a new load module is loaded, not all the ZPARM parameters changed are eligible for activation using the -SET SYSPARM command. Parameters still exist that require DB2 to be recycled before they can become active.
At DB2 startup, using the -START DB2 command, the DSNZPARM module, whose name you can optionally specify on the -START command, is loaded into DB2’s storage. If you specify no name, the load module name defaults to DSNZPARM. Let’s assume the default name is used when the
-START DB2 command is issued with no additional parameters. DB2 is now processing using a load module in storage called DSNZPARM. Assume you determine some keyword(s) in the ZPARM source need to be changed. Usually, this source is a copy of the installation job DSNTIJUZ from hlq.SDSNSAMP that has been saved to another location. The appropriate changes are made to the macros, the source is then assembled and link-edited into hlq.SDSNEXIT as a new load module that, in this case, will be called DSNZP001.
The -SET SYSPARM command is issued, specifying the new load module name. The format of the command for this first action is -SET SYSPARM LOAD(DSNZP001). The SET SYSPARM command doesn’t replace the originally loaded module. DSNZPARM is still cached in DB2’s storage. However, DB2 is now processing using DSNZP001. The command’s LOAD keyword will always load a new copy of the ZPARM load module into storage. If you specified no load module name, DSNZPARM is the default load module name.
What if you make more changes and link-edit the new changes into the same load module DSNZP001? To activate them, you use a slightly different format of the command. Issuing the command -SET SYSPARM RELOAD will reload the last load module named into storage, replacing whatever is currently active.
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.
As the table becomes more populated, data access would become less optimized, affecting response time performance. NPGTHRSH, when set, tells DB2 that if the table had less than some number of pages, to try an index access rather than a tablespace scan. A suggested starting value is 10. DB2 will attempt index access for all tables with 10 or fewer pages. This lets DB2 use standard cost-based optimization for all objects greater than 10 pages. There’s also a special setting for this keyword, -1, which will all but disable DB2’s cost base optimization. So this may not be your best choice.
A negative aspect of using this keyword is the fact that it’s systemwide, which means DB2 attempts index access for all tables with low or invalid statistics. DB2 V8 introduces an alternative solution with the new CREATE TABLE keyword, VOLATILE, which could reduce the use of NPGTHRSH in some instances because it turns on index access for all objects, if an index is available, defined with the keyword. With V8, changes to NPGTHRSH can now be activated using the -SET SYSPARM command.
There are parameters that control warning messages for non-scrollable cursors, how IN-list processing occurs, rolling up accounting records for parallel tasks, controlling measured usage pricing, start join processing, outer join processing, and more. Which opaque keywords are available for change depends on what version of DB2 you’re running.
The Really Ugly
Just because a parameter exists doesn’t mean you have to tune it. Often, these hidden parameters were added as serviceability aids for DB2 Level 2 support. They’re available to solve specific DB2 issues with guidance from support personnel and aren’t for the general public to play with. They can come and go from release to release. In fact, they may even disappear during the life of a single release.
Often, when you mention hidden ZPARMs, just like a Pavlovian response, you’ll almost invariably have someone inquire about the hidden EXPLAIN tables—that mystical set of tables that, if you could only access them, would answer all your SQL performance questions. Of course, that’s not going to happen. You still can’t formally access them, and if you could, those hidden tables wouldn’t be the panacea they’re perceived to be.
The SQL EXPLAIN statement was first introduced in March 1986, with the availability of DB2 V1.2. It gave DBAs and programmers their first official glimpse at what the optimizer was up to (or more correctly, how access path selection was working).
It didn’t take long for folks to learn there was a lot more information about processing of an SQL statement that wasn’t available through the externalized EXPLAIN tables. This was considered “secret” information being written to a set of “hidden tables” that DB2 used internally. You could enable these tables by “turning on” a hidden ZPARM and creating several tables with no externalized Data Description Language (DDL). So the quest was on to “discover” how to gain access to these mysterious hidden EXPLAIN tables and the hidden ZPARM that controlled their access. Everyone seemed to know they existed, but the hidden tables proved elusive. To this day, it’s still a challenge to locate them, as well it should be.
It’s probably no surprise that this article doesn’t address how to access the hidden EXPLAIN tables or modify the hidden ZPARM to enable access to them. But rather, we’ll ask the question: “Why do you really want to access these tables and, if you could, should you?” There’s never been any real advantage to having access to the “hidden” EXPLAIN tables.
Do they really contain information that will be useful in solving all your tough SQL performance issues? Probably not. These tables are a service aid for DB2 Level 2 support and were never meant to be externalized. Deciphering what’s in them isn’t as straightforward as one might hope. In fact, using these hidden tables could easily take you down the wrong path, wasting valuable time, looking at areas that aren’t involved in the performance issue you’re attempting to remedy. If you still think you need to solve this mystery, check out the new V8 Visual Explain. Much of the information you’re seeking may already be available to you.
Data Sharing Considerations
Then, of course, there are always the concerns that might arise from using data sharing. In a data-sharing environment, each data-sharing member will have its own DSNZPARM module. This is necessary because each data-sharing member runs its own address spaces independently and each set of address spaces is probably running on a different processor. It’s beneficial because it allows each data-sharing member to be configured uniquely. You should be careful to ensure that each member’s ZPARM source is kept unique, possibly using a different naming convention for each data-sharing member.
You should take advantage of RACF, or some equivalent product, to secure everything associated with your DSNZPARM module. The load library containing the load module should always be write-protected in all environments to ensure the DSNZPARM load isn’t accidentally deleted or overlaid. Starting a DB2 subsystem could be challenging without a DSNZPARM module.
Don’t forget about the DSNZPARM source, the macros, and their current value settings. The source needs to be protected. You should control who can modify it. You should be able to keep multiple versions. Changing the source used to create the load module must be coordinated so one person’s changes don’t unintentionally supersede another’s. You should also get into the habit of using BROWSE when you’re simply “looking” at the source. That could prevent a keyword from accidentally getting changed. In addition, you should restrict the SET SYSPARM command for control over when a DSNZPARM module is switched. By default, the ability to issue this command is part of the privilege set belonging to SYSOPR, SYSCTRL, and SYSADM.
Take time to become familiar with the effects of changing the parameters in DSNZPARM, learn the use of the -SET SYSPARM command, and always keep a copy of the DB2 Installation Guide nearby. With every release of DB2 and the introduction of certain PTFs, new DSNZPARM parameters are introduced, existing parameters are changed, and some parameters are removed, so make sure you’re staying current with maintenance. Follow these simple practices, and DSNZPARM will always be “good” to you and will never show you its “ugly” side.