Blog
DB2utor

Categories

February 04, 2008

DB2 9 System Parameter Changes for Systems Programmers

This is the final installment of a three-part series. Initially I presented a fast way of finding the different DB2 system parameter changes. Last week I went into detail on parameters that are more application development-related. Here I cover changes that have an impact on the DB2 subsystem in which your systems programmers maintain.

DSN910.SDSNMAC(DSN6LOGP)
The DSN6LOGP macro contains the log initialization parameters used by the LOG MANAGER.

* MAXARCH
Default change: From 1,000 in Version 8 to 10,000 in Version 9.

Defined: MAXARCH is a decimal number between 10 and 10,000. It's used to specify the maximum number of archive log volumes to remember in the bootstrap data set (BSDS). For the dual archive option, the number is applied to each copy of the log. If the limit is set at 500, then 500 copy 1 and 500 copy2 volumes are recorded in the BSDS.

Impact: When the active log becomes full, the log manager switches to a new active log and copies the full active log off to an archive log. The START-RBA and END-RBA (LRSN in data sharing) and data set names are copied to the BSDS. If you stay with the default, you're now storing 10,000 entries in the BSDS instead of 1,000. The BSDS is used by recovery to determine for a given RBA what archive log to read. It's highly unlikely that you'd have the archive logs going back 10,000 entries, so you'll want to figure out what your recovery requirements are and keep only enough information recorded in the BSDS to support that requirement.

DSN910.SDSNMACS(DSN6SPRM)
The DSN6PRM macro contains the initialization parameters used by the DBM1 address space.

* ADMTPROC (new)
Valid value: a procname. The default procname is DSNADMT.

Defined: This is the JCL procedure name used to start the DB2 Admin Scheduler Task Address Space. If left blank, then no address space is started.

* CACHEDYN_FREELOCAL 
Default changed: From 0 in Version 8 to 1 in Version 9. Valid setting is a value between 0 and 3.

Defined: CACHEDYN_FREELOCAL specifies whether DB2 can free local cached statements to relieve storage constraints below the 2 GB bar. The setting is effective only for plans and packages bound as KEEPDYNAMIC(YES).

Valid settings are: 
0: DB2 cannot free local cached statements to relieve storage constraints below the bar. 
1: DB2 can free local cached statements to relieve storage constraints  below the bar. This is the default setting.
2: Serviceability
3: Serviceability

* COMCRIT (no longer supported)
Added to DB2 version 8 with APAR PK08344. Valid setting is NO (default) or YES. I've not found an APAR that adds this to DB2 9.

Defined: COMCRIT is used to enable Common Criteria, an international standard that helps ensure the security of computer systems in a network environment.

* EDMPOOL_ABOVE_2GB (no longer supported)
I couldn't find documentation on EDMPOOL_ABOVE_2GB.

* EDM_SKELETON_POOL (new)
Valid values: Between 5120 and 2097152. The default is 102400.

Defined: This pool space is above the 2-GB bar and is used to store skeleton copies of plans and packages (SKCT/SKPT). The value is specified in kilobytes. See the installation guide for the formula used to calculate this value.

* HONOR_KEEPDICTIONARY (new)
Valid settings: NO (default) and YES.

Defined: During the migration of DB2 version 8 to version 9.1, by default DB2 ignores the LOAD and REORG parameter KEEPDICTIONARY when the tables are converted from basic row to reorder row format. Over time when you know you have all of your table spaces converted, you may want to change this default to YES to avoid having to rebuild the dictionary.

* MAX_CONCURRENT_PKG_OPS (new)
Valid value: Between 1 and 20. The default is 10.

Defined: MAX_CONCURRENT_PKG_OPS specifies the maximum number of auto rebind requests that can be processed concurrently.

* MAX_OPT_ELAP (no longer supported)

* MAX_OPT_STOR 
Default changed: From 20 in Version 8 to 40 in Version 9. Valid value between 0 and 100 MB.

Defined: MAX_OPT_STOR specifies the maximum amount of storage to be consumed by the optimizer.

* MAXTEMPS (new)
Valid value: Between 0 (default) and 2147483647.

Defined: MAXTEMPS specifies the maximum number of megabytes of temp storage in the work file database that can be used by a single agent at any given time for all temporary tables. An entry of 0 means that no limit is enforced.

* MINSTOR
Default changed: From NO in Version 8 to YES in Version 9.

Defined: When MINSTOR is set to YES, DB2 actively manages storage for individual threads with the goal of minimizing the total storage used by the thread. If you don't have a storage problem, you may want to change this to NO to save some CPU cycles.

* MXQBCE
Default changed: From 32767 in Version 8 to 1023 in Version 9.

Defined: MXQBCE specifies the maximum number of join combinations the optimizer considers for a x-table join. For a 10-table join, the max is 1023 (2^10 – 1). This is used in conjunction with TABLES_JOINED_THRESHOLD. When TABLES_JOINED_THRESHOLD is set to 10, set MXQBCE to 1023. If it's set to 11, then set MXQBCE to 2047. If it's 12, MXQBCE is 4095.

