Upgrading databases

To upgrade a DB2 database to DB2 Version 9.7:

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

6. If the UPGRADE DATABASE command returns the SQL1499W warning message and writes theADM4100W warning message with all the details to the administration notification log, you have external unfenced routines on Linux or UNIX that have no dependency on the DB2 engine libraries and the UPGRADE DATABASE command redefines your external routines as FENCED and NOT THREADSAFE. Also, the DB2_FENCED option is set to 'Y' for all user-defined wrappers.
   This command also generates a script called alter_unfenced_database-name.db2 with all the SQL statements to redefine external unfenced routines, altered during the database upgrade, as NOT FENCED and THREADSAFE. This script is created in the directory specified by the diagpathdatabase manager configuration parameter. If the diagpath parameter is not set, the script is created in the INSTHOME/sqllib/db2dump directory where INSTHOME is the instance home directory.
   If you need to define your routines as NOT FENCED and THREADSAFE, refer to Upgrading C, C++, and COBOL routines for details on how to safely run your routines in the new multithreaded database manager and then use the generated script to redefine your routines.
7. If the UPGRADE DATABASE command returns the SQL1499W warning message and writes theADM4101W warning message to the administration notification log, take note of the system catalog tables reported in the ADM4101W message so that you collect statistics on these tables as part of the post-upgrade tasks.
8. If the UPGRADE DATABASE command returns the SQL1499W warning message and writes theADM4102W 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.
9. If the UPGRADE DATABASE command returns the SQL1499W warning message and writes theADM4106W 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 9.7 to completely disable XML Extender and remove XML Extender functionality before upgrading your instance and databases.
10. If the UPGRADE DATABASE command returns the SQL1499W warning message and writes theADM4105W 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.
11. If the UPGRADE DATABASE command returns the SQL1499W warning message and writes theADM9516W warning message to the administration notification log, verify that the indexrecconfiguration 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.
12. 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 duringVerifying that your databases are ready for upgrade.
13. 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 duringVerifying that your databases are ready for upgrade
    .
14. 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.
15. 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 9.7.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 andDB2DIR\samples\clp on Windows, DB2DIR represents the location specified during DB2 Version 9.7 installation, and sample is the database name.