Reviewing the DB2 10 Data Warehouse DSNZPARM for z/OS
The previous two weeks’ Part 1 and Part 2 discussions highlighted and discussed the various DB2 Data Warehouse DSNZPARMS for z/OS within Versions 8 and 9. With the new DB2 Temporal Tables the system needs all the DB2 Data Warehouse DSNZPARMS to be enabled and all the other new and improved DB2 10 DSNZPARMS for maximizing performance. To ensure the new DB2 DSNZPARMS are enabled, below is a listing of the Depreciated, Improve or new DB2 DSNZPARMS to make sure your data warehouse and other applications get the best performance available within your DB2 10 environment.
Since the storage model of the new DB2 10 system has moved much of the processing components above the 2GB memory line, most of these new and improved DB2 DSNZPARMS deal with new maximum memory settings. If you are monitor system paging, running on hardware that has enough memory or running on one of the new 196 hardware platforms, leverage the new DB2 memory capabilities and the new hardware as soon as possible.
Remember even with a number of the DB2 10 components moving above the 2GB memory bar, there are still many components (sometimes as much as 25%) below the bar. Monitor your system paging and the size of DB2 memory footprint and adjust your settings incrementally and carefully.
Remember that monitoring, analyzing, improving and repeating in small increments is the best way to provide the best DB2 Data Warehouse DSNZPARMS and a stable high performance environment.
Please note: As this table does not lend itself to a convenient website format, this post is being made available as a PDF file. Click here to access the DB2 10 DSNZPARMS chart.
| DSNZPARM | Status/ Suggested Setting | Suggested Setting | IBM Documentation Comments | zParm Macro |
| RETVLCFK | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Specifies whether the VARCHAR column is to be retrieved from a padded index | DSN6SPRM |
| DISABSCL | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Specifies whether SQLWARN1 and SQLWARN5 are set from non-scrollable cursors on OPEN and ALLOCATE CURSOR | DSN6SPRM |
| OJPERFEH | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Specifies whether to disable performance enhancements for outer join operations | DSN6SPRM |
| OPTIOWGT | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Controls how DB2 balances the I/O cost and CPU estimates when selecting access paths | DSN6SPRM |
| OPTIXIO | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Provides stable I/O costing with less sensitivity to buffer pool sizes NOTE: Hidden parameter | DSN6SPRM |
| PTCDIO | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Enables an optimizer enhancement to improve inefficient index access path for a single-table query NOTE: Hidden parameter | DSN6SPRM |
| SMSDCFL | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Specifies a DFSMS data class for table spaces | DSN6SPRM |
| SMSDCIX | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Specifies a DFSMS data class for indexes | DSN6SPRM |
| STATCLUS | Depreciated zParms | These depreciated zparms are no longer relevant within DB2 10. | Specifies the type of clustering statistics to be collected by the RUNSTATS utility | DSN6SPRM |
| SEQCACH | from BYPASS to SQE | SQE – This helps DB2 and especially data warehouse leverage all the hardware technology available within the infrastructure. | Specifies whether to use the sequential mode to read cached data from a 3990 controller | DSN6SPRM |
| SEQPRES | from NO to YES | YES – This provides extra speed and buffer pool memory for DB2 utility processing. Monitor when your utilities run to make sure it is not stealing precious buffer pool memory from critical processes. | Specifies whether DB2 utilities that do a scan of a non-partitioning index followed by an update of a subset of the pages in the index allow data to remain in cache longer when reading data | DSN6SPRM |
| These DB2 10 DSNZPARMS have new maximums available for leveraging the new memory and more workload within DB2. | ||||
| CTHREAD | from 2000 to 20000 | 3000 – DB2 10 provides more memory for additional processes and most systems already have this adjusted. Bumping it up provides extra head room for threads and processing. | The maximum number of allied threads (threads started at the local subsystem) that can be allocated concurrently | DSN6SYSP |
| MAXDBAT | from 1999 to 19999 | 3000 – DB2 10’s new memory model provides the opportunity to increase the number of active connection. Increasing this to 2500 is a tiny step toward more workload. | The maximum number of database access threads (DBATs) that can be active concurrently | DSN6SYSP |
| IDFORE | from 2000 to 20000 | 3000 – Modest increase that matches the other thread-related DSNZPARMS. | The maximum number of allied threads (threads started at the local subsystem) that can be allocated concurrently. These are typically the number of TSO foreground connections to users. | DSN6SYSP |
| IDBACK | from 2000 to 20000 | 3000 – Modest increase that matches the other thread-related DSNZPARMS. | The maximum number of concurrent connections that are identified to DB2 from batch | DSN6SYSP |
| MAXOFILR | from 2000 to 20000 | 3000 – Modest increase that matches the other thread-related DSNZPARMS. | The maximum number of data sets that can be open concurrently for processing of LOB file references | DSN6SYSP |
| STATIME | from 5 to 1 | 5 – Prefer to limit the background statistics gathering. Use the VOLATILE table space setting for alerting the optimizer for access path considerations. | The number of minutes between gathering statistics | DSN6SYSP |
| DSMAX | from 10000 to 20000 | 12,500 – Be careful with this setting since the open data sets take additional memory and your system should be closing inactive data sets anyway | The maximum number of data sets that can be open at one time | DSN6SPRM |
| IRLMRWT | from 60 to 30 | 30 – Prefer to have the system abend locking applications more quickly, but can cause problems. Be careful with this parameter and monitor its effects on the number of timeouts in your environment. | The number of seconds before a timeout is detected | DSN6SPRM |
| CHKFREQ | from 500000 records to 5 minutes | System dependent – DB2 10 provides the ability to have both the number of log records and/or the amount of time to drive DB2 checkpoints. Let your DB2 recovery preparation drive this setting for your best disaster recovery scenario. | The number of minutes or log records between log checkpoints | DSN6SYSP |
| URLGWTH | from 0 to 10000 | 2000 – This may be a high record count. Adjust to your workloads so the DB2 console does not get overrun with messages. | The number of log records that are to be written by an uncommitted unit of recovery before DB2 issues a warning message to the console | DSN6SYSP |
| URCHKTH | from 0 to 5 | 0 or 1 – Any process that is uncommitted over a unit of recovery is potentially unrecoverable in a disaster recovery scenario. This messaging provides documentation for these processes. | The number of checkpoint cycles that are to complete before DB2 issues a warning message to the console and instrumentation for an uncommitted unit of recovery | DSN6SYSP |
| LRDRTHLD | from 0 to 10 | 0 or 1 – Processes that hold locks too long within your system should be redesigned for better application concurrency. Having this setting low provides messages to document the offending applications. | The number of minutes that a read claim can be held by an agent before DB2 writes a trace record to report it as a long-running reader | DSN6SPRM |
| CONSTOR | from NO to YES | No – Contracting a thread’s storage area is very expensive within the system. If the system has memory issues try changing this to Yes. | Specify whether DB2 is to periodically contract each thread’s working storage area | DSN6SPRM |
| MINSTOR | from YES to NO | No – Minimizing thread’s storage is very expensive within the system. If the system has memory issues try changing this to Yes, but be careful. | Specify whether DB2 is to use storage management algorithms that minimize the amount of working storage consumed by individual threads | DSN6SPRM |
| IRLMSWT | from 300 to 120 | 30 – Prefer to have the system fail quicker if the IRLM is not available. | The number of seconds that DB2 waits for the IRLM to start during autostart | DSN6SPRM |
| MAXRBLK | from 8000 to 400000 | Increase by 1000, monitor and repeat the 1000 increase. The RID pool provides DB2 with a vital area that is shared across the system. Be careful Increasing this memory storage block, 25% is grabbed below the 2GB bar and needs to be monitored for system paging. RID pool failures cause table space scans that should be avoided. Increase this setting as high as possible for the best performance. | KB of storage needed for the RID pool | DSN6SPRM |
| SRTPOOL | from 2000 to 10000 | Increase by 1000, monitor and repeat the 1000 increase. The Sort pool is also a shared resource across the system. When increasing this memory storage block, 25% is grabbed below the 2GB bar and needs to be monitored for system paging. | KB of storage needed for the SORT pool | DSN6SPRM |
| EN_PJSJ | from OFF to ON | ON – This will help Star Join SQL activities within the data warehouse. (See the DSNZPARM PART1 Blog entry for further details.) | Specifies whether dynamic index ANDing for star join, also known as “pair-wise join,” can be enabled when star join processing is enabled | DSN6SPRM |
| UTSORTAL | from NO to YES | Yes, if possible. Coordinate this with your usage and settings for Real-Time Statistics. Make sure to turn this on in a controlled and monitored environment. | Specifies whether DB2 uses real-time statistics to determine the sort work data set sizes if real-time statistics data is available | DSN6SPRM |
| SPRMPCWH | from 5 to 1 | 1 – Hash anchors provide a way to track Latch contention within your buffer pools. In most instances 1 is quite enough. In rare high performance cases with extremely large buffer pools more Hash anchors may be necessary. Monitor this closely when changes are made. | Specifies the number of buffers per hash anchor (if positive) or the number of hash anchors per buffer (if negative). NOTE: Hidden parameter | DSN6SPRM |
| PCLOSEN | from 5 to 10 | 5 – Opening and closing data sets is very performance expensive within your environment. Minimize the thrashing of data sets and monitor the impact of any changes. | Specifies the number of consecutive DB2 checkpoints since a set or partition was last updated, after which DB2 converts the set or partition from read-write to read-only | DSN6SYSP |
| RRULOCK | from NO to YES | No – This parameter can greatly affect application deadlocks within your environment. Make sure to monitor deadlocking if this is changed. | Specifies whether to use the U (UPDATE) lock when using repeatable read (RR) or read stability (RS) isolation to access a table | DSN6SPRM |
| NUMLKTS | from 1000 to 2000 | Per system activity, increase this if possible. Lock escalation is an issue with some LOB tables and increasing this parameter value can help avoid lock escalation. Monitor appropriately. | Specifies the default value for the maximum number of page, row, or LOB locks that a single application can hold. Escalation occurs simultaneously in a single table or table space before lock | DSN6SPRM |
| EDMDBDC | from 11700 to 23400 | Increase. Within DB2 10 most of the EDM Pool storage has been moved above the 2GB bar. Increasing the EDM Pool DBD Cache provides more capacity for more and larger DBDs within your active systems. Monitor its effect on the storage size of the overall DB2 system. | Specifies the minimum size (in KB) of the DBD cache that can be used by the EDM | DSN6SPRM |
| EDM_ SKELETON_POOL | from 5120 10240 to | Increase. Again within DB2 10 most of the EDM Pool storage has been moved above the 2GB bar. Increasing the EDM Pool Skeleton Pool provides more capacity for more SQL within your active systems. Monitor its effect on the storage size of the overall DB and system paging | Specifies the minimum size (in KB) of the EDM skeleton pool | DSN6SPRM |
| EDMSTMTC | from 56693 to 113386 | Increase by 5000, monitor and repeat. Again within DB2 10 most of the EDM Pool storage has been moved above the 2GB bar. Increasing the EDM Pool Statement Cache provides more capacity for SQL caching reuse. Monitor its effect on the storage size of the overall DB. | Specifies the size (in KB) of the statement cache that can be used by the EDM | DSN6SPRM |
| STATROLL | from NO to YES | No – This parameter may affect access paths and changing it to Yes may be appropriate for tables with empty partitions. Change to Yes may provide better access paths in some situations. Monitor your access paths when it is being changed. | Specifies whether the RUNSTATS utility aggregates the partition-level statistics, even though some parts may not contain data | DSN6SPRM |
| BP8K0 | from 1000 to 2000 | 2000 – Change this parameter to the maximum when it is being used within your system. | Specifies the initial installation size in pages of the BP8K0 buffer pool | |
| NUMCONDB | from 100 to 200 | 150 – Provides space for additional databases and their workloads with the new DB2 10 system. | The estimated number of concurrently-open databases | |
Leave a Reply