Version 5 of DB2 supports deferred unique constraint checking until end of 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:
- Log in with a user ID that has SYSADM authority.
- Issue the db2start command.
- 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.
- 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.
- 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.
DB2 interprets the re-creation of an already-existing unique index to signal that the index is ready to be converted to Version 5 semantics.
When database migration is completed, the old statistics, 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.
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.
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.
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.
- Number of concurrent databases running against the database manager
(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 (tmdatabase)
If the migrating database configuration file has this parameter set to NULL, the parameter is reset to 1ST_CONNECT.
- Query heap size (queryheapsz)
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.
- Maximum number of idle agents (idleagents)
If the migrating database configuration file has this parameter greater than the Maximum Simultaneous agents (maxagents) of the same file, the parameter is reset to 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.
- 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 Version 5 Command Reference for the syntax of updating database and database manager configuration.
 |
If you are not using explain tables in DB2 Version 2, 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; see 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.