* MXDTCACH (new)
Valid values: Between 0 and 512. The default is 20.

Defined: MXDTCACH specifies the maximum size, in MB, of memory for data caching. If you specify 0, DB2 doesn't use caching and applies sparse index only. Otherwise, for values between 1 and 512, DB2 allocates memory above the 2GB bar. The recommended value is 128 for SAP and PeopleSoft installations.

* OPTCCOS4 (no longer supported)
OPTCC0S4 was added in V8 to enable PK26760 Optimizer enhancements.

* OPTXQB (new parameter)
Valid setting: ON (default) or OFF. I cannot find any documentation that tells me what OPTXQB is.

* OPTXOIRC (no longer supported)
OPTXOIRC was added in V8 to enable PK30857 Optimizer enhancements.

* PARAPAR1 (no longer supported)
PARAPAR1 was added in V8 to enable PQ87352 Optimizer enhancements.

* PLANMGMT (new parameter)
Valid settings: OFF (default), BASIC and EXTENDED.

Defined: PLANMGMT impacts the behavior of REBIND PACKAGE and REBIND TRIGGER PACKAGE. By leaving the default value of OFF you are not getting any of the new features in plan management. With BASIC a new version of the package is created and a previous version is saved. With EXTENDED you not only get the current version and previous version but you also have the original version of the package.

* RELCURHL (no longer supported)

Defined: RELCURHL provides the option to hold a lock after commit is eliminated. DB2 always releases the page or row lock for cursor hold at commit, which allows for greater concurrency.

* REOPTEXT (no longer supported)

Defined: The DB2 Version 9.1 documentation and installation process currently refers to an unused subsystem parameter called REOPTEXT (APAR PK46054). This parameter was originally intended to activate selective reoptimization of dynamic SQL queries with predicates that contain parameter markers. Subsequent design changes eliminated the need for REOPTEXT prior to DB2 Version 9.1 general availability, but it was never removed.

* RESTORE_RECOVER_FROMDUMP (new)
Valid settings: NO (default) or YES.

Defined: RESTORE_RECOVER_FROMDUMP specifies for the RESTORE SYSTEM and RECOVER utilities whether the system-level backup selected as the recovery base should be used from the disk copy of the system-level backup (NO) or the dump on tape (YES).

* RESTORE_TAPEUNITS (new)
Valid settings: NOLIMIT (default), 0 through 255.

Defined: RESTORE_TAPEUNITS specifies the maximum number of tape units or tape drives that the RESTORE SYSTEM utility can allocate when restoring a system-level backup that's been dumped to tape. With the default of NOLIMIT, the RESTORE SYSTEM utility allocates as many tape units as necessary to restore the system-level backup. This setting can be overridden with the TAPEUNITS keyword on the RESTORE SYSTEM utility statement when it's executed.

* RELCURHL (no longer supported)

* RPITWC (new)
Valid settings: NO (default) or YES.

I could not find any documentation on this parameter.

* SJMXPOOL (no longer supported)

* SUPPRESS_TS_CONV_WARNING (no longer supported)
Defined: DB2 9.1 always operates as if SUPPRESS_TS_CONV_WARNING=NO. DB2 9.1 reports SQLCODE +20272 for all cases where DB2 converts a table space from index-controlled to table-controlled partitioning.

* STATCLUS (new)
Valid settings: STANDARD or ENHANCED (default).

Defined: The ENHANCED clustering statistics result in better SQL access paths when there are duplicate values of the clustering index or when the data is in reverse clustering order. These enhanced clustering statistics may cause many access paths to change. The STANDARD option results in the same clustering statistics as Version 8.

SYSTEM_LEVEL_BACKUPS (new)
Valid settings: NO (default) or YES.

Defined: SYSTEM_LEVEL_BACKUPS specifies the default of NO if you don't take system-level backups with the BACKUP SYSTEM utility. Specify YES if you take system-level backups with the BACKUP SYSTEM utility.

* TABLES_JOINED_THRESHOLD (no longer supported)
Defined: This value was the minimum number of table joins in a query to cause DB2 to monitor the resources consumed when determining the optimum access path for that query.

* UTILS_DUMP_CLASS_NAME (new)
Valid settings: blank (default) or SMS dump class name.

Defined: UTILS_DUMP_CLASS_NAME specifies the name of the DFSMShsm dump class that the RESTORE SYSTEM utility uses to restore from a system-level backup that's been dumped to tape. This is also the dump class that the RECOVER utility ues to restore objects from a system-level backup that's been dumped to tape. The setting is applicable only when you specify RESTORE_RECOVER_FROMDUMP=YES. The argument must be blank or a valid SMS dump name.

DSN910.SDSNMACS(DSN6SYSP)
The DSN6SYSP macro contains general system parameters.

* STORPROCj (no longer supported)

