Quick Beginnings for DB2 Extended Enterprise Edition for UNIX

Post Migration

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 5, because at the end of the restore, the database is migrated to Version 5.

• Unique index conversion to DB2 Universal Database Version 5 semantics

  Version 5 of DB2 supports deferred unique constraint checking until the end of a statement. This can result in correct processing of multiple row updates, that in previous releases of DB2, returned an error because the updates temporarily created duplicate values in the transient state. Deferred unique constraint checking will guarantee that updates that result in a table with only unique keys (for example, key = key + 1) will succeed, regardless of the order of the data.

  Note:

  This change only applies for unique indexes that are created in Version 5 of DB2.

  All unique indexes in a migrated database do not automatically migrate to Version 5 semantics during database migration because of the following 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.
  
  

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

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

  1. Log in with a user ID that has SYSADM authority.

  2. Run the db2start command.

  3. Run the db2uiddl command against your migrated database. Refer to the Version 5 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.

  4. Review the output generated from the db2uiddl command, and make changes, if needed. Comments in the output will flag any situations that require your attention.

  5. Connect to the database by issuing the db2 connect to database_alias command.

  6. Execute the file as a DB2 Command Line Processor command file, using a command similar to the following:

        db2 -tvf filename
     

     where filename is an output file from db2uiddl.

  7. Disconnect from the database by issuing the db2 connect reset command.

  DB2 interprets the re-creation of an existing unique index to signal that the index is ready to be converted to Version 5 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 5 of DB2 has statistics that are modified or do not exist in the previous version. To take advantage of these, you may want to issue the runstats command on tables, particularly those tables that are critical to the performance of your SQL queries.

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

• Rebind Packages

  During database migration, all existing packages are invalidated. After the migration, each package is rebuilt when it is used for the first time by the Version 5 database manager. However, for better performance, we recommend that you run the db2rbind command to rebuild all packages stored in the database, after database migration is complete. Refer to the Version 5 Command Reference for the syntax of this command.

• Update database and database manager configuration

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

  The following database manager configuration parameters are changed to Version 5 defaults:

  • Sort heap threshold (sheapthres)

    If the migrating database configuration file has this parameter at a value which is less than the Version 5 default, the parameter is reset to its Version 5 default value.

  • Backup buffer (backbufsz)

    If the migrating database configuration file has this parameter at a value which is less than the Version 5 minimum, the parameter is reset to its Version 5 minimum value.

  • Restore buffer (restbufsz)

    If the migrating database configuration file has this parameter at a value which is less than the Version 5 minimum, the parameter is reset to its Version 5 minimum value.

  • Maximum number of concurrent active databases (numdb)

    If the migrating database configuration file has this parameter at a value which is greater than the Version 5 maximum, the parameter is reset to its Version 5 maximum value.

  • TM-database (tm_database)

    If the migrating database configuration file has this parameter set to NULL, the parameter is reset to 1ST_CONNECT.

  • Query heap size (query_heap_sz)

    If the migrating database configuration file has this parameter at a value less than the Application Support Layer heap size (aslheapsz) of the same file, the parameter is reset to aslheapsz + 1.

  • Agent pool size (num_poolagents)

    If the migrating database configuration file has the value of this parameter greater than the value of the Maximum Number of agents (maxagents) of the same file, the parameter is reset to the value of maxagents - 1.

  The following database configuration parameters are changed to Version 5 defaults:

  • Application Control heap size (app_ctl_heap_sz)

    If the migrating database configuration file has this parameter at a value which is less than the Version 5 default, the parameter is reset to its Version 5 default value.

    Note:

    This only applies to DB2 Parallel Edition database migration to DB2 Version 5, because this parameter exists in DB2 Parallel Edition Version 1.x databases only. For other downlevel databases, this parameter is set to its default value after migration completes.

  • Lock List (locklist)

    For Version 1 DB2 databases, the lock list parameter will be first adjusted to locklist * 32 / 25. If the migrating database configuration file has this parameter at a value less than the Version 5 default value, the parameter is reset to its Version 5 default value.

  • Database Heap (dbheap)

    For Version 1 DB2 databases, the database heap size will first be adjusted to dbheap * 16. If the migrating database configuration file has this parameter at a value that is less than the Version 5 default, the parameter is reset to its Version 5 default value.

  • Default log space (logfilsiz)

    The database migration process will attempt to increase the logfilsiz value, if the log file related parameters have a total logfilsiz that is less than the default logfilsiz value.

  • Application Heap size (applheapsz)

    For Version 1 DB2 databases, application heap size will be first adjusted to applheapsz * 16. If the migrating database configuration file has this parameter at a value which is less than the Version 5 default, the parameter is reset to its Version 5 default value.

  • Sort heap (sortheap)

    For Version 1 DB2 databases, the value of the sort heap parameter will be adjusted to sortheap * 16.

  For better performance, you may want to tune your database and database manager configuration parameters to take advantage of Version 5 enhancements. Refer to the Command Reference for the syntax of updating database and database manager configuration.

• Migrate Explain Tables
  

  If you are not using explain tables in the version of DB2 that you are migrating, skip this task.

  Version 5 of DB2 has added several new columns to the explain tables. These columns provide for the capture of:

  • Data for new SQL features added in Version 5
  • More detailed access plan information.

  While the explain function in Version 5 will continue to work with explain tables created for Version 2, the new Version 5 data will not be captured in them.

  For better performance of SQL statements, we recommend that the Version 2 explain tables be dropped and new explain tables be created; refer to the SQL Reference and the Administration Guide for details on creating new explain tables. If, however, there are Version 2 explain tables that you need for ongoing comparison, you can use the EXPLMIG.DDL script to migrate them.

  To migrate the explain tables in a database that has been migrated to Version 5, connect to the database and run the following command from the sqllib/misc directory:

     db2 -tf EXPLMIG.DDL
  

  The explain tables belonging to the user ID that is used to connect to the database will be migrated. To migrate explain tables belonging to another user, connect to the database with that user ID and run the command.

[ DB2 List of Books | Search the DB2 Books ]