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 V5:

• Migration is only supported from V1.x or V2.x. Earlier versions of DB2 (Database Manager) must be migrated to V1.x or V2.x before attempting to migrate to V5.

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

• Migration between platforms is not supported.

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

• Database objects 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 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 (V1.x or V2.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.

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:

• If the name in the GRANTEE column is a user or is not defined; the GRANTEETYPE is defined as U.
• If the name in the GRANTEE column is a group; the GRANTEETYPE is defined as G.
• If the name in the GRANTEE column is both a user and a group; the GRANTEETYPE is defined as U. Privileges must then be explicitly granted to the group.

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:

• View Definitions

  If a view from before Version 2 involves "SELECT *", the view may be unusable after migration. If the view is unusable, attempts to use it, directly or indirectly, will result in SQLCODE -158. The view must be dropped and recreated in order to avoid this error. If fewer than the current number of columns in the SELECT * table is desired, the recreated view must specify the needed columns.

• Configuration Parameters

  Configuration parameter values are preserved during the migration of the database, with the exception of the following parameters:

  • "Application Heap Size (applheapsz)"
  • "Package Cache Size (pckcachesz)" (applies to all platforms except DB2 for OS/2 V1.x.)
  • "Maximum Storage for Lock List (locklist)"
  • "Recovery Range and Soft Checkpoint Interval (softmax)".

  For these parameters, the use of the associated heap has changed significantly in Version 5.

  • applheapsz is reset to the Version 5 default value if the current value is less than the Version 5 default value.
  • pckcachesz is always reset to the Version 5 default value.

  For locklist, the DB2 Version 1 or DB2 Parallel Edition Version 1.2 value is multiplied by a factor of 32/25. This computed value will be used as the Version 5 parameter value, if this value is greater than the Version 5 default. Otherwise, the Version 5 default will be used.

  OS/2 Users: If you are migrating from Version 1, parameters previously allocated in units of 64 KB segments are multiplied by 16 to allow for allocation in units of 4 KB pages. In addition, the softmax configuration parameter will be set to the default value, since this parameter is now measured as a percentage of the log records written rather than the number of log records written.

  In order to take advantage of Version 5 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 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:

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

   • Correct database connection states.

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

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.

   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.

     Note:

     To restore a Version 1 OS/2 database backup, you must use DB2RESDB.

   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.

   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 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.

[ DB2 List of Books | Search the DB2 Books ]