* DBPROTCL (no longer supported)
Defined: The default for the DBPROTOCOL bind option is DRDA. You can still specify DBPROTOCOL(PRIVATE) explicitly when binding packages and plans, but DB2 issues a warning message if you perform any bind with this value.

* MGEXTSZ 
Default changed: From NO in Version 8 to YES in Version 9. Valid settings are YES or NO.

Defined: With the default value of YES, DB2 automatically optimizes the secondary extent allocations of data sets for table and index spaces that have a SECQTY value of greater than 0. When all secondary extents are exhausted for the first data set of a nonpartitioned table space or a nonpartitioned index space that has a SECQTY value of greater than 0, the primary space allocation of each subsequent data set is the larger of the SECQTY setting and the value that's derived from the sliding scale algorithm.

With a MGEXTSZ set to NO, you must manage the secondary extent allocations manually. For nonpartitioned table and index spaces, when all extents of the first data set are exhausted, the primary space allocation of each subsequent data set is always the PRIQTY setting. When the sliding scale is used, secondary extent allocations that are allocated earlier are smaller than those allocated later, until a maximum allocation is reached. 

The maximum allocation is 127 cylinders for data sets with a maximum size of 16 GB or less, and 559 cylinders for data sets with a maximum size of 32 GB or 64 GB. Secondary extent allocations for data sets in implicitly-created table spaces are not influenced by this setting because they always use the sliding scale.

* MAXOFILR (new)
Valid values: 0 to n, where N < CTHREAD value. The default is 100.

Defined: DB2 9 introduces MAXOFILR (MAX OPEN FILE REFS) to control the maximum number of data sets that can be open concurrently for processing of LOB file reference. The highest number for MAXOFILR is effectively limited to the setting of the CTHREAD (MAX USERS) parameter. This limitation exists because a thread can hold at most one file open using file references. With z/OS 1.7, DSMAX can be 64K and even get up to 100K with APAR PK13287 and PK29281.

* TBSBP8K (new)
Valid values: 8K BPs (BP8K0 – BP8K9). The default is BP8K0.

Defined: TBSBP8K is the name of the bufferpool to be used by 8KB page size table spaces that are implicitly created.

* TBSBP16K (new)
Valid values: 16K BPs (BP16K0 – BP16K9). The default is BP16K0.

Defined: TBSBP16K is the name of the bufferpool to be used by 16KB page size table spaces that are implicitly created.

* TBSBP32K (new)
Valid values: 32K BPs (BP32K and BP32K1 – BP32K9). The default is  BP32K.

Defined: TBSBP32K is the name of the bufferpool to be used by 32KB page size table spaces that are implicitly created.

* IMPDSDEF (new)
Valid settings: YES (default) or NO.

Defined: IMPDSDEF specifies whether DB2 should define the underlying data set when creating an implicit base table space or implicit index space for the enforcing primary key index, enforcing unique key index or ROWID index for a ROWID column defined as GENERATED BY DEFAULT. This isn't used for implicitly created LOB or XML table spaces, auxiliary indexes or DOCID indexes. 

YES means that the data set is defined when the table space or index space is implicitly defined. NO means that the data set isn't defined until data is inserted into the base table.

* IMPTSCMP (new)
Valid settings: YES (default) or NO.

Defined: IMPTSCMP specifies whether DB2 should enable data compression when creating an implicit base table space. It applies only to implicitly created base table spaces; it's not used for implicitly created LOB or XML table spaces.

NO means that data compression isn't activated when a table space is implicitly defined. YES means that data compression is activated when a table space is implicitly defined.

* XMLVALA 
Default changed: From 2097152 in Version 8 to 204800 in Version 9. Valid range 1-2097152. Available with DB2 version 8 through PK25747. 

Defined: Specifies an upper limit for the amount of storage, in kilobytes, that each user can have for storing XML values.

* XMLVALS
Default changed: From 2048 in Version 8 to 10240 in Version 9. Valid range 1-51200. Available with DB2 version 8 through PK25747.

Defined: XMLVALS specifies an upper limit for the amount of storage, in megabytes, that the system can have for storing XML values.

* TBSBPLOB (new)
Valid values: 4K BPs (BP0-BP49), 8K BPs (BP8K0-BP8K9), 16K BPS (BP16K0-BP16K9), 32K BPs (BP32K,BP32K1-BP32K9). Default is BP0.

Defined: TBSBPLOB is the name of the bufferpool to be used by LOB table spaces that are implicitly created.

* TBSBPXML (new)
Valid values: 16K BPS (BP16K0-BP16K9). The default is BP16K0.

Defined: TBSBPXML is the name of the bufferpool to be used by XML table spaces that are implicitly created.

A Parameter Primer
As I've said, system parameters change as maintenance is put on the system. So this is probably an incomplete list but I’m sure it is enough to give you a good understanding as to what the improvements are in DB2 9.

As usual I really appreciate your feedback. If you find this type of information helpful, please send me a note or post a comment. If you have a topic you'd like me to cover, post a comment.