Before you upgrade your databases,
it is important to use the db2ckupgrade command
to verify that your databases are ready for upgrade.
The db2ckupgrade command
verifies that a list of conditions is true to succeed at the database
upgrade. 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, 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 -allChecks 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 11.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 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 11.1 installation image or the directory where you mounted the Db2 product
DVD.
- On Windows operating
system, insert the Db2 version 11.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 11.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
11.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
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 (*).
Starting with Db2
version 9.7, you can use a single asterisk (*) as a wildcard character. In some workload attributes, if the
intention is to represent an actual asterisk, then you can use two asterisks (**). The UPGRADE
DATABASE command replaces the single asterisk (*) with two asterisks (**) depending the type of
connection attribute.
- If you created global variables
of XML data type or created compiled SQL functions with parameters
of XML data type or XML data type in the RETURNS clause, you must
upgrade to the version 10.1 Fix Pack 1 software or later fix pack releases that
support the XML data type in these database objects. If you decide
to upgrade to the version 10.1 software, 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 11.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.