Feb 1 ’06
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.
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:
- Running V8 code: DB2 is now running V8 executables except for code that performs any functions new to V8. More on this later.
- New utility functions are available: Utilities such as REORG and Image Copy are now in V8, including any new functionality, parameters, and performance benefits. (Exception: BACKUP SYSTEM and RECOVER SYSTEM aren’t available.)
- Parser now processes Unicode data: All SQL must be converted (if necessary) to Unicode before it can be parsed.
- New access paths are available: Binds of SQL statements can now take advantage of new join methods and new access paths.
- There are some control blocks above the 2GB bar: Portions of the EDM Pool (Database Drivers [DBDs] and dynamic statement cache) have moved above the 2GB bar, providing virtual storage constraint relief.
- DRDA requestors can use multi-row processing: This applies mostly to dynamic SQL arriving from Distributed Relational Database Architecture (DRDA) requestors through Distributed Data Facility (DDF). They can use multi-row fetch, insert, and update functions, which can provide significant CPU savings. (Static SQL multi-row processing isn’t available until you’re in NFM.)
- Page fixing for virtual pools exists: Virtual pools can now be page-fixed. This creates some CPU savings, as DB2 no longer must release and regain storage for these pools.
Compatibility Mode Issues
In CM, issues generally fall into these categories:
- CPU increases
- Code page and character set issues
- Data sharing “mixed mode”
- Access path regression.
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:
- Much of the DB2 code now runs in 64-bit mode.
- The parser requires conversion to and from Unicode.
- Indexes now allow variable-length keys.
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:
- Have DRDA requestors use multi-row processing and, when migrating to NFM, use multi-row processing (especially in DSNTIAUL and DSNTEP4)
- Rebind plans and packages that may take advantage of new (or better) access paths
- Implement long-term page fixing of virtual pools; review this with your systems programming staff first to ensure that real storage is available for the virtual pools.
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:
- Verify the CCSIDs in DSNHDECP are valid
- Validate the CCSIDs of the data stored in your tables
- Document the code pages used by your terminal emulators.
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.
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:
- Plans and packages that reference catalog tables
- Vendor packages
- New SQLCODES.
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.
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!