Feb 1 ’06

Migration Challenges

by Editor in z/Journal

I covered the planning steps necessary to migrate to DB2 UDB for z/OS Version 8 in a previous article (see “DB2 Universal Database for z/OS Version 8: Pre-Planning Your Migration,” in the April/May 2004 issue of z/Journal).

IBM documents the V8 migration process in the DB2 V8 Installation Guide, and provides additional information and planning tips on its Website. We won’t go into the specifics and prerequisites of the migration in this article; instead, we’ll concentrate on the major steps (or “modes”) of DB2 during the migration and the typical issues IT shops face at each point.

Issues Prior to Migration

Before running any DB2 V8 migration jobs, you must begin with a DB2 Universal Database (UDB) for z/OS and OS/390 V7 subsystem. Here, you must make several decisions regarding your software maintenance strategy.

Your software maintenance strategy determines how soon and when you’ll apply maintenance to your suite of mainframe software (DB2, CICS, z/OS, IMS, DFSMS, JES, and so forth). There’s an excellent IBM manual describing how to develop a strategy titled Parallel Sysplex: Software Management for Availability, SG24-5451. This will help you determine how often to apply preventive maintenance (PTFs), HIPERs, ServerPacs, and so forth.

Another aspect of software maintenance is whether you’ll take advantage of Recommended Service Upgrade (RSU) or Consolidated Service Test (CST). For more information on CST and RSU, visit the IBM Website mentioned at the end of this article.

At some point prior to migration, you must upgrade the DB2 ERLY code; this may require an Initial Program Load (IPL). See my previously mentioned article for more details.

Migration Steps and Typical Issues

Figure 1 shows the V7-to-V8 migration process. This process chart doesn’t show the actual or recommended elapsed times to spend in each mode. Many shops intend to stay in Compatibility Mode (CM) for several weeks to several months. After that, running the Enable New Function Mode (ENFM) job may take an hour or more, after which they migrate directly to New Function Mode (NFM) and run comfortably. Your experiences may vary.

Compatibility Mode

As you run migration jobs, your DB2 subsystem is transformed from one mode to the next. The first stop is CM. This is reached after running multiple jobs culminating in DSNTIJTC (Tailor Catalog), which runs the CatMaint program.

Several things have happened in CM:

Compatibility Mode Issues

In CM, issues generally fall into these categories:

CPU Issues

Some shops have seen a significant increase in CPU usage after migrating to CM. Among several possible reasons for this are changes in the way Task Control Block (TCB) and Service Request Block (SRB) CPU times are now charged to various address spaces.

Most CPU-related issues arise because:

SQL processing and access to tables and indexes occur in the DBM1 address space. In V8, the DBM1 address space runs in 64-bit mode; the code uses 64- bit instructions. The instructions are “wider,” memory addresses are longer, and executable modules are somewhat larger. There are more “bytes” of executable code, resulting in somewhat longer path lengths.

Some CPU is now used in converting data to Unicode. In addition, the index-handling code has been greatly expanded to handle potentially variable- length keys.

IBM provides materials explaining what kinds of environments may experience CPU regression and these are documented on the IBM support Website.

You may be able to mitigate this CPU consumption problem if you:

Code Page and Character Set Issues

As part of pre-planning for migration, you audited your DB2 subsystem for coded character set (CCSID) usage. You should do this now because it’s essential that you:

Use migration job DSNTIJP8 to see if you have multiple CCSIDs in one encoding. Reference APARs PQ56697 and PQ89018 to confirm that your terminal emulators are configured correctly.

Failure to resolve code page issues before migration may result in lost data or a DB2 subsystem that won’t work.


Data Sharing Mixed-Mode Issues

Mixed mode refers to running a DB2 data sharing environment with one or more members running as DB2 V8 and one or more others running as DB2 V7. The data sharing members share the DB2 catalog and directory, which means there are shared plans for SPUFI, DSNTIAD, DSNTIAUL, DSNTEP2, and DSNTEP4. Binding one or more of these plans in the V8 environment may cause problems if they’re executed from a V7 member.

Consider defining packages for these plans and binding two versions of each.

There are some restrictions on issuing commands against utilities in a data sharing mixed mode environment. If you execute a utility in a V7 environment, all commands issued against the utility (such as TERM) should be issued only from the V7 environment.

Refer to the Installation Guide and the Data Sharing Planning and Administration manuals for details on data sharing mixed-mode issues.

Access Path Regression

Some shops have experienced changes in access paths for SQL statements bound in the V8 environment. There are many considerations here, including newly available access paths such as reverse index scanning and SQL statements that contain certain categories of predicates designed to coerce DB2 into choosing a particular path.

