Discussing DSNZPARM keywords is a great pass time for many of us. In fact, ZPARMs are discussed so frequently that about 10 years ago we started to classify ZPARMs into three different states.
First, there are the completely exposed, external ZPARMs. These are the ZPARM keywords described in the Installation Guide and referenced through the installation panels. They are by far the easiest for find information about. We tell you when we add them, deprecate them, remove them from the product, change their defaults, etc… In fact, just about anything that happens to a ZPARM keyword described in the Installation manual is documented someplace……
Next, the are the infamous hidden ZPARMs. I can remember a time when one would get chewed out for just mentioning them. And if you were a non-IBMer talking about them, like at a user group meeting or conference, you were almost guaranteed that the back of the room would have a strong contingent of IBMers in attendance.
The hidden ZPARMs, as I once explained at a conference, are hidden for a reason; we didn’t want you to mess around with them. Often, they are (were) put in place as a service aid, something DB2 Level 2 support could use to fix a very specific problem, gather more information about a problem, or use as a short term solution to a problem until a PTF could be made available. They were not there for everyone to mess around with. I know, some considered it a huge challenge to track these little guys down to figure out what they did or how they might be used. I’m not sure why someone would want to mess around with something that wasn’t documented, but they did.
I think the most famous of all hidden ZPARMs had to be the ZPARM that enabled the hidden EXPLAIN tables. Of course, that particular hidden keyword is of no interest or use any more. Just about everything , if not everything, EXPLAIN could possible tell you is now externalized in over a dozen EPLAIN tables. If you want to know more about all of the EXPLAIN tables, check out my post from February 2010 “Explaining the history of EXPLAIN tables”.
Finally, there are the opaque DSNZPARM keywords. I’m not sure who coined this term. I’m sure I will probably find out after posting this blog entry. Maybe that person will step forward an tell us.
But getting back on topic…. Someplace around the turn of the century (that sounds both weird and ominous), someone decided to start explaining the ZPARM keywords that aren’t completely documented in the manuals yet not completely hidden from our sight either. In my opinion, this happened because more ZPARM were being delivered via APARs, often in the form of switches to enable/disable new features. These APARs almost always contained detailed covers that explained everything you could possible want to know about the ZPARM keyword. Some of these explanations were even more detailed that the explanations of the exposed ZPARMs in the Installation Guide. They were not really for general use, a little like a hidden ZPARM, but they were well documented like an external ZPARM… so someone decided to call them opaque ZPARM keywords.
So, why all the stuff on naming ZPARMs, other than I like to ramble?
The web based product documentation for DB2 10 and DB2 9 (not sure about DB2 Version 8 ) and the DB2 9 and DB2 10 Installation and Migration Guide PDFs have a section called “Subsystem parameters that are not on installation panels” that list out all of the ZPARMs NOT included on the installation panels. This makes it so much easier to find them and modify them.
Here is what that section contains in the DB2 10 documentation…
- CACHEDYN_FREELOCAL in macro DSN6SPRM – Indicates whether DB2® can free cached dynamic statements to relieve DBM1 below-the-bar storage.
- COMPRESS_SPT01 in macro DSN6SPRM – specifies whether the SPT01 directory table space is to be compressed. .
- DISABSCL in macro DSN6SPRM - are SQLWARN1 and SQLWARN5 set for non-scrollable cursors on OPEN and ALLOCATE CURSOR.
- EN_PJSJ in macro DSN6SPRM – specifies whether to enable dynamic index ANDing,
- HONOR_KEEPDICTIONARY in macro DSN6SPRM – should KEEPDICTIONALY be honored for LOAD and REORG when specified.
- IMPDSSIZE in macro DSN6SYSP – specifies the maximum data set size (DSSIZE) in gigabytes that DB2 should use for creating an implicit base table space.
- IMPTKMOD in macro DSN6SYSP – specifies whether DB2 is to track modifications to the pages of implicitly created table spaces.
- INDEX_IO_PARALLELISM in macro DSN6SPRM – is I/O parallelism enabled for index INSRTs.
- INLISTP in macro DSN6SPRM – max elements in an IN-list to aid with predicate optimization
- MAX_CONCURRENT_PKG_OPS in macro DSN6SPRM – the maximum number of automatic bind requests that can be processed simultaneously.
- NPGTHRSH in macro DSN6SPRM – use special access path selection for tables under a given size.
- OJPERFEH in macro DSN6SPRM – disable performance enhancements for outer join operations.
- OPTIOWGT in macro DSN6SPRM – controls how DB2 balances the I/O cost and CPU estimates when selecting access paths.
- PTASKROL in macro DSN6SYSP – roll up accounting trace records from a parallel query task into the originating task’s accounting trace.
- RETVLCFK in macro DSN6SPRM – retrieved VARCHAR column from a padded index.
- SECADM1_INPUT_STYLE in macro DSN6SPRM – is SECADM1 setting passed as a hexadecimal string or as a standard character string.
- SECADM2_INPUT_STYLE in macro DSN6SPRM – is SECADM1 setting passed as a hexadecimal string or as a standard character string.
- SEQCACH in macro DSN6SPRM – is sequential mode used to read cached data from a 3990 controller
- SEQPRES in macro DSN6SPRM – controls whether data should remain in cache longer
- SJTABLES in macro DSN6SPRM – enable star join processing
- SMF89 in macro DSN6SYSP – if yes, DB2 does detailed measured usage tracking if SMF type 89 records are activated
- SMSDCFL in macro DSN6SPRM – DFSMS data class for table spaces
- SMSDCIX in macro DSN6SPRM – DFSMS data class for indexes
- STATCLUS in macro DSN6SPRM – type of clustering statistics that are to be collected by the RUNSTATS
- UNION_COLNAME_7 in macro DSN6SPRM – result column name behavior in UNION queries
- ZOSMETRICS in macro DSN6SPRM – enables DB2 to gather z/OS metrics
Of course, as new opaque DSNZPARM keywords are introduced they may or may not end up in this section of the documentation. I’m not privy to why these keywords were chosen for inclusion. What I do know, it’s a great step in the right direction, the direction to help make modify DB2 just a little bit easier.
….While writing today’s blog entry, I was listening to The Beatles‘ 1965 release of “Rubber Soul”. I’d like ot say this one of my favorate Beatles alblums. However, they all are… I can say Rubber Soul does bring back a lot of memories. We played half the songs on this album.