Upgrading databases

After you upgraded your instances to DB2® Version 10.5, you must upgrade each database under each instance.

To upgrade a DB2 database to DB2 Version 10.5:

1. Log on to the DB2 server as the instance owner or a user with SYSADM authority.
2. 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.
3. 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.
4. 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.

5. 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.

6. 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

7. 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.
8. 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.
9. 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.
10. 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.
11. 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.

12. 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.
13. 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.

14. 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:
    1. Generate the DDL statements to re-create the workload management objects by issuing the db2look command with the -wlm parameter.
    2. Drop all of the workload management objects from the database.
    3. Resolve all issues that are reported by the db2ckupgrade command and block the database from being upgraded.
    4. Upgrade the database.
    5. Re-create the workload management object in the upgraded database by issuing the DDL statements that t you generated with the db2look command.
15. 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

    .
16. 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.
17. 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.
18. 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.