Some of these issues can be addressed by using DB2 V8 enhanced catalog statistics and by removing oddball predicates. One additional technique is to use optimizer hints. See the SQL Reference Guide for more information.

Enable NFM

The next step is the execution of CatMaint to convert many DB2 Catalog columns to Unicode and expanding the length of many to 128 bytes. While this job is running, you’re enabling NFM.

Typical Issues in ENFM

Job DSNTIJNE may abend due to lack of sort work space or other reasons. It may be restarted, but once started, there’s no falling back.

Execution time for this job is mostly a function of the size of your catalog, particularly tablespace SYSDBASE, since it contains tables, SYSCOLUMNS, SYSTABLES, and others whose columns are being expanded. IBM has made presentations and white papers on this process available on their DB2 support Website.

 “No New Function” NFM

Once job DSNTIJNE has completed, you’re a step away from full DB2 V8 NFM. All catalog conversion is now complete and all V8 executable code is in place. The last step is to run job DSNTIJNF (New Function). This will make available all DB2 V8 functions that were delivered with the product. (A few other steps are needed to allow applications to use these functions, including binding plans and packages with NEWFUN(YES).)

Transition Mode Issues

Most new issues that arise in this mode center around the catalog changes. Many columns that were once EBCDIC and fixed length are now in Unicode and defined as VARCHAR(128). So, you must be careful when coding SQL statements that access these catalog tables.

Most of the conversions necessary will occur automatically. For example, an SQL Select against SYSTABLES in SPUFI may appear to display EBCDIC data (rather than Unicode). This will depend on the CCSID settings in DSNHDECP, how you’ve bound the SPUFI plan (or package), and the code page setting for your terminal emulator.

Another set of issues revolves around certain characters that may not be converted correctly. Some shops have experienced difficulty binding SQL statements containing the caret (^) character, which can be used as a “not” operator in operations on expressions such as:

WHERE T1.COL1 ^= ‘1’

Here the caret with the equals sign is the not equals operator.

Another related issue is accessing Unicode data that may contain multibyte characters. Functions such as SUBSTR and LENGTH are byte-based, not character-based. They function on strings by byte position. Since Unicode strings may have multi-byte characters, however, use of SUBSTR and LENGTH may produce incorrect results.

You probably have no catalog tables containing object names (table names, column names, etc.) that, when converted to Unicode, result in a multi-byte character. Still, it’s possible. In any case, with the advent of Unicode data, you should move to the use of character-based functions such as SUBSTRING.

Here’s an example:



For a discussion of these and other Unicode issues, visit the IBM DB2 support Website.

Some customers may want to remain in this mode for a while before implementing NFM. They may believe they must resolve the issues mentioned previously before migrating to NFM. Some customers may have chosen to migrate to V8 for the benefits already achieved in CM, such as new utility function and some virtual storage constraint relief.

Don’t stay in this mode too long. IBM and third-party vendors are likely devoting their resources to exploiting new V8 functions rather than debugging problems that might occur in ENFM or NNFNFM. Since the expected, stable platform is DB2 V8 in NFM, you should move there as soon as possible after running the ENFM jobs.

New Function Mode

Running job DSNTIJNF enables NFM. All new functions are available. Apart from concerns regarding use and implementation of new functions, there are several issues that arise only in NFM:

Referencing Catalog Tables

Many of the catalog tables were changed by job DSNTIJNF. As a result, some SQL in applications that reference these catalog tables may need to be revisited. For example, any applications that select column values from rows in one of these tables will need new host variable definitions, since the column data types have changed to VARCHAR(128). Also, any queries containing an ORDER BY specification on any column converted to Unicode will use the Unicode collating sequence, not EBCDIC.

Vendor Packages

Some shops have experienced complicated symptoms because vendor packages weren’t updated to the correct service level. Ensure that all third-party software packages used in the DB2 V8 NFM environment will either tolerate or exploit the features you need.


There are new SQLCODES that relate to potential errors in string conversions. It’s possible that applications (particularly distributed applications or those using dynamic SQL) will now encounter new exceptions.

One new complexity involves multirow processing. It’s now possible to catch an array of SQLCODES. For example, if an application opens a cursor defined as FOR UPDATE OF, fetches a group of 10 rows, and then updates five of the rows, it’s possible the application will have to deal with 10 or more SQLCODES. These won’t be available in the SQL Communications Area (SQLCA); instead, the application must use the new GET DIAGNOSTICS statement.


IBM has gathered a wealth of V8 migration information that’s available on the DB2 support Website, including presentations, white papers, Redbooks, and other migration recommendations. Shops contemplating V8 migration should gather and take advantage of those resources pertinent to their environments. Especially valuable is the DB2 UDB for z/OS V8: Everything You Ever Wanted to Know and More Redbook. Good luck with your migration!