DB2 Tools corner: The DB2 for z/OS REORG utility and noncontiguous partitions

Reorganize multiple partitions of very large table spaces in parallel

By keeping up to date with the IBM® DB2® Utilities Suite for z/OS® service stream maintenance and enhancements, you can learn about the latest improvements that might solve some of your current problems. These add-ons can save you significant time by eliminating errors while saving your company from expending unneeded processor resources and improving availability. In this article, learn about an enhancement that lets you avoid contention when reorganizing noncontiguous partitions.

Mary Petras (marypetr@us.ibm.com), Consulting Product Design Professional, IBM

Mary Petras photoMary Petras works with the technical support team for DB2 for z/OS Tools and utilities. Mary joined IBM in 1996 and has worked with customers on their DB2 data sharing migrations. She has presented at conferences for the International DB2 Users Group (IDUG) and IBM Information On Demand (IOD), and she is a co-author of six IBM Redbooks publications devoted to DB2 performance, DB2 for z/OS performance tools, and DB2 data sharing.



20 January 2011

Overview

The BUILD2 phase only occurs for REORG at the partition level when non-partitioned secondary indexes (NPSIs) exist. Before DB2 9, the BUILD2 phase for an online REORG against a partitioned table space with NPSIs often caused availability issues. The logical index partition is unavailable for the duration of the BUILD2 phase; the longer the duration of the BUILD2 phase, the longer the outage of the underlying data. As a result, the BUILD2 phase became a major source of contention for many DB2 customers. In DB2 9, the BUILD2 phase was eliminated for REORG SHRLEVEL REFERENCE or CHANGE, and a shadow copy of each NPSI is taken instead.

Now, you might notice that more of the processor is used, and you might also need additional DASD for the shadow copy of each NPSI. On a positive note, this new enhancement not only reduces impact on index group buffer pools (GBP) in a data sharing environment when the NPSI is GBP-dependent, but it also allows for improved availability because the BUILD2 phase is eliminated.

When an online REORG is invoked for a single partition, multiple partitions, or a range of partitions in DB2 9, the REORG utility builds a shadow copy of each NPSI. The underlying NPSIs are still available to the application, and updates are applied to both the shadow copy and the original NPSI. Whether the REORG is a SHRLEVEL REFERENCE or CHANGE still determines whether the applications have READ and UPDATE access to the underlying data. The SWITCH phase allows DB2 to switch the application from using the original copy to the shadow copy of the data and indexes.

Because the NPSIs are now rewritten, scheduling a REORG INDEX against them might not be necessary. In each REORG at the partition level, a shadow copy of the NPSI is taken. Although it results in improved availability, it also implies that you cannot run concurrent REORGs at the partition level for separate partitions of the same table space. Each of these REORGs would have to shadow each NPSI, but multiple concurrent shadows of the same page set are not allowed.

DB2 allows only one utility job to make a shadow copy of the same NPSI, so any other REORG job fails because of contention on that one NPSI.

As a result, you can no longer run those REORGs against a single partition or range of partitions for the same table space concurrently. Before DB2 9, most customers had processes in place to concurrently REORG multiple partitions of a partitioned table space. With DB2 9, this limitation has caused some customers to revisit their current REORG methodology and change their existing batch utility schedules, or possibly rework utilities to avoid the contention. DB2 Tools can help by correctly generating the REORG utilities to avoid the contention.

If you had multiple online REORG jobs running concurrently against separate partitions of the same table space when NPSIs exist, you might receive the following message:

DSNU180I - UTILITY IS NOT COMPATIBLE WITH THE REORG TABLESPACE UTILITY UTILID = 
xxxxxx.yyyyyy, OBJECT = nnnnnn.mmmmmm

This error is a result of contention on one of the shadow NPSIs.

Of course, REORG SHRLEVEL REFERENCE or CHANGE is allowed to run against multiple contiguous partitions in the same table space. But what if the partitions that need to be reorganized are noncontiguous?


DB2 to the rescue

