Performance features in DB2 V9 for z/OS – Part 10

As I talked about in the previous weeks, DB2 Version 9 for z/OS has over 50+ great new performance features. Many of these features can really benefit your standard DBA operations; improve application performance and overall availability of your systems and data.

There are many new enhancements with DB2 9 for z/OS that are especially geared to data warehousing (DW) and business intelligence (BI) applications.  These DW and BI improvements can leverage the previous enhancement ORDER BY and FETCH FIRST n ROWS within a sub-select, the DB2 Materialized Query Tables (MQTs), and the many OLAP functions that have also come along in Version 9.

All of these enhancements are great and help tremendously with DW and BI applications.  The other important item is controlling these features through the correct zParm settings.  I recently came across these settings and recommendations in the Enterprise Data Warehousing with DB2 9 for z/OS, SG24-7637 Redbook and thought you might be interested.

Check out your applications and see where these zParms can help your performance with DB2 Version 9 for z/OS.  I think these zParms can make a significant difference in your DW BI workload performance.  Remember everyone’s systems are different and these are only recommendations.  Please be careful and analyze your system conditions before making any changes.



Recommended
Parameter
Benchmark
Parameter
Description
CDSSRDEF=ANY1 Bind Option alsoParallelism degree – ANY=parallelism, 1= No parallelism
CONTSTOR=NONOConstraints storage constraints in DBM1 address space, specify YES. See also: MINSTOR
DSVCI=YESYESThe DB2-managed data set has a VSAM control interval that corresponds to the buffer pool. YES is the default and the preferred setting.
MGEXTSZ=YESYESSliding scale for secondary extent allocations for DB2-managed data sets are to be sized
MINSTOR=NOYESBest performance, specify NO for this parameter. To resolve storage constraints in DBM1 address space, specify YES. See also: CONTSTOR
OPTIXIO=ONONOPTIXIO=ON: Provides stable I/O costing with significantly less sensitivity to buffer pool sizes.
PARAMDEG=X0 – lets DB2 adjust#Processors <= X <= 2*#Processors. If concurrency level is low, the ratio can be higher.
SRTPOOL=800051,200,000The Sort Pool size
STARJOIN=DISABLESTARJOIN and the SJTABLES parameter define the join processing of a query. Watch your access path changes when using this parameter
MXQBCE1023The maximum number of access combinations considered by the optimizer when joining N tables.  The MXQBCE default is 1023 in DB2 9

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>