IBM Destination Z - Group home

Simplifying DB2 Migration

By Destination Z posted Mon December 23, 2019 03:27 PM


Migration to IBM DB2 11 for z/OS is much easier and faster when compared to migration to DB2 10. A large part of this is that DB2 has now separated migration of applications from the process of migrating DB2 subsystem.

Through the use of this important new capability—called application compatibility—you can migrate your DB2 10 subsystem to DB2 11 without making changes to applications to deal with any SQL incompatibilities presented by DB2 11. After completing migration to the new release, you can address any of these incompatibilities surgically and precisely.

Modes of Migration

The migration modes of prior releases exist in DB2 11 as well. These are conversion mode (CM), enabling-new-function mode (ENFM) and new-function mode (NFM).

CM is the first mode that you enter when migrating to DB2 11. You can remain in CM for as long as you like, however no new function is available in CM. Fallback to DB2 10 and data sharing coexistence with DB2 10 are supported only while in CM.

When you are satisfied that DB2 11 will support your environment, you can move from CM into ENFM. In ENFM, DB2 conditions the catalog and directory for the use of new function. To enter ENFM, no DB2 10 subsystems can be active in a data-sharing group. After you enter ENFM, you can no longer fall back to, or coexist with, DB2 10. As with CM, no new function is available in ENFM.

In NFM, new function that is not SQL related is available. New SQL related function requires that you specify APPLCOMPAT(V11R1) for your packages.

Application Compatibility in DB2 11 for z/OS

DB2 11 for z/OS introduces the concept of application compatibility to simplify migration. Prior to DB2 11, behavioral changes due to the new DB2 release could occur at migration to CM, migration to NFM, at BIND or PREPARE, or any combination of MODE and BIND or PREPARE. The behavior depended on if the change was in the optimizer (BIND or PREPARE), in the query execution engine, or was related to new function.

With DB2 11, application changes occur when the application moves to DB2 11 mode—APPLCOMPAT(V11R1). Additionally, all potential incompatible changes are traced with IFCID 376 so customers can tell which applications, and which SQL statements are impacted when moving to DB2 11. Customers can take advantage of this new capability to identify changes that they might need to make to applications that may be affected by incompatible changes in DB2 11.

Working Around Incompatible Changes

DB2 is over 30 years old, and one of the reasons it continues to be the backbone of many companies is that it continues to evolve. Additionally, industry standards exist for SQL that DB2 for z/OS must be compliant with. Resolving compliance issues, or enhancing the product sometimes introduces an incompatible change that may negatively effect existing applications.

An example of an incompatibility that was introduced in DB2 10 is a change to the CHAR function. To conform to the SQL standard, DB2 10 changed the way in which the CHAR and VARCHAR built-in functions format decimal data. The same was true for CAST specifications on decimal data with a CHAR or VARCHAR result type. In addition to complying with the SQL standard, this change improved consistency of DB2 for z/OS with other members of the IBM DB2 family.

When customers expressed their concern about this change, DB2 development responded with APARs PM66095 and PM65722 to mitigate the impact. Additionally, DB2 11 for z/OS introduced CHAR9 and VARCHAR9 functions to provide the behavior of V9 CHAR and VARCHAR functions, while still allowing new applications of clients or vendors to take advantage of standard SQL behavior.

By definition, most new function is incompatible with existing behavior in some way. At the very least, most new function results in the removal of an SQLCODE that previously indicated that this function was unavailable, either syntactically or semantically.

Impact to Customers

When customers learn about changes in SQL or XML behavior, they often have difficulty figuring out the impact of a single change to thousands of applications. DB2 has historically tried to limit incompatible changes. When changes were necessary, DB2 tried to implement these at a release boundary. Customers have very clearly stated that this isn’t an acceptable solution due to the complexity this adds to a DB2 migration.

Solution Requirements

Discussions with customers have led to requirements that need to be addressed in any solution to help migrate to new releases of DB2 with as little application impact as possible. The requirements include:

• Not forcing application changes to address incompatible SQL changes on a release boundary.
• Allowing changes to be introduced at the application (package) level.
• Providing more warning and time to customers for incompatible changes to be addressed.
• Providing a mechanism to identify applications that need to be analyzed for incompatible behavior.
• The need for a mechanism to identify the potential impact of new reserved words.


DB2 will continue to limit incompatibilities when possible. This is a basic tenant of simplifying migration and reducing customer impact. DB2 will also provide mechanisms to identify and make changes to affected applications (packages). To ensure that this capability provides reasonable time for customers to make these changes, this mechanism will enable support for up to two back-level releases (N-2).

DB2 Development also added code to DB2 10 to:

• Identify where DB2 11 keywords would cause a failure.
• Issue IFCID 366 to alert customers of this issue.

Because new reserved words are reserved only in context, we expect that most customers will identify few or no issues, but the function allows customers to move forward with confidence. APAR PM84769/UK94459 was closed in 2Q 2013. Three words are checked in context: ARRAY_EXISTS, CUBE and ROLLUP.

Pre-migration Planning

DB2 11 will also continue to flag any issues (with warnings or error codes) that are detected with SQL in the pre-migration job DSNTIJPM. In the future, during migration to the release that comes after DB2 11, DSNTIJPM will flag:

• Warnings in DB2 11+1 for any packages that are bound with APPLCOMPAT(V10R1)
• Warnings in DB2 11+2 for any packages that are bound with APPLCOMPAT(V11R1)
• Serious errors in DB2 11+2 for any packages that are bound with APPLCOMPAT(V10R1) or lower; these packages will be inoperative when migration to that following release (DB2 11+2) occurs

Summary and More Information

The APPLCOMPAT bind option provides a foundation to separate release migration from application changes that may be necessary to enable an application to take advantage of function in a new release.

Customers can use the tracing capabilities that are provided in DB2 11 to identify applications that potentially need to change. If any changes are necessary, customers have two releases to make the necessary changes to the affected applications.

Find out more about APPLCOMPAT. Learn more about DB2 for z/OS.

Chris Crone leads the team that develops the SQL interfaces for DB2 for z/OS where he has worked for the last 26 years. He also works closely with the z/OS and IBM z Systems teams on HW/SW synergy items. Chris is very active with customers and vendors, helping them deploy new applications and optimize their applications to run well with DB2 for z/OS.

Jay Yothers is an IBM DB2 for z/OS architect. He has been part of the DB2 Development organization since the first release of DB2 and has designed and developed many key features of DB2 for z/OS. He has been awarded more than 20 patents for his work in DB2 for z/OS. Jay is a frequent speaker at conferences such as IDUG, SHARE and Insight and at seminars, webcasts, user group meetings and executive briefings, bringing a wealth of knowledge and experience to such events. He can be reached at