Administration Guide

Migration Considerations

To successfully migrate a database created with a previous version of the database manager, you must consider the following:

• "Migration Restrictions"
• "Security and Authorization"
• "Storage Requirements"
• "Release-to-Release Incompatibilities"

Migration Restrictions

There are certain pre-conditions or restrictions that you should be aware of before attempting to migrate your database to V6:

• Migration is only supported from V2.x or V5.x. Migration from DB2 V1.2 Parallel Edition is not supported; only DB2 UDB EEE V5.x is supported. Earlier versions of DB2 (Database Manager) must be migrated to V2.x or V5.x before attempting to migrate to V6.

• Issuing the migration command from a V6 client to migrate a database on a V6 Server is supported. However, issuing a migration command from earlier versions of DB2 clients to a V6 Server is not supported.

• Migration between platforms is not supported.

• User objects within your database cannot have V6 reserved schema names as object qualifiers. These reserved schema names include: SYSCAT, SYSSTAT, and SYSFUN.

• Database objects in DB2 Version 2.x with a dependency on the SYSFUN.DIFFERENCE function must be dropped before migrating the database. Objects that might have a dependency on this function include: views, constraints, functions, and triggers.

• User-defined distinct types using the names BIGINT, REAL, DATALINK, or REFERENCE must be renamed before migrating the database.

• Your database cannot be in one of the following states:

  • Backup pending

  • Roll-forward pending

  • One or more table spaces not in a normal state

  • Transaction inconsistent

• Restore of down-level (V2.x or V5.x) database backups is supported but rolling-forward of down-level logs is not supported.

Security and Authorization

You need SYSADM authority to migrate your database.

Storage Requirements

Space is required for both the old and new catalogs during the migration, and the amount of disk space required will vary depending on the complexity of the database as well as the number and size of the database objects. These objects include all tables and views. You should make available at least two times the amount of disk space as the database catalog currently occupies. If there is not enough disk space, migration fails.

You should also consider increasing the database configuration parameters associated with the log files. You should increase logfilsiz, logprimary, and logsecond to prevent the space for these files from running out. If log space is completely used, you will receive a SQLCODE of SQL1704N with a reason code of 3. If this happens, increase the log space parameters and re-issue the database migration command.

Release-to-Release Incompatibilities

To successfully migrate a database, you should consider the impact of the incompatibilities between the two versions of the product. The following incompatibilities deserve special attention before you begin your migration:

Configuration Parameters

When migrating from Version 2.x to Version 6, a small number of parameters are not preserved due to a change in the behavior of the associated heap.

In order to take advantage of Version 6 enhancements, you should re-tune your database manager and database configuration after migrating your databases. To assist in this tuning, you may wish to record and compare configuration parameter values from before and after your migration. (See the GET DATABASE CONFIGURATION and GET DATABASE MANAGER CONFIGURATION commands in the Command Reference manual.)

Migrating a Database

The following are the steps you must take to migrate your database. The database manager must be started before migration can begin.

PRE-MIGRATION:

Note:

The pre-migration steps must be done on a previous release (that is, on your current release before migrating to, or installing, the new release).

1. You cannot migrate a database that is in one of the following states:
   • Backup pending
   • Roll-forward pending
   • One or more table spaces not in a normal state
   • Database inconsistent

   You cannot migrate a Version 2.x database that contains any database objects with a dependency on scalar function SYSFUN.DIFFERENCE.

   In addition, you cannot migrate a database that contains any database objects which have a qualifier (schema name) of SYSCAT, SYSSTAT, and SYSFUN. These schema names are reserved for use by the database manager.

   You cannot migrate a database where there are user-defined distinct types using BIGINT, REAL, DATALINK, or REFERENCE as the name of the type.

   You cannot migrate a database where there are table spaces with containers which were defined using an absolute database path.

   See the Quick Beginnings for information about migrating from previous releases, and for information about functions to help with the above step of the migration process. This book also introduces when and how to use the DB2CKMIG pre-migration utility.

2. All applications and end users must be disconnected from each database being migrated. Use the LIST APPLICATIONS and the FORCE APPLICATIONS commands as necessary.

