How to Have a Happy Conversion Mode Day

Ready or not, IT organizations can prepare now for eventual IBM DB2 11 for z/OS conversion

A lot has been said, written, and presented regarding the many benefits and enhancements IBM® DB2® 11 for z/OS® data management has to offer organizations. These benefits include enhanced application compatibility and access path stability, as well as CPU cost reductions for many batch processes, queries, and online transaction processing (OLTP) operations. Many IT organizations are in the process of building cost cases to get management approval for the upgrade, or are working with the business to agree on implementation dates to help minimize application impact. Other client organizations are deliberately building a delay into their implementation plans to allow the DB2 11 for z/OS codebase to mature a bit more before committing themselves.1

Whatever the reason, IT organizations are looking at dates for the initial upgrade to DB2 11 for z/OS conversion mode (CM) that may be many months or even years away. That delay can sometimes be frustrating from a technical perspective, but there are significant amounts of preparation work that can be done well in advance of the big day when any given DB2 environment is upgraded to DB2 11 for z/OS CM.2 Some of the major preparation tasks are discussed here.


Creating a performance baseline

Before upgrading, IT organizations should ensure they have a good picture of their current critical DB2 transactions and batch processes. If there are any performance-related complaints or problems following the upgrade, how else will an organization be able to really tell if there has been any regression?

Most DB2 performance monitors allow creating and populating a performance warehouse that stores both detailed and summarized information about the performance of key processes. This data is critical to establish a before upgrade position for later comparison, but it’s surprising how many large, well-established DB2 sites don’t create this performance snapshot. If any DB2 environment falls into this category, the IT organization should spend some time now to begin collecting the performance data for a historical baseline that can be used as a basis for comparison after moving to DB2 11 for z/OS CM.

Most releases of DB2 involve changes to the way CPU time is allocated between the DB2 address spaces and allied address spaces. Collecting historical data for both the system address spaces—through statistics trace—and allied threads—through accounting—is therefore useful so that IT organizations can consider the whole picture when comparing a given workload before and after the upgrade.


Preparing packages preemptively

In an effort to reduce the overhead associated with supporting an ever-expanding array of obsolete package structures, IBM has been tightening the restrictions for legacy packages supported by recent DB2 releases. For example, DB2 10 required a REBIND for any packages bound earlier than DB2 Version 6, and DB2 11 moves the goalposts again, achieving IBM’s aim to support only package structures from the two previous releases. Therefore, packages bound before DB2 Version 9 are not supported upon entry to DB2 11 for z/OS CM.3

Part of the preparation to upgrade to DB2 11 for z/OS should include identifying any pre-DB2 Version 9 packages and rebinding them in advance to avoid any nasty surprises that may occur if DB2 performs an automatic REBIND when the package is executed in DB2 11 for z/OS CM. Note that the DSNTIJPB job includes a report to help identify the pre-DB2 Version 9 packages.

Bear in mind that there are some powerful facilities that were introduced in DB2 9 and DB2 10 to help combat the possibility of access path regression during a rebind operation. The plan stability feature allows database administrators (DBAs) to keep either one or two previous copies of a package during the REBIND process. If DBAs encounter any issues with the new package, they can simply use the SWITCH option to revert to the previous copy.

The new APREUSE and APCOMPARE BIND options introduced in DB2 10—and enhanced again in DB2 11—provide even more powerful options for minimizing the possibility of access path regression during mass REBINDs. Unfortunately, APREUSE and APCOMPARE need some advanced package structures that weren’t introduced until DB2 9, so they are not going to help with any pre-DB2 Version 9 packages. However, these options are expected to be of great value during REBINDs in DB2 11 for z/OS CM—and in preparing for planned future releases beyond DB2 11.


Introducing 10-byte log addressing

DB2 11 expands relative byte address (RBA) and log record sequence number (LRSN) values from 6 to 10 bytes to dramatically increase the addressable log range. Specific steps are required in DB2 11 for z/OS new-function mode (NFM) to convert bootstrap data sets (BSDSs) and system and application page sets to the new 10-byte formats. However, DB2 11 still uses 10-byte addressing internally when DB2 11 for z/OS is started in CM. As a result, DISPLAY THREAD commands show a 10-byte unit-of-recovery identifier (URID) value—the 6-byte value that is left-padded with zeroes—from DB2 11 for z/OS CM onward. Any processes or utilities that parse URID values must be prepared to accommodate the new 10-byte values.

