Upgrading databases
After you upgraded your instances to Db2® version 11.1, 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 11.1 and upgraded the instance to Db2 version 11.1.
Restrictions
Review the steps in Upgrade restrictions for Db2 servers for database upgrade.
Procedure
To upgrade a Db2 database to Db2 version 11.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.
- 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:
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.db2 UPGRADE DATABASE database-alias USER username USING password
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:
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.SQL1704N Database upgrade failed. Reason code "3".
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:
If you created this table, rename it by running the RENAME statement:db2 "SELECT tabname, tabschema, definer FROM syscat.tables WHERE tabschema = 'SYSTOOLS' AND tabname = 'DB2LOOK_INFO'"
If you did not create this table, remove it by running the DROP command:db2 RENAME SYSTOOLS.DB2LOOK_INFO TO new-table-name
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:
- Database configuration parameter settings
- Table spaces information
Packages information for your applications only
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:
Alternatively, if you have sample files that are installed, run the testdata.db2 script: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
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 11.1 installation, and sample is the database name.cd samplefile-dir-clp db2 connect to sample db2 -tvf testdata.db2
What to do next
After upgrading a Db2 database, perform the recommended post-upgrade tasks to ensure a successful database upgrade.