idug.org - International DB2 User Group

Tuesday
Feb 09th
Text size
  • Increase font size
  • Default font size
  • Decrease font size

Best practices- DB2 9 for z/OS migration

E-mail Print

Roger Miller, IBM

Customers are migrating more quickly to DB2 9 this year.  The DB2 9 value is helpful for most customers, and the migration experience has been positive.  Migration is a broad topic, so I’ll emphasize some keys to success, point to many resources, emphasize some important points and provide the latest information. In this article, I’d like to note current service level recommendations, note some of the key changes in access path management, note changes for work space, indicate recent changes in data format, and then point to the newest function changes.

 

Get the right service level

 

As you migrate to DB2 9, using the best practices and recommendations can help you have a better experience.  The Consolidated Service Test or CST process is being used successfully by very large and small customers.  The current service level recommendation is to get to the current quarterly CST level and some specific identified APARs as a minimum level.  The August 2009 level is RSU0906, delivered in July 2009.  RSU0906 includes all service through the end of March 2009 and PTF in error resolution, high impact, security, integrity, pervasive PTFs and their associated requisites and supersedes through the end of May 2009.  Additional service is noted in the reports.

http://www.ibm.com/servers/eserver/zseries/zos/servicetst/mission.html

 

Check on the key information APARs.  Check the areas which you use most.  System z zIIP processors can make parallel processing less expensive than sequential access, but can cause problems in storage management and parallel processing, so check those areas if you use parallelism.

  • II14401 notes the key migration and fallback APARs.
  • II10817 indicates the key storage APARs.
  • II12836 notes the APARs for parallelism.
  • II14334 shows the APARs for large objects or LOBs.
  • II14426 shows the XML APARs.
  • II14441 notes recent APARs for incorrect output.
  • II14203 shows the key APARs related to distributed processing or DDF.

 

Most DB2 for z/OS customers are large in some metrics, and unique in a number of ways.  After getting all of the recommended service, you need to test with your unique workloads and options to find any other needed service.  Staging DB2 service levels through development, QA, and production also helps find situations that are unique.

 

Get ready for access path management

 

Recommendations for some of the subsystem parameters have changed.  At this time, the recommended value for parameters is:

  • STATCLUS = ENHANCED    ENHANCED is the default and recommended value.  DB2 9 optimization is better when the matching statistics are collected, improving information for clustering and scanning.
  • OPTIOWGT = ENABLE    ENABLE is the default after APAR PK75643, PTF UK42565(0901) is installed.  This change provides more appropriate weight balance for CPU and IO with faster processors, such as the z9 and z10.
  • OPTIXOPREF = ON       ON is the default after APAR PK77426 is installed. This value will favor index only access.

 

The DB2 package stability or access path stability function has been very helpful with customers who have access path regression in getting back to the old access paths.  Read section 10.13 of Packages Revisited for a discussion of the options and techniques.  If the disk space for two or three times the packages in SPT01 is a concern, then watch for APAR PK80375, which will allow compression on the SPT01 table space.  Chapter 4 of Packages Revisited discusses conversion to packages from plans containing DBRMs.

DB2 9 for z/OS Packages Revisited, SG24-7688 http://www.redbooks.ibm.com/abstracts/SG247688.html

 

The best techniques for minimizing potential access path issues are to

Use the Optimization Service Center (OSC) to capture the SQL statements and related information.  You can start using OSC on V8 to get the needed information and to help tune your queries.

New Tools for Query Optimization, SG24-7421, http://www.redbooks.ibm.com/abstracts/sg247421.html

 

Run the statistics adviser to generate the recommended statistics to be collected.

Get current RUNSTATS data with the enhanced statistics on clustering, so that the DB2 9 optimizer is using DB2 9 statistics.  Get the statistics noted in the item above.

 

Set up the work space

DB2 9 has substantial changes for work space.  Rather than separate databases and table space for work files and temporary tables, the space has converged.  Space for work files will favor 32K files when record sizes are over 100 bytes.  Every customer needs to have much more space for 32K files and will move from the TEMP database to the work file database.  While work files can span table spaces, declared temporary tables cannot.  If you can avoid having small work files compared to the largest declared temporary tables, then most problems are avoided.

 

Some customers need more separation of declared temporary space and work files.  A technique was delivered in APAR PK70060 to provide the separation. Some of the documentation and the installation changes are still in process.

The converged space uses DB2-managed table spaces instead of user-managed ones.  Use multiple table spaces with zero secondary space for work files.  DB2 gives preference to table spaces with zero secondary quantity when allocating space for work files. Multiple work file table spaces help to support efficient concurrent read and write I/O's to work files.

 

