Upgrading to a new Db2 server

If you want to upgrade to a new Db2 version 12.1 server, re-create your instances and then upgrade your databases by restoring a pre- version 12.1 database backup. After restoring the database backup, the RESTORE DATABASE command automatically runs the UPGRADE DATABASE command.

To upgrade to a new Db2 version 12.1 server:

1. Perform a full offline database backup of your existing databases and any other pre-upgrade tasks that apply.
   If you performed full offline database backups recently and you cannot perform another one before upgrade, you can perform an incremental offline database backup instead.
2. Log on to the new Db2 server as root on Linux and UNIX operating systems or user with Local Administrator authority on Windows operating systems.
3. Install Db2 version 12.1 on the new Db2 server.
4. Create your instances on the new Db2 server by running the db2icrt command from the version 12.1 copy location that you installed in the previous step. If the new \Db2 server has similar resources, then restore the database manager configuration parameter values for each instance using the UPDATE DBM CFG command and the values that you saved in the pre-upgrade tasks.
5. Transfer pre-Db2 version 12.1 backup files for all the databases that you want to upgrade to the new Db2 server.
6. Log on to the Db2 server as a user with SYSADM authority.
7. Upgrade the database using the RESTORE DATABASE command. The following example shows how to restore the sample database on UNIX operating systems:

      db2 RESTORE DATABASE sample FROM /db2/backups

   where sample is the database name and /db2/backups is the directory for the database backup file.

   If you performed an incremental offline database backup before upgrade, you must have access to the most recent full offline database backup and the incremental offline database backup and use an automatic incremental restore to upgrade the database. A manual incremental restore will fail because each RESTORE DATABASE command tries to upgrade the database before the database is completely recovered. The following example shows how to perform an automatic incremental restore:

      db2 RESTORE DATABASE sample INCREMENTAL AUTOMATIC 
          TAKEN AT timestamp WITHOUT PROMPTING
   

   In a partitioned database environment, you must execute the RESTORE DATABASE command in all database partitions starting with the catalog partition first. If sqlcode 7535 is returned as follows:

   SQL2517W The database was restored and then upgraded to the current release.
   The database upgrade returned sqlcode "7535" and tokens "*N".
   

   then you can run the UPGRADE DATABASE command again.
8. When the database was restored but the database was not upgraded, the RESTORE DATABASE command returns the following error and includes the upgrade error message with the reason code:

   SQL2519N  The database was restored but the restored database was not upgraded 
      to the current release.  Error "-1704" with tokens "3" is returned. 
      SQLSTATE=57011

   The error message SQL1704N indicates the database upgrade failed. Find this SQL error code in the Message Reference Volume 2 to read the list of the possible solutions for each reason code. In the previous example, tokens "3" means reason code 3 which indicates that the upgrade failed because the database logs are full. If this error occurs, complete the following steps to upgrade the database:

   1. Increase the size of the log files.
   2. Upgrade the database using the UPGRADE DATABASE command.
   3. If the log file size is still not large enough, the following error is returned:

      SQL1704N  Database upgrade failed.  Reason code "3". 

      You must increase the log file size and attempt to upgrade the database again.

   4. After the database upgrade is completed reset the size of the log files to their pre-upgrade values.

9. Optional: Configure your new Db2 server to use the new resources available by running the AUTOCONFIGURE command to calculate the buffer pool sizes, and the database manager and database configuration parameters values. The following example shows how to run this command to only display recommended values for the sample database:

   db2 CONNECT TO sample
   db2 AUTOCONFIGURE USING MEM_PERCENT 80 
         WORKLOAD_TYPE complex 
         NUM_STMTS 1 TPM 73
         ADMIN_PRIORITY performance
         IS_POPULATED YES
         NUM_REMOTE_APPS 15 
         ISOLATION CS
       APPLY NONE;

   If you choose not to run this command or not to apply the recommended values, manually configure your Db2 server to use the new resources. Otherwise, your databases might not perform as expected.
10. Restore any external routines that you backed up in the pre-upgrade tasks.
11. 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
        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 12.1 installation, and sample is the database name.