In this article, I concentrate on planning the DB2 V7 migration process. While IBM provides detailed guidelines for DB2 migration in its manuals, their information is general – the emphasis is on the actual migration, not on the preparations you must make. Each migration is different, and the process must be customized for your environment. In addition, there are various tricks and traps for the unwary. Here are a few:

1. Plan for multiple IPLs as a part of your migration: One to apply the fallback maintenance, one during the actual migration ... and one when you have to fall back to a previous version. Oh, then another one when you re-migrate.
2. Do you have any ERP packages (PeopleSoft, SAP, Seibel, etc.)? Immediately upon migration these packages may take advantage of features like scrollable cursors and Unicode. Will these work?
3. The default values for several Zparms have changed. Have you taken these into account in your production Bind process?

I will review these and other gotchas as I review a typical migration, focusing on the most important part: the planning process.

Release Incompatibilities

IBM documents release incompatibilities in several places, including the Release Guides and Installation Guides:

• DB2 V6 Release Planning Guide (SC26-9013-03)
• DB2 V6 Installation Guide (GC26-9008-02)
• DB2 V7 Release Planning Guide (SC26-9943-01)
• DB2 V7 Installation Guide (GC26-9936-01)

The best information is in the Installation Guide(s). The chapters on migrating from Version 5 and Version 6 have sections titled "Migration Considerations" that list the various new, unsupported, and changed features and functions.

Here are a few examples (taken from the DB2 V6 and V7 Installation Guides):

1. (Version 6) Unsupported objects. Type 1 indexes are now unsupported, as well as shared read-only data and dataset passwords for the catalog and directory objects. IBM includes a job DSNTIJPM in the SDSNSAMP library that identifies these objects. Note on dataset passwords: There are two places where catalog/directory passwords come into play: in the underlying VSAM datasets and in old catalog table columns. The VSAM dataset passwords are removed using the IDCAMS ALTER statement (and job DSNTIJPM should generate these ALTERs for you). The embedded passwords in your DB2 catalog tables are removed using the Change Log Inventory utility (DSNJU003) and the SYSTEMDB command.
2. (Version 6) Stored procedure support. Stored procedures are now managed using the DML statement CREATE PROCEDURE (rather than inserting rows into the SYSPROCEDURES table). Stored procedures defined in SYSPROCEDURES will generate these statements as a part of the DSNTIJMP execution step of migration.
3. (Version 6) Host variables now require leading colons. This could cause serious problems. The precompiler now assumes that a keyword without a leading colon used in the context where a table column may appear is therefore a table column, not a host variable. In some cases (e.g., there is no such table column) the precompiler generates an error message; however, if the name is a valid column name then no error is generated. This is potentially disastrous. You should review all SQL statements and ensure that all host variables have leading colons.
4. (Version 6) RECOVER INDEX is now REBUILD INDEX. This is because you may now use the Copy utility for indexes; hence, the Recover Index utility uses a previously created index image copy.
5. (Version 7) Scrollable cursors are supported. This is a new V7 feature, but it needs to be considered before migration. It is possible that you have applications (such as PeopleSoft) that will begin using scrollable cursors the instant you migrate to V7. Review all such applications prior to migration.
6. (Version 7) Unicode support. Support for Unicode functions is handled through Unicode Systems Services in MVS or z/OS. Ensure that this feature is both installed and enabled with the correct code pages, or you risk some odd application behaviors. (Example: Upon our migration to DB2 V7 our PeopleSoft application began sending requests in Unicode. As we had not implemented Unicode System Services the application received the following cryptic error: 1208 UNKNOWN 3 .)
7. (Version 7) Drop constraint before dropping Index. If an index is being used to enforce a constraint (such as Uniqueness or Primary Key), you must first DROP the constraint before DROPing the Index. An attempt to drop the index first will result in a –669 SQLCODE. This may affect the way you do change management.
8. (Version 7) Utility thread usage. As utilities may now use parallel threads to speed up processing, review your settings for CTHREAD and IDBACK and consider increasing these values.
9. (Version 7) IRLM Proc parameter change. The GROUP parameter in the IRLM address space JCL is now renamed to be IRLMGRP.

In summary, review the release incompatibilities and communicate them to the appropriate stakeholders in your company. In particular:

• Inform application development departments of any SQL-related issues, such as the requirements for leading colons on host variables.
• Inform technical systems support of new co-requisite FMIDs such as Unicode Systems Services.
• Inform database administration of new defaults for Zparms, new utility keywords, and new SQL reserved words.

Pre-migration Activities

Your first step is not ordering the new V7 product. First, do the following:

