IBM Systems Magazine, Mainframe Edition - January/February 2011 - 44

Tips
specif ic processing elig ible for System z* Integrated Information Processors (zIIPs); zIIP processing can reduce the number of billed MIPS a company uses. This CPU savings is more significant when using index compression. As in DB2 9, DB2 10 can direct up to 80 percent of CPU-intensive parallel-query processing to run on an available zIIP. DB2 10 makes more queries eligible for query parallelism, which can result in more zIIP exploitation. In DB2 10, portions of the RUNSTATS utility are eligible to run on a zIIP. XML schema validation and nonvalidation parsing of XML documents are eligible for zIIP or System z Application Assist Processing (zAAP). If XML parsing is done under DDF enclave threads, it’s eligible for zIIP. If the XML parsing is done under a batch utility, it’s eligible for zAAP. join predicates on materialization work files are also not indexable. In general, avoiding materialization is desirable. In DB2 10, materialization can be avoided in additional areas, particularly for view and table expressions involved in outer joins. The processing of Stage 1 and nonindex matching predicates has also been enhanced. DB2 now processes non-Boolean predicates more efficiently when accessing an index and Stage 1 data-access predicates. You don’t need to rebind your static applications to take advantage of some of these optimization improvements. However, a rebind is required to take full advantage. More complex queries with many predicates show higher improvement. Queries that scan large amounts of data also show higher CPU savings. DB2 10 also contains some SQL-sorting enhancements. DB2 10 introduces hash support for large sorts, which potentially reduces the number of merge passes needed to complete these sorts. Hashing can be used to identify duplicates on input or to sort, if functions such as DISTINCT or GROUP BY are specified. Some additional cases also exist where, when FETCH FIRST N ROWS is used, DB2 10 can avoid the sort process altogether. the highest key in the index–a new page-selection algor ithm helps minimize getpages, which can help reduce CPU cost. DB2 10 contains some referential integrity-checking improvements on inserts that may result in reduced CPU utilization and I/O processing.

Utilities and Large Objects
Generally speaking, DB2 utility CPU performance in DB2 10 is equivalent to that of DB2 9, but DB2 9 already introduced performance improvements of up to 50 percent CPU savings compared to DB2 8 for various utilities processing. DB2 10 also contains numerous large object (LOB) enhancements and one of them applies to Conversion Mode, namely LOB materialization avoidance. For LOBs, IBM has observed up to a 16-percent reduction in CPU consumption for DDF Inserts.

Query Performance
Range-list index scan is a new type of access path DB2 10 uses to significantly improve the performance of certain scrolling-type applications where the returned result set is only part of the complete result set. The alternative in DB2 9 was to use multiindex access (index ORing), which isn’t as efficient as single-index access. Prior to DB2 10, list prefetch couldn’t be used for matching IN-list access. In DB2 10, list prefetch can be used for IN-list table access, ACCESSTYPE=’IN’. The process of putting rows from a view or nested table expression into a work file for additional query processing is called physical materialization, which is an overhead. Additionally, it limits the number of join sequences that can be considered and can limit the administrator’s capability to apply predicates early in the processing sequence. The

Add Up the Savings
All of the CPU savings described here apply to Conversion Mode. Some of the performance improvements are available upon installation of DB2 10, without additional changes, while others require very minimal changes, such as a change to installation parameters. Some require a System z10 processor and perhaps changing the buffer-pool parameters. Most require appropriate rebinds to fully realize the benefits. Additional CPU benefits can be obtained when migrating to New Function Mode, which will be outlined in a future article.

Insert Performance
The performance of high concurrent inserts is better in DB2 10, with tests showing a typical range of 5- to 40percent reduction in CPU time when compared with DB2 9 performance. Higher CPU performance is achieved when processing sequential inserts. The amount of improvement also depends on the table-space type, with Universal Table Spaces seeing higher improvements. When a series of inserts are sequential w ith respect to the cluster key–but the key is less than

Jeff Berger has worked for IBM for 31 years and is an expert on hardware and software synergy between DB2 and IBM’s storage products.

44

JA N UA RY/ FE B RUA RY 2 011

ibmsystemsmag.com/mainframe



IBM Systems Magazine, Mainframe Edition - January/February 2011

Table of Contents for the Digital Edition of IBM Systems Magazine, Mainframe Edition - January/February 2011

IBM Systems Magazine, Mainframe Edition - January/February 2011
Table of Contents
On the Web
Editor's Desk: Time is Expensive
Data Display: Utilities, Users and Devices Are Getting Energy Wise Are Getting Energy Wise
Think Smarter: Smart Storage Solutions Let You Have It All
Insider: Energy Efficiency is More Than an Optimized Server
Trends: Case Study Shows IBM’s zEnterprise System Reduces Costs Across the Business
Moving Forward: UniGroup Inc. develops a mainframe-based Web tool to create a more flexible business model.
Tipping the Scale: Labor expenses should influence IT acquisition decisions.
Four-Star Advantage: The Polaris Workshop maps out a custom platform strategy.
Tips and Techniques: Reduce Resource Consumption on DB2 10 for z/OS
Solutions
Advertisers' Index
Stop Run: System/360 Pioneer Fred Brooks Teaches Design That Still Shapes Computing
IBM Systems Magazine, Mainframe Edition - January/February 2011 - IBM Systems Magazine, Mainframe Edition - January/February 2011
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Cover2
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 1
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 2
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 3
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Table of Contents
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 5
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 6
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 7
IBM Systems Magazine, Mainframe Edition - January/February 2011 - On the Web
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 9
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 10
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 11
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Editor's Desk: Time is Expensive
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 13
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Data Display: Utilities, Users and Devices Are Getting Energy Wise Are Getting Energy Wise
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 15
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Think Smarter: Smart Storage Solutions Let You Have It All
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 17
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 18
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 19
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Insider: Energy Efficiency is More Than an Optimized Server
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 21
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 22
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 23
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Trends: Case Study Shows IBM’s zEnterprise System Reduces Costs Across the Business
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 25
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 26
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 27
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Moving Forward: UniGroup Inc. develops a mainframe-based Web tool to create a more flexible business model.
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 29
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 30
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 31
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Tipping the Scale: Labor expenses should influence IT acquisition decisions.
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 33
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 34
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 35
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Four-Star Advantage: The Polaris Workshop maps out a custom platform strategy.
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 37
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 38
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 39
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 40
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 41
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Tips and Techniques: Reduce Resource Consumption on DB2 10 for z/OS
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 43
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 44
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Solutions
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Advertisers' Index
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Stop Run: System/360 Pioneer Fred Brooks Teaches Design That Still Shapes Computing
IBM Systems Magazine, Mainframe Edition - January/February 2011 - 48
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Cover3
IBM Systems Magazine, Mainframe Edition - January/February 2011 - Cover4
IBM Systems Magazine, Mainframe Edition - January/February 2011 - RF1
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/relevantz_20190102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2019mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20181112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2018mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20171112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_sesupp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_linuxsupp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20161112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/MainframeSecurity
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20151112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150910_se
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150910
http://www.ibmsystemsmagmainframedigital.com/MFSkills
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150506_supp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20141112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_gt_201405
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/BigData
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20131112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20121112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/buyersguide2013
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/ibmsystems_mainframe_2012bg
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20111112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20101112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100910_bg
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20091112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090506
http://www.nxtbook.com/nxtbooks/ibmsystemsmag/mainframe_20090304
http://www.nxtbook.com/nxtbooks/mspcomm/ibmsystems_mainframe_200901
http://www.nxtbookMEDIA.com