A new enhancement was delivered in the service stream to allow a change to the REORG syntax and to give you the opportunity to combine the reorganization of multiple noncontiguous partitions in a single REORG step. This effectively removes the restriction against running multiple REORG TABLESPACE PART jobs concurrently. For a REORG SHRLEVEL CHANGE with multiple parts, the NPSIs are only shadowed once, thereby significantly reducing overhead when multiple noncontiguous partitions need to be reorganized.

Before this enhancement, the DB2 REORG utility accepted only contiguous partitions in a REORG control statement, as shown in the following example:

REORG TABLESPACE SHRLEVEL CHANGE PART(10:15)

With this enhancement, you can REORG multiple noncontiguous partitions in the same statement. For example, if you need to REORG parts 10 - 15 and parts 17 and 24, issue the following REORG statement:

REORG TABLESPACE SHRLEVEL CHANGE PART(10:15, 17, 24)

For each NPSI, only one NPSI shadow would occur per REORG execution on a single table space.

The DB2 9 APAR/PTFs that provide the capability of native support of noncontiguous part range specifications in the REORG utility are PK87762/UK59095 and PM13259/UK59096. DB2 10 has this feature in its base product, so no PTF is necessary. After you install these PTFs, you must recycle DB2; otherwise, the changes will not be active.

This enhancement, which allows you to reorganize many partitions of very large table spaces in parallel instead of sequentially, can increase your disk space requirements and can lead to utility failure. To determine whether you should use this enhancement, carefully evaluate your requirements, and test the function and its capabilities in your environment.

New utility functions are available immediately when you migrate or install DB2 9 conversion mode (CM).


How to determine if a REORG is necessary

The DB2 Performance and Utilities group at the IBM Silicon Valley Lab recommends that you use real-time statistics (RTS) to determine if a REORG is necessary.


Automated procedures can help

The DB2 Automation Tool for z/OS allows you to build DB2 utility jobs based on various exception criteria.

Using DB2 Automation Tool to handle a REORG of multiple noncontiguous partitions or ranges is the best way to selectively determine your criteria for what gets reorganized and when reorganization will occur. The other benefit is that you can set up the profile once, and the profile can be reused (or edited), which saves time and reduces the chance for errors. You'll also make sure that you are taking advantage of the latest DB2 for z/OS and DB2 Utilities enhancements with DB2 Automation Tool.

This product allows you to set up different sets of profiles:

Object profiles
Determine which objects you want to run certain utilities against.
Utility profiles
Represent one or more utilities. In this case, you might use it to generate a REORG SHRLEVEL CHANGE against partitions of a table space.
Exception profiles
Specify which criteria should be used to determine that the REORG be generated. Each object in the object profile is scanned to check whether the criteria is met; utilities are generated against only those objects that meet the specified criteria. One such example of exception criteria is RTS.
Job profiles
Connect the object, utility, and exception profiles together.

There are many options to be specified when you build a job profile and run it to generate the online REORG. In particular, it will generate an online REORG of multiple noncontiguous parts if you have APAR/PTF PM03078/ UK58293 applied to the DB2 Automation Tool. This PTF supports the previously mentioned DB2 9 PTFs for generating an online REORG against multiple noncontiguous parts of the same partitioned table space.


Conclusion

It is important to stay informed about new features and functions that are provided by DB2 in the service stream, especially if they might solve a problem you are having in your environment. This is just one of the many improvements DB2 Utilities makes on your behalf to continue to improve the availability, scalability, and reliability of DB2 in your operating environment. We recommend that you stay current with DB2 maintenance and automate your REORG utility maintenance jobs by using a tool such as DB2 Automation Tool. Minimizing manual utility generation will make your life easier and allow you to focus on other, more important DBA tasks while knowing that your utility jobs will be executed based on the conditions that you determined are important in your DB2 environment.

You will need the following PTFs and APARs to ensure that you can perform an online REORG of multiple noncontiguous parts on your DB2 9 objects:

  • DB2 9 APAR/PTF PK87762/UK59095 and PM13259/UK59096
  • DB2 Automation Tool V3.1 for support in DB2 9 APAR/PTF PM03078/UK58293

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=618573
ArticleTitle=DB2 Tools corner: The DB2 for z/OS REORG utility and noncontiguous partitions
publish-date=01202011