Upgrading databases

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

To upgrade a DB2 database to DB2 Version 10.1:

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

4. Optional: Issue the db2 LIST DATABASE DIRECTORY command to ensure the database is in the list of all catalogued databases in the current instance.
5. 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

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

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

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

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

14. 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.
15. 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.

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

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

    .
18. 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.
19. 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:
    • Database configuration parameter settings
    • Table spaces information

    • Packages information for your applications only

    You do not need to check package information for system generated packages. The information about system generated packages can change after upgrade.
20. 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.