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
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
Before this enhancement, the DB2 REORG utility accepted only contiguous
partitions in a
REORG control statement, as shown in the following
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 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.
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
- "DB2 9 for z/OS: Using the Utilities Suite" (IBM Redbooks®, April 2010): Read more about the different situations and considerations for when to schedule a REORG and about using the correct space statistics metrics from RUNSTATS or RTS.
- DB2 Utilities Suite for z/OS: Learn more about the DB2 Utilities Suite for z/OS.
- DB2 for z/OS area on developerWorks: Get the resources you need to advance your DB2 skills.
- developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.
- Participate in the discussion forum.
- Participate in developerWorks blogs and get involved in the developerWorks community; with your personal profile and custom home page, you can tailor developerWorks to your interests and interact with other developerWorks users.
Dig deeper into Information management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Keep up with the best and latest technical info to help you tackle your development challenges.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.