If applications use large Declared Global Temporary Tables (DGTTs), then some table spaces should be defined with non-zero secondary quantity in the WORKFILE data base. This will minimize contention for space between work files and DGTTs.  DB2 allocation of space for DGTTs gives preference to table spaces that can grow beyond the primary space allocation (with SECQTY > 0), since DGTTs are limited to one table space and cannot span multiple table spaces as the work files can.

 

If the subsystem has no DGTTs or only small DGTTs, then having all table spaces defined with zero secondary quantity works best.

 

New data format

 

Reordered row format occurs in NFM.  A number of recent changes have adjusted the function to be more flexible.  APAR PK78958 disables RRF conversion for table spaces which are compressed.  The primary challenge was table spaces with many VARCHAR(1) columns, where the compression ratio was reduced significantly.  APAR PK87348 will provide more extensive changes, externalizing a subsystem parameter and a utility keyword to keep basic row format.

 

Watch for recent and upcoming changes

 

Rather than creating 60,000 databases implicitly, changes in PK62178 default to 10,000 and allow you to change the number of databases to use.  If you want to pipe information to the LOAD utility, look at PK70269.  PK72214 changes the install process to stop changed data capture for catalog tables as needed, asking you to restart them after switching to DB2 9 and ENFM.  If you have multiple members of a data sharing group in a single LPAR and prefer the V8 group attach logic, parameters are added to control the random group logic in APARs PK79228 and PK79327.

 

The -ACCESS command only worked for specific names, but the command is being changed to allow wild cards and ranges in APAR PK80925.

 

This is the latest information today, but I’m certain that more is coming soon.  DB2 9 for z/OS: Resource Serialization and Concurrency Control is a new IBM Redbooks publication SG24-4725-01 that we expect to deliver in a few months.  Watch for more updates to DB2 books and other information on the web.

http://www.ibm.com/software/db2/zos

 

Trackback(0)
Comments (0)Add Comment
feedSubscribe to this comment's feed

Write comment
You must be logged in to post a comment. Please register if you do not have an account yet.

busy
 

Top Spotlight

 

IDUG Mentor

Apply here to be an IDUG Mentor! IDUG wants to recognize and help its long term members who regularly attend IDUG conferences and share that knowledge with their co-workers.  As every year gets harder to justify conference attendance, IDUG wants to make this year special for you with an unprecedented offer. If you qualify to be an IDUG Mentor, here is just a snapshot of what awaits you at the IDUG 2010 North America Conference: Recognition at the opening session Special meeting with IBM developers and presenters Early admission to the vendor exhibits Mentor SIG for you to share training ideas Eligibility to apply for a $1600 discount coupon to bring a first-time attendee IDUG knows that companies are cutting back on training budgets given the current economic climate.  Some companies are now deciding whether you, the team expert, or a less experienced team member should be the one sent to training. In many cases, they are saying that the budget this year is too tight to allow anyone to go.  To show you appreciation for your past IDUG conference attendance, we want to offer you the ability to convince your manager this is the BEST year to send you AND a new team member. To become an IDUG Mentor, you must have attended 5 major IDUG Conferences, North America or Europe, in the past 10 years and submit an application listing the conferences and years that you have attended.  With your full price conference registration, you become eligible to apply for a $1,600 discount coupon for the North American 2010 conference made out to a brand new IDUG conference attendee under your sponsorship. Two full IDUG conference registrations for a total of $2,390 - the absolute best value in training you will find anywhere!  Air fares are the lowest in years, and Tampa, Florida has some of the most reasonable rates in the country.  This is the year that your company should be taking advantage of these deals -- allowing you to share your IDUG experience with newer employees to ensure long term success! Apply here to be an IDUG Mentor!   We will need to know which 5 major conferences you have attended.  If you wish to sponsor a brand new IDUG attendee for the $1600 Mentor discount coupon, please include the name, e-mail address, phone number, and place of employment of the person you wish to sponsor.  IDUG's Mentor administrator will verify that the individual has not attended a conference in the past and notify you once eligibility has been confirmed. After you have completed your registration, notify the Mentor administrator and they will generate a special registration code for your Mentee. Please note that the coupon is specific to that individual, and they must provide identification at the conference when picking up their conference badge.  Because of this very special discount rate, IDUG must limit the number of coupons to one per company.  It is also limited to Mentors who are paying a full price registration.  While speakers or IDUG volunteers are eligible to apply and become an IDUG Mentor, they are not eligible to use any other registration discount and also get the Mentor discount rate.  

idug.org login
Lost Password?
Forgot your username?
No account yet? Register
Show Cart
Your Cart is currently empty.