After you upgraded your instances to DB2® Version 10.5, you must 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.5 and upgraded the instance to DB2 Version 10.5.
Restrictions
Procedure
To upgrade a DB2 database
to DB2 Version 10.5:
- 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.
- Optional: Issue the db2 LIST DATABASE
DIRECTORY command to ensure that 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. 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.
- 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 that are not supported by database upgrade. These cases are described in the upgrade restrictions.
- If the UPGRADE DATABASE command
returns the SQL1243W warning message, you
must 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 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.
- If the UPGRADE
DATABASE command returns the SQL0473N error message, you
must 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 DBT5512 error message, the command failed to upgrade the database
because the ID of a workload management object conflicts with a system-reserved
ID. To upgrade the database, 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.
- Resolve all issues that are reported by the db2ckupgrade command
and block the database from being upgraded.
- Upgrade the database.
- Re-create the workload management object in the upgraded
database by issuing the DDL statements that t you generated with the db2look command.
- If the UPGRADE
DATABASE command returns the SQL1700N error message, you
must 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 that the following settings and database information are the
same:
You need not check package information for system generated
packages. The information about system generated packages can change
after upgrade.
- Verify that 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 that are 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 that is specified during DB2 Version 10.5 installation, and sample is the database name.
What to do next
After upgrading a DB2 database, perform the recommended post-upgrade tasks to ensure a successful
database upgrade.