• Order and apply the fallback maintenance for your current DB2 subsystem. This is also called the DB2 V7 Migration Fallback maintenance, and is documented in Informational APAR II12653. This maintenance permits a successful fallback to your current DB2 version after a failed migration. Schedule a time to apply this maintenance well before you do your migration. Note: This may require IPLs of your systems. Check the PTF descriptions to verify this.
• Order the manuals. These are easily ordered through your IBM representative or via IBM's publications web site (see link at the end of this article). I recommend the following:
• GC26-9936-01 Installation Guide
• SC26-9943-01 Release Planning Guide
• SC26-9945-02 Utility Guide
• GC26-9940-01 Messages and Codes
• SC26-9935-01 Data Sharing Planning and Administration
• (for Data Sharing users)
• SC26-9931-01 Administration Guide

Order other manuals as needed.

• Choose your migration (and fallback!) dates. Depending on your circumstances you may require an extended outage to perform your DB2 V7 migration and (optionally) fall back to the previous version. Make sure that your outage plans contain sufficient time for the following:

1. Complete backup copies of essential data (DB2 Catalog and Directory, BSDSs, critical application data, DB2 Logs, and so forth) prior to the outage. Ensure that these backups are externalized to media and safeguarded.
2. Executing the migration process checklist
3. Fallback to previous DB2 version if migration fails
4. Migration verification steps (including application testing) if migration succeeds
5. Additional backups of essential data

• Prepare a checklist. There are many resources available. Begin with the DB2 V7 Installation Guide, and find the relevant chapter for your migration. Additional information is available from IBM Advanced Technical Support – consult with your IBM representative. Last, review and customize for your shop, adding steps as required and reordering where necessary. For example, we added ADRDSSU backups of critical datasets to the beginning of our checklist.

With these preparations made, you can now proceed with the planning required to properly order your DB2 Version 7 software.

What Exactly Is DB2 Version 7?

DB2 is not a single software product. It is a combination of modules identified by Function Module Identifiers (FMIDs). The usual way to determine what you need is by reviewing the current Preventive Service Planning (PSP) information or by contacting the IBM Support Center. PSP buckets are identified by product levels (the UPGRADE) and FMIDs (called SUBSETs), and should be listed in the Program Directory when you order.

DB2 Version 7 (actually, IBM Database 2 Universal Database Server for OS/390 and z/OS with National Language Versions Version 7) usually comes with the base code and some additional FMIDs, including those for the IRLM, ISPF panels, and a few others. You should also order the following:

• JDB771D Management Clients Package
• JDB771H REXX Language Support

The Management Clients Package (also called the 390 Enabler) includes a workstation CD-ROM with the DB2 Installer, Visual Explain, and DB2 Estimator, as well as a DB2 Connect Personal Edition Kit and the DB2 Control Center. The REXX Language Support FMID will be required if you are accessing DB2 through REXX – the most common example of this is shops that use the Stored Procedure Builder.

In addition, you will most likely be ordering the IBM DB2 utilities (FMIDs JDB771K and JDB771M).

Last, determine if any of your current applications (such as PeopleSoft) may make use of Unicode Systems Services. If so, you should contact your systems programming staff and ensure that the appropriate FMID for your operating system is installed and enabled.

Limit or forbid usage of new features and functions

While some shops are moving to DB2 Version 7 to take advantage of new features, these should be severely limited or forbidden immediately after migration. This allows you to fall back without having to worry about applications that took advantage of a new feature – and now will not work.

There is another, more subtle reason. Allowing or implementing new features without the appropriate infrastructure in place can lead to problems or even administrative nightmares.

Consider WLM-managed stored procedures. You decide to implement these immediately after going to DB2 Version 7. Regrettably, you may stumble over the following problems for the next several weeks or months:

• Some of your stored procedures were implemented with a Service Level Agreement (SLA). You now have to define and construct multiple WLM application environments, possibly prepare JCL Procs for multiple WLM address spaces, and agree on dispatching priorities.
• Some stored procedures generate abends, or worse, begin looping. How will you debug them? Do you have a tool that can display which SPs are running?
• Your production turnover process for stored procedures now requires implementing –STOP and –START PROCEDURE statements, a WLM REFRESH, and perhaps LLA REFRESH. How will you do this?

As another example, you decide to move your EDM Pool to a Data Space. Regrettably, you now find that your third-party tool you use to display dynamic SQL no longer displays the SQL statement text.

So, make sure that you are ready to support any new features or functionality before you implement them.

Tricks and Traps

Here is a grab bag of additional items to consider as part of your migration planning.

