DB2 Performance Features in DB2 V9 for z/OS – Part 17

New APARs Bring Features into V8 and V9 NOW!

Some of the new tablespace partitioning choices and features in DB2 Version 8 and now DB2 Version 9 for z/OS that I talked about last week are getting extra enhancements.  During the recent IBM System Z Summit road show that was in Washington DC last week, a number of other tablespace enhancements were discussed that are making their way back into Version 8.

Within the IBM Z Summit, there are not only great presentations on DB2 but also Rational Application Developer, CICS, WebSphere and other great System Z products that continue to make them the lowest cost of ownership products across the computing spectrum.  If you have not seen the agenda yet, go here (http://www-01.ibm.com/software/os/systemz/summit/index.html) to see all the great presentations.

During the DB2 presentations, the talk about current technology, its enhancements and the new DB2 Version X for z/OS was very interesting.  More great features are making it into DB2 and some are making all the way back into DB2 Version 8 through the APAR maintenance stream.

One great enhancement is the exposure of the ability to possibly compress SPT01.  This is coming into DB2 by exposing a DB2 Version 8 and DB2 Version 9 for z/OS zParm.  This zParm is exposed through APAR PK80375 and has a number of considerations especially for data sharing environments that are storage constrained.  This might help your system perform better and relieve some of the memory challenges on your system.

Other Z Summit presentation discussions talked about the reordered row format (RRF) feature and recommended applying APARs PK78958, PK78959, PK79127, PK87348 and PK85881.  The RRF feature is great feature to help minimizing the logging within the tables with VARCHAR columns.  This can be especially beneficial to improve DB2 performance for some of the large software packages that have many VARCHAR(1) column definitions.

Other presentations within the Z Summit talked about a number of great features coming and also other APARs that might help performance for other situations.  They were:

  • APAR PK70060 to separate the DGTTs from competing with DB2 Sort processes within the Workfiles for secondary extents within the TEMP database.
  • APAR PK83992 is great for speeding up unloads that use File Reference Variables for unloading LOB data.
  • APARs PK81471, PK81470 and UK41212 for the APPEND process.  This can be a great option for quickly adding data or LOBS into a table and then reclaiming the space through a REORG.
  • APAR PK49265 helps the optimizer prune “Always False” predicates.  Within SQL sometimes developers code deliberate false statements within their SQL WHERE clauses.  This APAR helps the optimizer understand the SQL better and sometimes allows index access.  Check this one out if your developers commonly rely on this bad habit of coding false WHERE predicates too much.
  • APAR PK59731, PK51734 and PK76100 help also with SQL optimization.  These APARs help with picking the best SQL access path such as favoring index usage, index-only access and Pair Wise Star Join methods over less efficient index and data access methods.  These APARs and their impact can help DB2 choose the best access path and the best indexes that dramatically speed up your processing to improve DB2 performance.

Of course, some of these are older APARs and there may be a number of considerations with all of them, so research if your system needs them before putting them on your system.

It is always great to see that IBM continues to make DB2 the best performing database any way they can.  Even though it is somewhat difficult through the APAR process, we all appreciate every DB2 performance item IBM can help us get into our systems.

_______________________________________________________

Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand, saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

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>