Before you upgrade your databases and instance, it is important to use the db2ckupgrade
command to verify that your databases and instance are ready for upgrade.
The db2ckupgrade command verifies that a list of conditions is true to
succeed at the database upgrade, and verifies that the Db2®
instance does not contain any discontinued values.. Also, this command writes to the log file, which
is specified with the -l parameter, a warning message for a list of conditions
that affect database upgrades. See the Command Reference for details about the list of
conditions.
The db2iupgrade calls the db2ckupgrade command. The
db2iupgrade fails if the db2ckupgrade command finds any of the
conditions are not true or if the instance contains discontinued values, and returns the error code
DBI1205E.
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 Db2
pureScale® V10.5 Fix Pack
9 or later databases), an important part of the HADR upgrade procedure is that the
db2ckupgrade command validates the log positions of the primary database and all
standby databases cataloged in the instances. To ensure the success of the UPGRADE
DATABASE command in the new release, it is highly recommended to ensure that the
db2ckupgrade command is run so that this log validation is done. If your upgrade
procedure requires you to run db2ckupgrade manually, ensure to use the
-alldbchecks option in order to validate the log positions.
Also, Using high availability disaster recovery
monitoring, ensure that both the primary's log shipping
functionality and the standby's log replaying functionality is working
correctly before you run the db2ckupgrade command.
When the db2iupgrade command calls the db2ckupgrade command,
the log positions are validated.
Before you begin
- Ensure that you have SYSADM authority.
- Ensure that all the local databases that you want
to upgrade are cataloged. For more information, see Cataloging databases.
- On Linux® or UNIX operating systems, decompress
a Db2 version 12.1 installation image to be able to run the db2ckupgrade command.
- Ensure that you meet the installation requirements
for Db2 database
products.
Procedure
To verify that your instance and databases are ready for upgrade
-
Log on to the Db2 server as the
Db2 instance
owner that you want to upgrade.
-
If the instance that owns the databases that you want to verify is not running, start the
instance by running the db2start command.
- From the command-line prompt, change to the appropriate
directory:
- On UNIX or Linux operating systems, change to the
DIRIMG directory, where DIRIMG is the location where you
decompressed the Db2
version 12.1 installation image or the directory where you mounted the Db2 product
DVD.
- On Windows operating system,
insert the Db2
version 12.1 product DVD in the drive and change to the
\db2\Windows\utilities directory. Or change to the
DIRIMG directory, where DIRIMG is the location where you
decompressed the Db2
version 12.1 installation
image.
- Verify that the local databases that are owned by the current instance
are ready to be upgraded and generate a log file by running the db2ckupgrade
command, as
follows:
<DIRIMG> db2ckupgrade sample -l db2ckupgrade.log -u adminuser -p password
db2ckupgrade was successful. Database(s) can be upgraded.
where:
- DIRIMG is the location where you decompressed the Db2
version 12.1 installation image or the directory where you mounted the Db2 product DVD.
- sample is the database name
- db2ckupgrade.log is the log file that is created in the current directory
that includes details on errors and warnings
-
Note: Instance-level checks are only run when db2ckupgrade is run without a
database name or when the -instOnly flag is provided. For example, to run
database and instance checks, run db2ckupgrade
-e
-l
db2ckupgrade.log -u adminuser -p password.
When the db2iupgrade command runs the db2ckupgrade
command, the update.log log file is specified for
db2ckupgrade in the instance home directory for Linux and UNIX operating systems or in the current
directory for Windows operating systems.
In a
partitioned database environment, run the db2ckupgrade command only once. It
checks all partitions. Similarly, in a Db2
pureScale environment,
run the db2ckupgrade from only one member node.
- If you created user-defined data types using a name that is a system
built-in data type name, drop these user-defined data types and re-create them using a different
name that is not restricted.
The db2ckupgrade command returns the
SQL0473N error message when user-defined data types have a name that is a system built-in data type
name. If you try to upgrade the database, the UPGRADE DATABASE command
fails.
- If you created user-defined objects that are dependent on
discontinued administrative routines, drop the dependent objects and re-create them using the
routine or view that replaces the discontinued routine.
The
db2ckupgrade command returns the DBT5534W warning message when a user-defined
object is dependent on discontinued administrative routines. If you upgrade a database that has
dependent objects, the
UPGRADE DATABASE command drops the discontinued
administrative routines and marks the dependent objects inoperative or invalid.
For more details,
see Some
administrative routines are discontinued
- If you created workload management objects that have a conflict with system-reserved ID
during database upgrade, drop these objects and re-create them after you upgrade the database. The
db2ckupgrade command returns the DBT5512E error message when a workload
management object cannot be upgraded because the ID of that object conflicts with a system-reserved
ID. Perform the following actions:
- Generate the DDL statements to re-create the workload management objects by issuing
the db2look command with the wlm parameter.
- Drop all of the workload management objects from the database.
After upgrading the database, re-create the workload management objects in the upgraded
database by issuing the DDL statements that you generated with the db2look
command.
- If you created database objects using restricted schema names, drop all
the database objects that use reserved schema names and re-create them using a schema name that is
not restricted.
The db2ckupgrade command returns the SQL0553N error message when database
objects have restricted schema names. If you try to upgrade the database, the UPGRADE
DATABASE command fails.
- If you have identifiers called NULL for column names, routine parameter
names, or variable names, qualify, or delimit with quotes these identifiers in your SQL statements
to avoid conflict with the NULL keyword.
The db2ckupgrade command writes the ADM4102W warning message to the log file
when a database has identifiers called NULL
. If you use identifiers called NULL
that
are not fully qualified or delimited with quotes in your SQL statements, the identifier name might
resolve to the NULL keyword instead. This would result in a change in behavior from previous
releases. See Upgrade impact from SQL statement changes for details.
- If workload connection attributes contain asterisks (*), replace the
asterisks (*) with another character. The db2ckupgrade command writes the
ADM4103W warning message to the log file when workload connection attributes contain asterisks (*).
Note: While
you can use a single asterisk (*) as a wildcard character, there might be instances with some
workload attributes where you want to represent an actual asterisk. In these cases, use two
asterisks (**). The UPGRADE DATABASE command replaces the single asterisk (*) with two asterisks
(**) depending on the type of connection attribute.
- If
you create global variables of XML data type, compile SQL functions with parameters of XML data
type, or have XML data type in the RETURNS clause, you must upgrade to Db2
version 10.5 or
later releases that support the XML data type in these database objects. If you are upgrading from a
version of Db2 older than version 10.5, you
must drop these database objects and re-create them, specifying a supported data type.
The db2ckupgrade command writes the ADM4004W warning message to the log file
when a database has global variables of XML data type or compiled SQL functions with parameters of
XML data type or XML data type in the RETURNS clause. The XML data type is not supported on these
database objects. Therefore, these database objects are invalidated during the database upgrade.
- Ensure that the log file for db2ckupgrade command
contains the following text:
Version of DB2CKUPGRADE being run: version 12.1. This text confirms that you are running the correct level of the
db2ckupgrade command.
- Check and fix any invalid flavor fields on SQLSPCS files by using the
fixtbspflvr tool.
Details about this tool can be obtained from
IBM Support.
- If the
AUTHENTICATION or SRVCON_AUTH DBM CFG parameters are set to
CLIENT, the db2ckupgrade command generates the DBT5512E error
message. The message is generated because CLIENT AUTHENTICATION is not supported by Db2
12.1
and later. To upgrade, change these settings to SERVER. However, if you wish to continue using
CLIENT AUTHENTICATION in version 12.1 at your own
risk, you can enable it by running one of the following commands: