After you upgraded your instances to DB2® Version 10.1, you need to upgrade each database under each instance.
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 Pre-upgrade tasks for DB2 servers.
- Ensure that you installed DB2 Version 10.1 and upgraded the instance to DB2 Version 10.1.
Restrictions
Procedure
To upgrade a DB2 database
to DB2 Version 10.1:
- 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.
To
avoid the overhead of an automatic rebind, consider using the REBINDALL parameter,
which specifies that a REBIND of all packages is
performed during upgrade.
- Optional: Issue the db2 LIST DATABASE
DIRECTORY command to ensure the database is in the list
of all catalogued databases in the current instance.
- 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.
Also,
consider using the REBINDALL parameter, which
specifies that a REBIND of all packages is performed
during upgrade
- 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 ADM7535W warning message with
all the details to the administration notification log, then the command
failed to refresh the table space attributes in the catalog table. However the database was upgraded successfully.
- If the UPGRADE DATABASE command
returns the SQL1499W warning message and
writes the ADM4003E warning message with
all the details to the administration notification log, then the command
failed to upgrade the DB2 Text
Search catalogs or indexes due to an error in a stored procedure.
- If the UPGRADE DATABASE command
returns the SQL1499W warning message and
writes the ADM7534W warning message with
all the details to the administration notification log, then the command
failed to refresh the table space attributes in the catalog table. However the database was upgraded successfully.
- 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 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 10.1 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
.
- If the UPGRADE
DATABASE command returns the ADM4003E error message, then
upgrade the DB2 Text Search
catalog and indexes manually. For
details, see SYSTS_UPGRADE_CATALOG and SYSTS_UPGRADE_INDEX.
- 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 10.1.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 10.1 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.