Thinking about upgrading to a new version of DB2 for z/OS? Of course you are!
Many DB2 shops using DB2 z/OS version 8 or version 9 are considering migrating to a later DB2 version in 2012. DB2 z/OS Version 8 will be out-of-service on April 30, 2012, which is driving some of these upgrades.
What are the biggest migration concerns? Rather than concentrate on specific features and benefits of a new version, the DBA should concentrate on projects and strategies.
Here are the top ten migration issues. For each issue, I recommend starting a separate mini-project, complete with project manager, project plan and timeline. Of course, multiple projects can be handled simultaneously by a single IT professional depending upon how much work you estimate will be required and your available IT staff.
#10 – Technical Training
A mainframe software upgrade involves somewhat more than simply inserting a CD in a drive and clicking Next. DBAs and systems programmers having experience with DB2 version migration are familiar with most of the migration issues, and you’re lucky if your staff is in this position.
For many IT shops, DB2 migration will involve multiple software upgrades, including third-party tools, distributed connections (such as WebSphere and DB2Connect), and perhaps even the operating system. This means several departments must communicate and coordinate their efforts. In addition, someone must be responsible for consistent documentation and education about the migration process and the new DB2 environment.
Typical tasks include: Gathering DB2 product documentation; presenting and updating internal procedures such as change control and JCL standards; and making available training to the application development teams (including new features to be used or deferred).
# 9 – New Version Feature Exploitation
Are you planning on using any new features? Is anyone else in your organization planning this? Are applications counting on certain features to be available immediately after your migration? This project is a planning effort to coordinate all these things.
Note the prerequisites and requirements for any features that you intend to use and begin planning for them now. You should also review the following:
- All application SQL that access the DB2 catalog
- Use of byte-based functions such as SUBSTR that may be used on Unicode data
- Enhancing SQL procedures to use SIGNAL and GET DIAGNOSTICS
- Converting tables using Identity columns to use sequences instead
# 8 – New Version Features: Not Right Now but Eventually
There are probably new features you may want to postpone supporting. While these are nice things to plan for, they should wait until you have comfortably moved to new function mode and have procedures in-place to support them.
This decision is entirely up to you. Consider: Will your third-party tools handle these features properly?
# 7 – DB2 Version Migration Metric
How long will your initial migration take? Get an idea now, before your production migration.
One way is to create a production-like environment in your sandbox subsystem. Migrate to the new version and monitor your timeline and progress. Realize that the sandbox and production systems probably run on different boxes with different resource availability, so factor in some extra time for production. Don’t forget to include a backout task in your production migration in case things go wrong.
# 6 – Access Path Backup
After migration is complete, what happens if one or more applications or queries begins to take an excessively long time to complete? One possibility is that the access paths of one or more SQL statements has changed. What to do?
If you don’t have one already, create a process for saving your critical access paths. The primary way to save access path information is by gathering and saving IFCID 0022 records. IFCID 0316 records may be saved too, as these contain information on dynamic statement caching.
Another method is to save EXPLAIN information for static SQL embedded in applications. Ensure that your production BIND process includes using the EXPLAIN(YES) parameter and saving the access paths in a centralized PLAN_TABLE. If any access path is radically changed after version migration, you can use the saved plan table rows as Hints for the optimizer.
# 5 – Current System Health Check
Check the health of your current DB2 subsystems prior to the migration. This includes the following:
- DB2 Catalog and Directory health (see the DSNTESQ member in SDSNSAMP)
- Search for incompatibilities and unsupported objects (see the Installation Guide)
- Analyze table and index health
The last item is important. Prior to making major configuration or software changes to a Windows personal computer it is a best practice to back up the Registry, remove most temporary and unneeded files, and perhaps even defragment your hard drive. Similarly, prior to a DB2 version upgrade you should take the opportunity to scope out your data and indexes and minimize or mitigate redundancies or other issues.
Here are some examples based on my migration experiences. Your system configuration and table and index allocations and usage may vary, so use this list to generate ideas for your own health checks. Refer to the manuals for technical terms.
Tablespaces
- Partitions with many relocated rows
- System tablespaces in more than 10 extents
- User tablespaces in more than 50 extents
- User tablespace image copy datasets more than 31 days old
- Tablespaces with data compression enabled, but less than 5% space savings
Indexes
- User indexes in more than 10 extents
- System indexes in more than 10 extents
- Indexes with a high percentage of out-of-position RIDS
- Index partitions with a large LeafDistance
- Clustering indexes with a poor cluster percentage
- Indexes with low cardinality leading columns
- User-defined indexes on system catalog tables
- Indexes with FullKeyCard less than 1 percent of table cardinality
Other
- Referential integrity relationships with no supporting index for foreign keys
- Packages having excessive versions
- Stored procedures with non-standard run options
- Plans and packages that have not been recently bound
- Tables granted access to PUBLIC
# 4 – Third-party Software Products Review
Many third-party software vendors have taken advantage of early releases of DB2 to upgrade their products. Since there are a lot of new features and functionality in later versions, it’s possible that some software products will tolerate only a subset of the new features.
You need to review your list of current and future products, review how you use them, contact the vendors, and ensure that any product upgrades occur at the appropriate time.
# 3 – New Version Performance Measurement Metrics
How will things run after your migration? Find out in advance by setting up a system performance test with appropriate metrics. You can use a combination of the DB2 sample jobs that come with DB2, along with a selection of your own critical applications.
Begin by preparing your test suite of applications, including your process for performance measurement. Typical measurements might include CPU used, I/Os, memory usage, and network traffic.
Run initial tests several times to control for one-time resource costs such as VSAM data set opens, buffer pool population, compression dictionary loading, Environmental Descriptor Manager (EDM) pool loading, dynamic statement cache loading, and so forth. One simple way to do this is to run your suite several times and use only the last measurement as your baseline.
# 2 – Disaster Recovery Process Update
While the probability is low that a disaster will strike right in the middle your migration, that’s no excuse for skipping planning.
Incorporate your migration plans into current disaster recovery plans immediately
You should also take time to update your backup and recovery procedures with any new objects associated with the new version.
# 1 – Implement Best Practices
There’s lots of literature on database administration best practices. The most important items are:
- Document all processes, including FAQs, how-tos, and standards
- Centralize the documentation
- Index the documentation, make it available to all
- Regularly review documentation, ensure it is up-to-date
- Regularly review processes and process documentation
- Define process quality measures; collect and analyze the metrics
Summary
Migrating to a new version of DB2 means a major transformation in the way the DBA works and how applications are developed. The advent of new features and functions changes multiple processes, including application development, change control, and third-party support tools. To ensure that your final implementation is successful, begin planning now.
Source(s):
IBM DB2 Technical Resources 2011
Surekha Parekh The World of DB2 2012
BM Db2 for z/OS Library – White Papers 2010-2012
