Reviewing the DB2 Data Warehouse DSNZPARM for z/OS
Last week we talked about the first alphabetic group of DB2 data warehouse DSNZPARMS that can improve your access paths and overall application performance. This week the second set of DSNZPARMS are discussed. Many of the data warehouse DSNZPARMS discussed are somewhat hidden within the regular DSNZPARM install panels. All of these DSNZPARMS discussed are available in DB2 for z/OS DB2 Version 9. Some are available in DB2 Version 8.
Caution needs to be taken with all system settings and especially these data warehouse DSNZPARMS. These DSNZPARMS are meant to change access paths and improve them, but each data warehouse design is unique along with each application access path, so results will vary. If the data warehouse DB2 subsystem is shared with other OLTP or operational applications, I highly recommend fully documenting and setting up a full PLAN STABLITIY plan and package management structure for your current access paths before changing any DSNZPARMS. This documentation along with a good PLAN STABILITY DB2 plan and package management implementation and back out practices helps your environment quickly react and back out any detrimental access paths encountered through unexpected rebind of any program.
Some of the comments from last week’s blog highlighted the resurgent of data warehousing on the z/OS platform and why running a data warehouse on z/OS provides many advantages over other platforms. One that was noted from several people is when your data warehouse runs on z/OS, the huge ETL processes usually don’t have to transmit the data over a network. Even though the network bandwidth is robust, avoiding this extra bottleneck can sometimes save hours of extra overhead, guaranteeing that your refresh data jobs have enough time every day to provide critical refreshes of you data within your data warehouse. Additionally most of your source data warehouse data comes from the z/OS operational systems and can quickly be put into operational business intelligence data warehouses. This fresh data increases sales, provides real time inventory or product availability updates and, the most important factor, removes latency for all your critical single point master data source of record for the enterprise.
Improve your system and application performance by adjusting these data warehouse DSNZPARMS to improve your access paths and by using the superior DB2 optimizer technology and most efficient performance available.
|zParm||Suggested Setting||Description||DSN6SPRM Macro|
|OPTIXOPREF||ON – The data warehouse application reports want to favor index only access whenever possible||After turning this setting on, the optimizer will favor index only access. When this is turned on the optimizer may choose a single column index over an index that has all the columns in your query. This is not always a good application improvement because sometimes the multiple column index mayhave fewer levels or help with ORDER BY or GROUP BY processing. Be careful with this setting especially when indexes might be used with sorting.||SPRMIXOP|
|OPTJBPR||ON – This parameter helps with JOIN activities and typically data warehouse reports do a lot of JOINs within their SQL.||This DSNZPARM enables the optimizer to consider join predicate filtering when estimating the I/O cost within buffer pool caching. This parameter should be used when your warehouse has a minimum of data skew. If not, it can cause problems with tables that have a lot of data skew as the formulas may overestimate the costs of join through the columns referenced. This cost over estimation can come through the data skew through either table involved in the join process and local predicates.||SPRMJBPR|
|OPTOFNRE||ENABLE – Most data warehouse application reports execute extensive joins and need all the optimization possible.||This DSNZPARM can impact applications that utilize an OPTIMIZE FOR n ROWS or FETCH FIRST n ROWS ONLY clause which are sometimes used within data warehouse operation business intelligence applications. DB2 can sometimes under estimate the cost of the join sequence between an inner table join index and the outer composite table index. DB2 optimizer join algorithms are very complex and join costing involves calculating complex possibilities. Optimizing for a particular amount of rows in the OPTIMIZE FOR n ROWS clause is further emphasized through enabling this DSNZPARM.||SPRMOFNR|
|OPTXQB||ON – The SQL access paths need to be optimized as much as possible.||
This DSNZPARM helps with global query block optimization for SQL with sub-queries that use an IN list with decimal data. This situation sometimes occurs within data warehouse applications.
|PARAMDEG||30 – Parallelism needs to be turned on and fully tested.||This DSNZPARM specifies the maximum degree of parallelism for a parallel group. 30 is a good place to start for improving performance for a complete database design that has a partition per day with parallel processes.||SPRMMDEG|
|PREDPRUNE||YES – The SQL within the data warehouse application uses a lot of always true variables for codes, etc.||This DSNZPARM improves the DB2 optimizer’s ability to prune predicates that are always true or false. By being able to easily understand these types of predicates it is able to prune them and consider all the complex SQL access paths available for improved performance. Turning on this DSNZPARM helps data warehouse queries that use codes and other types of standard values.||SPRMPPRU|
|PTCDIO||OFF – Make sure to turn this one off.||There are several old PTFs associated with this DSNZPARM.PQ97866 and PQ86763 which were introduced several years ago to fix an access path problem. Make sure to have this DSNZPARM set to OFF.||SPRMCDIO|
|SJMISSKY||ON – The amount of JOINS within the application justifies this being turned on.||By setting this DSNZPARM to ON, DB2 may choose a more efficient access path for star join queries during the star join outside-in phase when it involves missing keys or when a dimension table is not highly normalized.||SPRMSJMK|
|SJTABLES||1 – Setting this to 1 lets the optimizer consider Star Joins for all types of SQL.||This DSNZPARM specifies whether the star join processing is enabled or not. This also tells DB2 the ratio between the fact/dimension ratio that will be used between the largest table and the dimension tables.||SPRMSJTB|
|STATCLUS||ENHACED – Leave current setting||STATCLUS specifies the type of clustering statistics that are to be collected by the RUNSTATS utility.||DSNTIP6|