IBM Books

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

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

Security and Authorization

You need SYSADM authority to migrate your database.

If migrating from DB2 Version 1, you should know that a database cannot be cataloged with a mix of authentication types. The authentication type of the instance is, in Version 5, defined in the database manager configuration file. If mixed types are detected during migration from Version 1, you can either stop the migration and change the directories or continue with the migration. If migration continues all the authentication types are changed to blank, and the database uses the authentication type specified in the instance.

To use two databases with different authentication types, a new instance must be created for one of the databases. The database should be backed up and restored to a new database under the new instance. It can then be dropped under the old instance and migration can then be run.

Beginning with Version 2, users and groups are differentiated in SQL statements and the system catalog. As a result, if a user and a group have the same name in the previous version, the authority and privileges granted to the group must be explicitly re-granted after migration.

During migration, the authorization catalog tables, SYSCAT.DBAUTH, SYSCAT.INDEXAUTH, SYSCAT.PLANAUTH, and SYSCAT.TABAUTH, are checked to determine if existing privileges are for users or groups, and the GRANTEETYPE is defined as follows:

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:

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.

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:

    You cannot migrate a 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 either DATALINK or REFERENCE as the name of the type.

    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:

  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 5. 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 V5 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 V5 is complete are not recoverable. That is, if sometime following the completion of the installation and migration to V5, the database needs to be restored (to a V5 level), the logs from before V5 installation cannot be used in roll-forward recovery.


  5. Migrate the database using one of the following:

    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 V5, 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 5. (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 5 format at some later time. The DB2MIGDR utility allows you to complete this 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 5 semantics. 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 5 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. The REBIND PACKAGE or BIND commands will selectively bind a particular package

  9. Tune your database and database manager configuration parameters to tak e advantage of Version 5 enhancements.

  10. Optionally, migrate Explain tables if you have been using the Explain tables and are planning to use them in Version 5. There are several new columns in the tables. See Chapter 14. "SQL Explain Facility" and Appendix M. "Explain Tables and Definitions" for more information.

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

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]