Quick Beginnings for UNIX**

Optional Post Migration Actions

There are optional activities you may want to undertake following database migration. You can also apply these optional activities to a down-level database backup which is restored to Version 6, because at the end of the restore, the database is migrated to Version 6.

• Migration of Unique Indexes (db2uiddl)

  DB2 Versions 5 and 6 support deferred checking for duplicate index key values until the end of UPDATE statements. This ensures that temporary duplicate index key values which may be present in mid-UPDATE, but no longer are present at the end of the UPDATE, will not cause the statement to fail.

  With DB2 Version 2 the same UPDATE statement may fail because checking for duplicate key index values is performed row by row as the statement processes the table. For example, if a row with value 1 is changed to value 2, but a row with value 2 already exists, a duplicate value 2 will be detected causing the DB2 Version 2 UPDATE statement to fail.

  Version 2.x and 5.x unique indexes are not automatically migrated to Version 6 semantics for several reasons:

  • Converting unique indexes is a very time-consuming operation.

  • You may have applications that depend on the previous version's unique index semantics.

  • You may want to manage the staged conversion of unique indexes on your own schedule, when needed, using the db2uiddl command.
  
  

  All existing applications will continue to work even if the unique indexes are not converted to Version 6 semantics. You have to convert unique indexes to Version 6 semantics only if support for deferred uniqueness checking is required.

  To convert unique indexes, you need to perform the following steps:

  step  1.	Log in with a user account that has SYSADM authority. See Working with the System Administrative Group for more information.
  step  2.	Start the database manager by entering the db2start command.
  step  3.	Run the db2uiddl command against your migrated database. Refer to the Command Reference for the syntax of this command. The db2uiddl command searches the database catalog tables and generates all the CREATE UNIQUE INDEX statements for user tables in an output file.
  step  4.	Review the output generated from the db2uiddl command. We recommend that you remove any unwanted indexes from the output file to reduce the time needed to execute it. Comments in the output will flag other situations that require your attention.
  step  5.	Connect to the database by entering the db2 connect to database_alias command. where database_alias is the alias of the database you are migrating.
  step  6.	Execute the output file, generated by the db2uiddl command, as a DB2 Command Line Processor command file, using a command similar to the following: db2 -tvf filename where filename is the name of the file generated by the db2uiddl command.
  step  7.	Disconnect from the database by entering the db2 connect reset command.

  DB2 interprets the re-creation of an existing unique index using the db2uiddl command to signal that the index is ready to be converted to Version 6 semantics.

• Update Statistics

  When database migration is completed, the old statistics that are used to optimize query performance are retained in the catalogs. However, Version 6 of DB2 has statistics that are modified or do not exist in Versions 2.x or 5.x. To take advantage of these, you may want to execute the runstats command on tables, particularly those tables that are critical to the performance of your SQL queries.

  Refer to the Command Reference for the syntax of the runstats command. For details on the statistics, refer to the Administration Guide.

• Rebind Packages

  During database migration, all existing packages are invalidated. After the migration process, each package is rebuilt when it is used for the first time by the Version 6 database manager.

  For better performance we recommend that you run the db2rbind command to rebuild all packages stored in the database. In DB2 Version 6 this command has a new option, all, which, when specified, rebinds all packages (valid and invalid). If the all option is not specified with the db2rbind command, only those packages marked as invalid are rebound. Refer to the Command Reference for the syntax of this command.

• Update database and database manager configuration

  Some of the database configuration parameters are changed to Version 6 defaults or to other values during database migration. The same is true for database manager configuration parameters which may have changed to Version 6 defaults or to other values. Refer to the Administration Guide for more information about configuration parameters.
  

  We recommend that you run the DB2 Performance Monitor for suggestions in choosing appropriate configuration parameters. For more information, refer to the Administration Guide.

• Migrate Explain Tables

  To migrate the explain tables in a database that has been migrated to Version 6, run the following command:

     db2exmig -d dbname -e explain_schema [-u userid password]
  

  where:

  • dbname represents the database name. This parameter is required.

  • explain_schema represents the schema name of the explain tables to be migrated. This parameter is required.

  • userid and password represent the current user's ID and password. These parameters are optional.

  The explain tables belonging to the user ID that is running db2exmig, or that is used to connect to the database, will be migrated. The explain tables migration tool will rename the Version 2 or Version 5 tables, create a new set of tables, using the EXPLAIN.DDL, and copy the contents of the old tables to the new tables. Finally, it will drop the old tables. The migration utility, db2exmig, will preserve any user added columns on the explain tables.

[ DB2 List of Books | Search the DB2 Books ]