db2ckupgrade - Check database for upgrade command

Verifies that a database can be upgraded.

Scope

In a partitioned database environment, the db2ckupgrade command checks each database partition.

Authorization

SYSADM

Required connection

None

Command syntax

Command parameters

database: Specifies an alias name of a local database to be scanned.
-e: Specifies that all local cataloged databases are to be scanned.
-allChecks: Specifies that all the checks are to be run. This requires exclusive database connection.
-resetUpgradePending: Specifies that upgrade pending state to be reset.
-l filename: Specifies a log file to keep a list of errors and warnings generated for the scanned database.
-u userid: Specifies the user ID of the system administrator.
-p password: Specifies the password of the system administrator's user ID.

Usage notes

To run the db2ckupgrade command:

On Linux® and UNIX operating systems, install a new Db2® copy to which you want to upgrade. Then run the db2ckupgrade command from the DB2DIR/bin directory, where DB2DIR is the location where the Db2 copy is installed.
On Windows operating systems, insert the Db2 database product CD to which you want to upgrade. Then run the db2ckupgrade command from the db2\Windows\Utilities directory on the CD.

The db2ckupgrade command checks the following two types of database concepts:
- Data compatibility - Ensures that data tables and similar objects are compatible for upgrade. These checks can be done under a shared or exclusive database connection.
- Data consistency - Ensures that the database is consistent, including for HADR databases supporting upgrade that the primary and standby log positions are valid. These checks can be done only under an exclusive database connection.
When db2ckupgrade command is called internally by the db2iupgrade command, an exclusive database connection is attained. Under this mode, the utility executes all data compatibility and data consistency checks. If all these checks pass and the database is ready to be upgraded, the database is put into upgrade pending state. No database connections are allowed while the database is in upgrade pending state. This is to prevent further workload from running on the database that might invalidate the checks made by the db2ckupgrade command. This is done to ensure that the UPGRADE DATABASE command issued later on succeeds. If for any reason you are not upgrading your database until a later time and if you want the database connections to resume, you can reset the upgrade pending state by issuing db2ckupgrade with the -resetUpgradePending parameter.
When db2ckupgrade command is called manually, the utility attempts to attain an exclusive database connection. However, if this connection mode cannot be established, the utility will fallback to attempt a shared connection to the database. The connection mode attained by the utility determines the type of checking that is done on the database. If you want to perform all checks on the database, specify the -allChecks parameter. This enforces that an exclusive connection to the database is attained and all data compatibility and data consistency checks are executed. But, when the utility is run manually, the upgrade pending state is not turned on.

The db2ckupgrade command fails to run against databases which are cataloged as remote databases.

This command verifies that all the following conditions are true:

A cataloged database actually exists.
A database is not in an inconsistent state.
A database is not in a backup pending state.
A database is not in a restore pending state.
A database is not in rollforward pending state.
Tables are not in a load pending state.
Tables are not in a redistribute pending state.
For version 9.8 or later, that all table space container paths use the same mount point.
For version 9.8 Fix Pack 3 or later, that the I/O write operations for the database are not suspended or are not being suspended.
That there are no MQT's that depend on system views.
Table spaces are in a normal state.
A database does not contain user-defined types (UDTs) with the name ARRAY, BINARY, CURSOR, DECFLOAT, ROW, VARBINARY, or XML.
A database does not contain the built-in DATALINK data type.
A database does not have a schema with the name SYSPUBLIC.
A database does not have orphan rows in system catalog tables that would cause database upgrade to fail.
A database that is enabled as an HADR primary database allows successful connections.
For version 9.7 (all Fix Packs), version 10.1 (all Fix Packs) and version 10.5 Fix Pack 6 or earlier, an HADR database role is not standby.
If SYSCATSPACE is a DMS table space and AUTORESIZE is not enabled, SYSCATSPACE has at least 50% free pages of total pages.
A database is not enabled for XML Extender.
A database is not using raw logs.

A local database must pass all of these checks to succeed at the upgrade process. The db2iupgrade command calls the db2ckupgrade command and specifies update.log as the log file for db2ckupgrade. The default log file created for db2iupgrade is /tmp/db2ckupgrade.log.processID. The db2iupgrade fails if the db2ckupgrade command finds that any of the previously listed conditions are not true, and returns the DBI1205E error code. The user needs to resolve these errors before upgrading the instance.

The db2ckupgrade command writes a warning message to the log file, as specified with the -l parameter, for any of the following conditions:

Column names, routine parameter names, or variable names are called NULL.
Workload connection attributes contain asterisks (*).
Database is enabled for Db2 WebSphere® MQ functions.

For an HADR database, the db2ckupgrade command called by the db2iupgrade command and db2ckupgrade with the -allChecks option performs the following actions:

For HADR environments that support upgrade without the need for standby reinitialization (single partition ESE Db2 version 10.5 Fix Pack 7 or later databases or refer to the FAQ technote "http://www-01.ibm.com/support/docview.wss?uid=swg21298716" for pureScale® supported 10.5 Fix Packs).
- Primary database
  - The db2ckupgrade command tool verifies that successful connections are allowed and validates that the primary's log shipping position matches the standby's log replay position.
- Standby database
  - The tool skips database verification and returns success if it detects an HADR standby database.
For HADR environments that do not support upgrade without the need for standby reinitialization (Db2 version 9.7 (all Fix Packs), version 10.1 (all Fix Packs) or single partition ESE databases version 10.5 Fix Pack 6 or earlier or refer to the FAQ technote "http://www-01.ibm.com/support/docview.wss?uid=swg21298716" for pureScale supported 10.5 Fix Packs).
- Primary database
  - The db2ckupgrade command issues STOP HADR to turn the database role to standard. This action requires the HADR pair to be re-initialized post-upgrade. To initialize the HADR pair, see Initializing high availability disaster recovery (HADR)
- Standby database
  - The db2ckupgrade command fails if it detects an HADR standby database.

Note: An important part of the HADR upgrade procedure is the db2ckupgrade command validating the log positions of the primary database and all standby databases cataloged in the instances. This applies to both single standby and multiple standby configurations. For the UPGRADE DATABASE command to be successful, it is highly recommended to run db2ckupgrade command for validating the log positions.

During installation on Windows operating systems, if you select a Db2 copy with the upgrade action in the Work with Existing window and you have local databases cataloged on your instances, a message box will warn you that you must run the db2ckupgrade command from the Db2 database product CD. Then you can choose one of the following actions:

Ignore the message and continue the installation process.
Run the db2ckupgrade command. If this command runs successfully, continue the installation process. If you find errors, quit the installation process, fix any errors, and then rerun the installation process.
Quit the installation process.

To verify that a database is ready for upgrade, refer to Verifying that your databases are ready for upgrade.