After you upgraded your instances to DB2® Version 9.7, you need to upgrade each database
under each instance.
About this task
Restrictions
Before you begin
- Ensure that you have SYSADM authority.
- Ensure that all the local databases that you want to upgrade are
cataloged.
- Ensure that you backed up your databases as indicated in the pre-upgrade tasks.
- Ensure that you installed DB2 Version 9.7 and upgraded the instance to
Version 9.7.
Procedure
To upgrade a DB2 database
to DB2 Version 9.7:
- Log on to the DB2 server
as the instance owner or a user with SYSADM authority.
- Optional: Rename or delete the db2diag log
files so that new files are created. Also, remove or move to another
directory any existing dump files, trap files, and alert log files
in the directory indicated by the diagpath parameter.
By doing this, the files only contain information about the upgrade
process that helps you to isolate and understand any problem that
might occur during database upgrade.
- Upgrade the database using the UPGRADE DATABASE command:
db2 UPGRADE DATABASE database-alias USER username USING password
where database-alias is
the name or the alias of the database you want to upgrade and the
username and password to authenticate a user with SYSADM authority.
- If the UPGRADE DATABASE command fails
and returns the SQL1704N error
message with a reason code that describes the cause of the failure,
find this SQL error code and determine the action to take from the
list of the possible solutions for each reason code. One
of the most common causes of upgrade failure is that the log file
space is not large enough, in which case the following error is returned:
SQL1704N Database upgrade failed. Reason code "3".
You
must increase log file size and
execute the UPGRADE DATABASE command again. After
the database upgrade is complete reset the value of logfilsiz, logprimary and logsecond database
configuration parameters.
There are additional error codes that
are returned by the UPGRADE DATABASE command for
specific cases not supported by database upgrade. These cases are
described in the upgrade restrictions.
- If the UPGRADE DATABASE command returns
the SQL1243W warning
message, you need to drop or rename the SYSTOOLS.DB2LOOK_INFO table.
Otherwise, the ALTER TABLE and COPY SCHEMA statements will fail to
run. Check if the SYSTOOLS.DB2LOOK_INFO table exists
by running the following command:
db2 "SELECT tabname, tabschema, definer FROM syscat.tables
WHERE tabschema = 'SYSTOOLS' AND tabname = 'DB2LOOK_INFO'"
If
you created this table, rename it by running the RENAME statement:
db2 RENAME SYSTOOLS.DB2LOOK_INFO TO new-table-name
If you did not create this table, remove it by running
the DROP command:
db2 DROP TABLE SYSTOOLS.DB2LOOK_INFO
- If the UPGRADE DATABASE command
returns the SQL1499W warning
message and writes the ADM4100W warning
message with all the details to the administration notification log,
you have external unfenced routines on Linux or UNIX that have no dependency on
the DB2 engine libraries and
the UPGRADE DATABASE command redefines your external
routines as FENCED and NOT THREADSAFE. Also, the DB2_FENCED option
is set to 'Y' for all user-defined wrappers.
This command
also generates a script called alter_unfenced_database-name.db2 with
all the SQL statements to redefine external unfenced routines, altered
during the database upgrade, as NOT FENCED and THREADSAFE. This script
is created in the directory specified by the diagpath database
manager configuration parameter. If the diagpath parameter
is not set, the script is created in the INSTHOME/sqllib/db2dump directory
where INSTHOME is the instance home directory.
If you need to
define your routines as NOT FENCED and THREADSAFE, refer to Upgrading C, C++, and COBOL routines for details on how to safely run your
routines in the new multithreaded database manager and then use the
generated script to redefine your routines.
- If the UPGRADE DATABASE command
returns the SQL1499W warning
message and writes the ADM4101W warning
message to the administration notification log, take note of the system
catalog tables reported in the ADM4101W message so that you collect
statistics on these tables as part of the post-upgrade tasks.
- If the UPGRADE DATABASE command
returns the SQL1499W warning
message and writes the ADM4102W warning
message to the administration notification log, qualify or delimit
with quotes the identifiers called NULL in your SQL statements to
avoid conflict with the NULL keyword.
If you use identifiers
called NULL for column names, routine parameter names, or variable
names in an SQL statement that are not fully qualified or delimited
with quotes, the identifier name might resolve to the NULL keyword
instead. This would result in a change in behavior from previous releases.
Refer to Upgrade essentials for database applications for details.
- If the UPGRADE DATABASE command
returns the SQL1499W warning
message and writes the ADM4106W warning
message to the administration notification log, drop all references
to the XML Extender user-defined data types and drop all XML Extender
database objects under the DB2XML schema. Starting with DB2 Version 9.7, XML Extender is discontinued.
To avoid this error, perform all the steps in Upgrading a DB2 server with XML Extender to DB2 Version 9.7 to completely disable XML Extender and
remove XML Extender functionality before upgrading your instance and
databases.
- If the UPGRADE DATABASE command
returns the SQL1499W warning
message and writes the ADM4105W warning
message to the administration notification log, create new MQ functions
for the XML data type by running the enable_MQFunctions command
with the -xml parameter. The set of DB2 WebSphere® MQ
functions for XML Extender are dropped during database upgrade.
- If the UPGRADE DATABASE command
returns the SQL1499W warning
message and writes the ADM9516W warning
message to the administration notification log, verify that the indexrec configuration
parameter is set to RESTART and issue the RESTART
DATABASE command to rebuild indexes marked as invalid during database
upgrade. Otherwise, index rebuild starts on your first access to the
table and you might experience an unexpected degradation in response
time.
Type-1 indexes and indexes with
an index page that could not be upgraded are marked invalid during
database upgrade.
- If the UPGRADE DATABASE command
returns the SQL0473N error
message, you need to reverse the database
migration and re-create all user-defined data types that use
a system built-in data type name with a different name that is not
restricted.
To avoid the UPGRADE DATABASE command
failure, re-create these user-defined data types during Verifying that your databases are ready for upgrade.
- If the UPGRADE DATABASE command
returns the SQL1700N error
message, you need to reverse the database
migration and re-create database objects that use restricted
schema names with a schema name that is not restricted.
To
avoid the UPGRADE DATABASE command failure, re-create
these database objects during Verifying that your databases are ready for upgrade
.
- Compare your database configuration settings after upgrade
with the configuration settings you had before you upgraded your database.
Verify the following settings and database information are the same:
You do not need to check package information for system generated
packages. The information about system generated packages can change
after upgrade.
- Verify your database upgrade is successful. Connect to
the upgraded databases and issue a small query:
db2 connect to sample
Database Connection Information
Database server = DB2/AIX64 9.7.0
SQL authorization ID = TESTDB2
Local database alias = SAMPLE
db2 "select * from syscat.dbauth"
Alternatively,
if you have sample files installed, run the testdata.db2 script:
cd samplefile-dir-clp
db2 connect to sample
db2 -tvf testdata.db2
where samplefile-dir-clp is DB2DIR/samples/clp on Linux and UNIX and DB2DIR\samples\clp on Windows, DB2DIR represents
the location specified during DB2 Version
9.7 installation, and sample is the database name.
What to do next
After upgrading a DB2 database,
performing the recommended
post-upgrade
tasks ensures a successful database upgrade.