Upgrade impact from DB2 command changes

In base tables, the dependent MQTs output now includes a new field called MQT Flags.

This command checks for type-1 indexes and generates a script file using the REORG TABLE command to convert type-1 indexes to type-2 indexes. Type-1 indexes are not supported in DB2 Version 9.7. See Converting type-1 indexes to type-2 indexes for details.

This command now requires that the instance owning the databases that you want to verify is running. You no longer have to stop the instance to run this command. If the instance is not started, the db2ckupgrade command returns the SQL1032N error message.

If you create databases in DB2 Version 9.7, you have to grant the required authorization to users that need to run these commands or grant DBADM authority to the SYSADM group to maintain the same authorization as in previous releases.

On Linux and UNIX operating systems, these commands now write to a new file called INSTHOME/sqllib/log/db2instance.log, where INSTHOME is the home directory for the instance owner. This file acts as a history file that records the instance activities such as create (db2icrt), drop (db2idrop), update (db2iupdt) and upgrade (db2iupgrade) and is only intended for DB2 support use.

If you issue the db2iupgrade or db2iupdt command with the -u parameter to indicate a different fenced ID than the previously defined fenced ID, this different fenced ID overwrites the previously defined one. In previous releases, the -u parameter was ignored.

In a partitioned database environment, the db2iupgrade and db2iupdt commands update the global registry for all available database partitions. This means if the instance is updated or upgraded successfully, the command removes the instance from the DB2 registry under the DB2 copy location where the instance was running and adds it under the DB2 copy location from which you are running the command. If a database partition is not available, you will receive the DBI1165E error message and the DBI1383I warning message.

On Linux and UNIX operating systems, these commands now write to a new file called INSTHOME/sqllib/log/db2instance.log, where INSTHOME is the home directory for the instance owner. This file acts as a history file that records the instance activities such as create (db2icrt), drop (db2idrop), update (db2iupdt) and upgrade (db2iupgrade) and is only intended for DB2 support use.

Starting in Version 9.7 Fix Pack 6, the -e parameter generates CREATE SCHEMA DDL statements for implicitly created schemas by default. If you specify the new -noimplschema parameter with the -e parameter, the CREATE SCHEMA DDL statements for implicitly created schemas are not generated, which was the default behavior in previous Version 9.7 fix packs and in previous releases.

For automatic storage table spaces, the -storagepaths parameter output now shows the new PathID and PathState columns to indicate the storage path identifier and current state. Also, the -tablespaces parameter output now shows the new PathDropped and PathID columns to indicate whether one or more containers reside on a storage path that have been dropped and the storage path identifier for the container.

For temporary tables, the -tcbstats parameter output now shows three new SchemaNm, StoredBytes, and BytesSaved columns. Also, the -tablespaces parameter output now shows the new Max HWM column to indicate the maximum HWM for DMS table spaces since the instance was started. For SMS table spaces, the HWM and Max HWM columns do not display any value.

For partitioned tables, the -tcbstats parameter output now shows the new PartID column to indicate the data partition ID in the TCB Index Information section and the TCB Index Stats section.

If you are converting type-1 indexes to type-2 indexes, you are also converting the unique indexes created on your database before DB2 UDB Version 5 and you do not have to run the db2uiddl command.

If you create databases in DB2 Version 9.7, grant the required authorization to users that need to run these commands. You can grant DBADM authority to the SYSADM group and grant DATAACCESS authority to users holding DBADM authority to maintain the same authorization as in previous releases.

When you create a database in DB2 Version 9.7, the DB2DETAILDEADLOCK event monitor is created by default as in previous releases. However, it is deprecated and might be discontinued in a future release. You should disable DB2DETAILDEADLOCK and remove it. Use the new lock event monitor instead before the DB2DETAILDEADLOCK event monitor is discontinued. See Monitoring locking data for details.

If you must stop the database immediately, you can issue the FORCE APPLICATIONS ALL command after the DEACTIVATE DATABASE command to end all existing connections. In previous releases, the DEACTIVATE DATABASE command stopped HADR standby databases because no user connections were allowed.

When you connect to Pre-Version 9.7 databases from Version 9.7 clients, use the DECOMPOSE XML DOCUMENT command instead. The DECOMPOSE XML DOCUMENTS command decomposes XML documents stored in a binary or XML column while the DECOMPOSE XML DOCUMENT command decomposes XML documents stored in files.

If you use the new RELATIONAL DATA INDEXES FOR TABLE parameter to list only relational indexes, the output displays the same columns as in previous releases.

The DESCRIBE INDEXES command output now shows an new INDEX PARTITIONING column to indicate whether an index is partitioned or non-partitioned.

The DESCRIBE DATA PARTITIONS command with SHOW DETAIL parameter now shows the new IndexTblSpId column to indicate the table space identifier where index data is stored.

Refer to the BIND command row for additional authorization changes that also apply to the DESCRIBE command.

Refer to the BIND command row for additional authorization changes that also apply to the EXPORT command.

You must also manage changes to the IMPORT and LOAD command that impact importing or loading files that you exported in previous releases. Refer to the Command Reference for details about changes to the IMPORT and LOAD command.

The Worksheet file format (WSF) is deprecated and support will be removed in a future release. Start using a supported file format instead of WSF before support is removed. After the tables are exported using a supported file format, continue to use the IMPORT and LOAD command to populate your tables.

The CREATE and REPLACE_CREATE modes of the IMPORT command are deprecated. Use DDL scripts that you developed or generated with the db2look command to create the table before you issue the IMPORT command.

Refer to the BIND command row for additional authorization changes that also apply to the DESCRIBE command.

If you issue the LOAD command with the REPLACE mode and the RESETDICTIONARY keyword on a table that has XML data in a Version 9.7 XML storage object and row compression enabled, this command now builds a compression dictionary for the XML data in addition to the dictionary for the table data. The compression dictionary for the XML data is stored in the XML storage object object.

If you have tables with XML columns that you created in Version 9.5 or earlier releases, issuing the REDISTRIBUTE DATABASE PARTITION GROUP command returns the SQL1412N error message. You must convert the existing XML storage to the Version 9.7 format. See Converting XML storage objects to the Version 9.7 format for details.

The CONVERT keyword is deprecated because type-1 indexes are discontinued. You should convert your type-1 indexes to type-2 indexes before upgrading your data server. See Converting type-1 indexes to type-2 indexes for details.

If you specify the INPLACE keyword without NOTRUNCATE TABLE, you might want to adjust your outage window because the time locks are held on the table is reduced during the truncate table phase. The truncate table phase is faster than in previous releases, especially on sparsely populated tables.

If you issue the REORG TABLE command with the LONGLOBDATA keyword and the KEEPDICTIONARY or RESETDICTIONARY keyword on a table has XML data in a Version 9.7 XML storage object and row compression is enabled, this command now builds a compression dictionary for the XML data in addition to the dictionary for the table data. The compression dictionary for the XML data is stored in the XML storage object object.

When you run this command on tables with LOB columns, it now collects statistics for the average length of column and number of null values in a column. Refer to the Command Reference for additional details.

Refer to the BIND command row for additional authorization changes that also apply to the RUNSTATS command.