3. Use the DB2CKMIG pre-migration utility presented in the Quick Beginnings for your platform to check to see if the database can be migrated. Re-use the utility until there are no more errors. Typical corrections include:

   • Drop and re-create objects using valid schema names.

   • Redefine containers so that absolute database paths are not used.

   • Correct database connection states.

   • Remove all dependencies from objects on scalar function SYSFUN.DIFFERENCE.

   • When a structured type and a user-defined function both live in the same schema, have identical names, and the function is a zero-argument function, there can be problems. As an example, migration might attempt to create a zero-argument constrictor function for the structured type that returns an initialized instance of the type. The result would be that this function would clash with the existing user-defined function (UDF) of the same name.

   • Drop and recreate objects using invalid user-defined distinct types, named BIGINT, REAL, DATALINK, or REFERENCE.

4. Backup your database.

   Migration is not a recoverable process. If you backup your database before the Version 5 restricted schema names are changed, you will not be able to restore the database from backup using DB2 Version 6. To restore the database, you will have to use the version of the database manager from which you are migrating your databases.

   Attention! If you do not have a backup of your database from before you attempted migration, and the migration failed, you will have no way of restoring your database using DB2 V6 or your previous version of the database manager.

   You should also be aware that any database transactions done during the period between the time the backup was completed and the time the upgrade to V6 is complete are not recoverable. That is, if sometime following the completion of the installation and migration to V6, the database needs to be restored (to a V6 level), the logs from before V6 installation cannot be used in roll-forward recovery.

   MIGRATION:

5. Migrate the database using one of the following:
   • The SQLEMGDB migrate database API
   • The MIGRATE DATABASE command-line processor command
   • The RESTORE DATABASE command, when restoring a full backup of the database.

   OS/2 Users: The DB2CIDMG migration program, which works in a Configuration/Installation/Distribution (CID) architecture environment, is only available on DB2 for OS/2. It allows for remote unattended installation and configuration on LAN-based workstations. You must have NetView DM/2 on your LAN to use CID migration.

   UNIX Users: The Quick Beginnings describes what to do if you do not want to migrate all databases in a given instance.

   Note:

   During installation of V6, all of the found local database directories are migrated. It may be that you require keeping one of your current local database directories past the time of the installation of Version 6. (For example, your operating system may allow a dual boot feature: where you can have the original version of DB2 when "booting"your system one way, and the new version when "booting" the other way.) If you keep your current directories, then you may need a way to migrate that database directory to the Version 6 format at some later time. The DB2MIGDR utility allows you to complete this migration.

   POST-MIGRATION:

6. Optionally, use the DB2UIDDL utility to assist in searching all unique indexes from the migrated database. This utility creates a file containing a list of CREATE UNIQUE INDEX statements. Executing this file as a DB2 CLP command file results in the unique index being converted to Version 6 semantics. This also includes creating the indexes with bi-directional pointers which are of benefit when scanning through leaf nodes to retrieve ascending or descending ranges of values. Refer to the Quick Beginnings manuals for more details.

7. Optionally, issue RUNSTATS on tables that are particularly critical to performance of SQL queries. Old statistics form the previous level database are retained in the migrated database. Therefore, any new statistics that are modified for, or are new to, Version 6 will not be added to the migrated database unless you issue RUNSTATS.

8. Optionally, rebind all packages. If migrating from a Version 2 database, there may be inoperative packages. Inoperative packages remain identified as inoperative following migration. All existing valid packages are marked as invalid during catalog migration. You can use the DB2RBIND utility to revalidate all packages, or allow package revalidation to occur implicitly when a package is first used. DB2RBIND has an argument called "all" which, when specified, rebinds all valid and invalid packages. The REBIND PACKAGE or BIND commands will selectively bind a particular package.

9. Tune your database and database manager configuration parameters to take advantage of Version 6 enhancements.

10. Optionally, migrate Explain tables if you have been using the Explain tables and are planning to use them in Version 6. There are several new columns in the tables. Refer to the "SQL Explain Facility" and the "Explain Tables and Definitions" in Administration Guide, Performance for more information. The Quick Beginnings manuals have details on migrating Version 2.x and Version 5.x Explain tables to Version 6.

Complete details on the migration steps are found in the Quick Beginnings manuals for your platform.

[ DB2 List of Books | Search the DB2 Books ]