Reviewing the DB2 Data Warehouse DSNZPARM for z/OS
In the previous weeks I briefly talked about the new DB2 10 temporal tables and how they are great for data warehousing applications. To leverage the temporal tables and data warehousing applications within the DB2 for z/OS environment the system needs the proper DSNZPARM settings. Since everyone has too many systems, some organizations only review system DSNZPARMs during migrations, meaning that many of the new settings might not be set up or enabled. It’s a good idea to review the DSNZPARMs on a regular basis and get them set up to maximize the performance of your data warehousing application SQL by leveraging all the DB2 optimizer capabilities.
First, get a listing of the current DSNZPARMs for your data warehouse system’s settings. This can be done a number of different ways: through your performance monitor, through the JCL that creates the DSNZPARMs or through the IBM REXX exec of the DB2 stored procedure DSNWZP. This DSNWZP stored procedure routine is one of the many DB2 administration procedures and is set up during the system install.
The data warehouse DSNZPARMs affect the performance of the DB2 system and its applications in many different ways, from the DB2 optimizer’s access path choices to the number of parallel processes DB2 will perform during data retrieval. To make sure that all DB2’s performance features and functions are available to improve performance the following is Part 1 of an alphabetic list of DB2 data warehouse DSNZPARMs. Making sure these data warehouse DSNZPARMs are enabled and adjusted for your system will help your application fully exploit DB2 and get the best performance possible for your data warehouse.
|zParm||Suggested Setting||Description||DSN6SPRM Macro|
|EN_PJSJ||ON – This will help Star Join SQL activities within the data warehouse.||This parameter specifies whether to enable dynamic index ANDing which is also called pair-wise join processing. This DSNZPARM needs to be enabled along with the star join DSNZPARM on DB2. When both are enabled, the pair-wise join processing can improve the performance of data warehousing type queries. For example, performance will improve when the fact table has indexes that provide matching join predicates with some dimension tables having local filtering. The indexes on the fact table can be single-key indexes. No multi-column index on the fact table is suitable for the existing star join method. Enabling EN_PJSJ provides more flexibility with index design on star schema fact tables when multi-column index design makes it difficult to effectively exploit the star join access method.||SPRMPJSJ|
|INLISTP||50 or 100 – Bigger the better provides for long IN-LIST queries to run effectively.||Subsystem DSNZPARM parameter INLISTP allows you to specify the maximum number of elements in an IN-list for your SQL. This DSNZPARM should be adjusted to handle your application requirements. The default value for INLISTP is 50.||DSN6SPRM|
|MAX_OPT_STOR||80 – This provides more memory for the DB2 optimizer. It also gives you some more room for growth if necessary.||MAX_OPT_STOR specifies the maximum amount of MB storage to be consumed by the optimizer. More storage, up to 100 MB, is better to allow the optimizer to consider more efficient access paths.||SPRMMXOS|
|MAXRBLK||Depends on your storage constraint but make it as big as possible.||This setting is for the amount of storage allocated for the RID pool. The RID processing within data warehouse projects is usually very big and a larger setting helps performance and prevents RID pool failures.
*NOTE* Twenty-five percent of this storage pool is located below the 2-GB bar, so be careful with memory constrained systems.
|MAXTEMPS||Depends on your storage availability, the size of your data warehouse questions and the answer size. Make it big enough to handle sorting your SQL result set answers. No limit can be dangerous as one user can allocate it all.||MAXTEMPS sets the maximum number of megabytes of temp storage in the work file database for all temporary tables in a process. An entry of 0 means that no limit is enforced. You can enter the value in gigabytes by specifying a suffix of G, for example 4G.||SPRMWFAL|
|MXDTCACH||512 – Since most data warehouses use a lot of sparse indexes for their SQL, maximize the data caching with the maximum of 512 if possible.||MXDTCACH is the amount of storage that DB2 makes available for leveraging sparse indexes. Sparse indexes can be very helpful for improving any type of query performance, especially complex star joins within a data warehouse application. DB2 allocates this data caching memory above the 2 GB bar to the specified size. Watch your system paging if this or other parameters increase DB2’s memory utilization.||SPRMMXDC|
|MXQBCE||4095 – The number of JOINs within data warehouse queries warrants that this parameter should be expanded beyond the default.||MXQBCE specifies the maximum number of join combinations the optimizer considers for the data warehouse application SQL-table joins. This DSNZPARM is used in conjunction with the TABLES_JOINED_THRESHOLD parameter and should be set accordingly. When TABLES_JOINED_THRESHOLD is set to 10, set MXQBCE to 1023. For a 10-table join, the max is 1023 (2^10 – 1). If it’s set to 11, then set MXQBCE to 2047. If it’s 12, MXQBCE is 4095 and so on. This should be set big enough for the DB2 optimizer to have the best chance to choose the best access path for your complex data warehouse SQL.||SPRMMXCE|
|NPGTHRSH||10 – The parameter should be used to encourage matching index access in the complex application report SQL.||The value of NPGTHRSH is an integer that indicates the threshold for the number of pages in tables that influences the DB2 optimizer to choose matching index access. DB2 often scans a table space or non-matching index when the table is small, even though matching index access is possible. Be careful with setting this DSNZPARM too low since in some cases, matching index access can be more costly than a table space scan or non-matching index access. Specify a small value for NPGTHRSH (10 or less), which limits the number of tables for which DB2 favors matching index access. If you need to use matching index access only for specific tables, use the table VOLATILE parameter, rather than the system-wide NPGTHRSH parameter.||SPRMNPAG|
|OPTIOWGT||ENABLE – This parameter should be turned on for research into the access paths advantages especially for shops that have the new 196 CPU mainframe.||Subsystem parameter OPTIOWGT controls how DB2 balances the I/O cost and CPU estimates when the DB2 optimizer chooses the best access path. When this DSNZPARM is set to ENABLE, DB2 uses a new formula that balances the cost estimates of I/O response time and CPU usage. This new formula is especially important for the new 196 mainframe processors that have the new 5.2 GHz processors. This DSNZPARM can affect SQL access paths especially for complex data warehouse applications, so be careful if your DB2 system is shared with other OLTP applications.||SPRMIOWT|
|OPTIXIO||ON – Leave in ON for better I/O algorithms||The default of ON helps minimize the impacts of object and buffer pool sizes on the chosen access path. The Optimizer will default to the I/O costing algorithm which is less sensitive to object buffer pool sizes. By using the I/O costing model your access paths may change especially with smaller tables. For large data warehouses the DB2 optimizer should understand your application access path and the I/O costs. The ON setting provides a stable costing model regardless of the buffer pool sizes. Setting the DSNZPARM to OFF allows the optimizer to be heavily influenced by object size and buffer pool sizes.||SPRMIXIO|