• Review all Catalog and Directory image copy jobs. DB2 Version 7 has new Catalog Tablespaces, so you will need to update your normal Catalog / Directory image copy jobs. Note that you will be running both the old and the new versions of these jobs as a part of migration, so they both need to be available.
• Archive the Logs. SYSCOPY records for image copies of DSNDB01.DBD01, DSNDB01.SYSUTILX and DSNDB06.SYSCOPY are placed in the DB2 Log. For maximum security you should issue the –ARCHIVE LOG command after the successful copy to ensure that the DB2 Logs with these records are externalized.
• Review third-party software. Many products only tolerate new DB2 features rather than exploit them. An upgrade to DB2 Version 7 may require upgrades of several third-party vendor software products. List these, contact the vendors, and find out what you need to upgrade. If you use DB2Connect, include an upgrade to this product in your planning.
• Plan for the new catalog. Upgrade your PLAN_TABLEs as a part of migration. Review any SQL that refers to catalog tables for possible changes or performance issues. Review products or procedures that make copies of catalog tables.
• Prepare for CATMAINT execution. DB2 version migration contains a step that runs the CATMAINT utility. In the past some customers had issues with this utility, including long run times and limited recoverability after abends. Luckily, most of these issues are addressed in DB2 Version 7. Execution times are much faster, and failure of the utility during migration will result in a roll back of changes to the Version 6 catalog and directory.

CATMAINT also displays status messages at several points to indicate progress. One remaining issue: While concurrent application processing is allowed during CATMAINT execution, I do not recommend it. CATMAINT makes changes to the DB2 catalog and directory, taking many exclusive locks. This will probably prevent many applications from executing. Ensure a quick, clean run by first stopping all DB2 application activity. Depending upon your system configuration this can be done through a combination of stopping JES initiators, issuing the DB2 command –STOP DDF, closing CICS and IMS connections, and having DB2 up in MAINT mode.

Last, ensure that CATMAINT has enough work space by increasing the size of the associated buffer pools for your Work Database (DSNDB07 for non-data sharing). Use the ALTER BPOOL command and specify a large VPSIZE parameter (your number will vary depending upon the number of objects you have in your catalog – consult with IBM for a specific recommendation).

• Prepare a Migration Verification process. If you do not implement the sample database and applications that are included with DB2, you should have a job stream that tests as much functionality as possible. I recommend that you include tests for the following:
• Several utilities (Load, Reorg, Copy)
• DSNTIAUL, DSNTIAD
• The DSNALI interface
• Distributed connections
• Stored procedures
• Any third-party software products
• Any automated console operations (ex: something you have in place to intercept DSNnnnnn messages and perform some task)
• Other automation (the Resource Limit Facility, third-party products that trap dynamic exceptions, etc.)
• Production program turnover
• Coordinate with Stakeholders to test essential applications. These test will be run immediately after you successfully migrate to DB2 Version 7, but before you open up the system to all applications. The purpose: to determine, as soon as possible, whether fallback to the previous version may be required. There should a description of the tests, specific pass/fail criteria, and clear division of responsibility for performing tests and sign off of their success.

Should Fallback Be Necessary

Despite your plans, fallback may be required. If so, follow the procedure in the checklist. I recommend that your first migrations, usually in your sandbox or development environments, include doing an explicit fallback. Not only does this give you confidence that a fallback can succeed, it allows you to measure how long it takes and any issues you have. This will be important when planning for a production fallback.

The word fallback is not entirely accurate. If you successfully execute the CATMAINT step of your migration, your DB2 catalog and directory are converted to Version 7. Fallback does not undo this; it involves falling back to the DB2 Version 6 (or Version 5) code, but remaining with a Version 7 catalog. That’s what the migration fallback maintenance was for (remember this from the beginning of the article?). You are now executing old code with a new catalog, and the fallback maintenance permits DB2 to function.

So, be aware of this. Fallback may affect you as follows:

• There will be several new catalog tables, and new columns in some tables; hence, any pre-built SQL you have for querying the catalog may need review.
• If you have upgraded any PLAN_TABLEs with the Version 7 columns, Explain will not populate them.
• Use the new version of your catalog / directory image copy JCL, not the old one.
• Third-party software products that query the catalog may not work correctly. Check with your vendors regarding their product’s tolerance for DB2 in a fallback mode.

Conclusion

Constructing a DB2 Version 7 migration checklist is easy; preparing for the migration can be tedious and difficult. However, with proper planning you can make it an almost enjoyable experience. Good luck!

Additional Information

Web Links of Interest

IBM DB2 for z/OS and OS/390 Support Page

Availability summary for DB2 for z/OS, OS/390, and MVS/ESA

IBM Publications Center (for ordering manuals)

Summary of Service Dates

• DB2 for z/OS and OS/390 service dates
• DB2 UDB for OS/390 Version 6 is Withdrawn from Marketing as of 30 June 2002. DB2 OS/390 Version 5 End of Service date is 31 December, 2002. Migration from Version 5 to Version 7 is supported.

--

Lockwood Lyon is a Systems and Database Performance specialist in the Midwest. He has over twenty years of experience in Information Technology as an IMS and DB2 database analyst, systems analyst, manager, and consultant. Most recently he's spent quite a lot of time on DB2 subsystem installation and performance tuning. Lockwood is the author of, MIS Manager's Appraisal Guide (McGraw-Hill 1993), Migrating to DB2 (John Wiley & Sons 1991), and The IMS/VS Expert's Guide (Van Nostrand 1990).