Similar considerations apply to processing DB2 trace records that contain RBA and LRSN values. These fields will also be expanded to 10 bytes upon entry to DB2 11 for z/OS CM. In most cases, these fields have been moved so other offsets in the record are not impacted, but there are some exceptions. The “DB2 11 for z/OS Installation and Migration Guide” contains detailed information on how to change programs to account for increased sizes and new offsets.4


Identifying application incompatibilities

DB2 11 for z/OS introduces some changes that may require applications to be amended, as new releases tend to do. Organizations are advised to research the full list documented in the “DB2 11 for z/OS Installation and Migration Guide,” but the good news is that IBM is trying to ease the process of identifying potential incompatibilities well in advance. PM84769 is a DB2 10 authorized program analysis report (APAR) that extends IFCID366 to report on some of the new reserved words in DB2 11.5 IT organizations can therefore obtain plenty of advance warning on many of the changes needed to their applications.

Of course, most application-release incompatibilities don’t actually need to be addressed until an organization moves to DB2 11 for z/OS NFM. If any incompatibilities were not already resolved earlier, the DB2 11 extended IFCID366 and IFCID376 trace records can be used while running DB2 11 for z/OS CM to help identify any incompatible SQL and XML code that may be executed. If some applications still haven’t made the required changes when an organization is ready to move the environment to DB2 11 for z/OS NFM, the new DB2 11 APPLCOMPAT feature can be used to allow specific packages to continue to run in DB2 10 for z/OS compatibility mode. And for organizations that still choose or are unable to get off those legacy, unsupported compiler releases, IBM has again provided support for the DB2 Version 7 precompiler—DSNHPC7.


Performing other DB2 11 preparation tasks

In addition to the areas discussed thus far, IT organizations can take many additional preparation steps in advance of a move to DB2 11 for z/OS CM. A comprehensive discussion is available in the paper, “How to Have a Happy DB2 11 CM Day.”6 It includes the following additional topics:

  • Getting to DB2 10 NFM
  • Checking hardware and software prerequisites
  • Checking the informational APARs and staying up-to-date on maintenance
  • Checking real and virtual storage availability
  • Applying the DB2 11 for z/OS fallback small processing enhancement (SPE)
  • Converting any remaining simple table spaces
  • Running the pre-migration jobs
  • Cleaning up ZPARMs
  • Checking the health of the catalog and directory
  • Checking EXPLAIN table format validity
  • Checking for use of unsupported stored procedures and functions
  • Removing views, materialized query tables (MQTs), and SQL table functions with period specifications
  • Removing REORG TABLESPACE SHRLEVEL NONE on locator object (LOB) table spaces
  • Handling expanded Distributed Relational Database Architecture (DRDA) client information
  • Removing password protection for active and archive logs
  • Checking DB2 tool compatibility
  • Testing
  • Organizing DB2 11 for z/OS education

Upgrading to DB2 11 for z/OS has the potential to deliver some compelling business benefits, including out-of-the-box CPU cost reduction and helping improve scalability and efficiency. Organizations that are able to begin their preparations early can greatly reduce the elapsed time and risk associated with the actual upgrade and begin exploiting those business benefits sooner rather than later. Please share any thoughts or questions in the comments.

1 Note that IBM put an increased emphasis on early production readiness during product development, and at least one client organization went into production with DB2 11 for z/OS prior to general availability. Feedback from early support program (ESP) client organizations consistently praised the overall code quality and robustness of the DB2 11 release.
2,6 For more information on preparing for DB2 11 for z/OS CM, see “How to Have a Happy DB2 11 CM Day,” by Julian Stuhler, Triton Consulting white paper. Note: Registration is required.
3 Note that DSNTIJPM and DSNTIJPB list all packages bound prior to DB2 9. Those packages still in existence upon entry to DB2 11 for z/OS CM will be dynamically rebound at runtime, if YES or COEXIST for the ABIND DSNZPARM is specified. Otherwise, any attempt to execute them will fail with SQLCODE -908 and SQLSTATE 23510—for packages—or SQLCODE -923 and SQLSTATE 57015—for plans.
4 For a comprehensive list of DB2 11 prerequisites, see DB2 11 for z/OS Installation and Migration Guide,” third edition, IBM, December 2013. In addition, a “pre-migration checklist is available at the site.
5PM84769: Flagging Next Release after V10’s Reserved Word Incompatibilities in V10,” IBM Support Portal APAR, July 2013.