Restore a DB2 9.1 database to a 9.7 instance and then run db2se to upgrade the DB2 Spatial Extender

Summary

If the DB2 database is upgraded but the DB2 Spatial Extender is not, subsequent geodatabase upgrades fail with an error similar to the following:

"ERROR Creating Geodatabase tables
, Error = -51
,EXT_Error = 0
,EXT_ERROR1 = Underlying DBMS error
,EXT_ERROR2 = 00000"

Procedure

1. Restore the older database to the newer DB2 instance:
   
   

   Code:
   $ db2 restore db gis from /u01/DB2/OldDB to /u01/DB2/NewDB
   DB20000I  The RESTORE DATABASE command completed successfully.

   
   
2. Apply backup logs to complete the restore operation.
   
   

   Code:
   $ db2 rollforward db gis to end of logs and stop
                                    Rollforward Status
   
    Input database alias                   = gis
    Number of nodes have returned status   = 1
    Node number                            = 0
    Rollforward status                     = not pending
    Next log file to be read               =
    Log files processed                    =  -
    Last committed transaction             = 2012-07-12-07.49.14.000000 UTC
   
   DB20000I  The ROLLFORWARD command completed successfully.

   
   
3. Restart DB2.
   
   

   Code:
   $ db2stop
   07/17/2012 16:35:58     0   0   SQL1064N  DB2STOP processing was successful.
   SQL1064N  DB2STOP processing was successful.
   
   $ db2start
   07/17/2012 16:36:03     0   0   SQL1063N  DB2START processing was successful.
   SQL1063N  DB2START processing was successful.

   
   
4. Create a directory for a system temporary tablespace to be used for upgrading the DB2 Spatial Extender.
   
   

   Code:
   $ mkdir /u01/DB2/NewDB/NODE0000/SQL00001/SYSTEMTEMP

   
   
5. Create a large pagesized system temporary tablespace so that the DB2 Spatial Extender can be upgraded successfully.
   
   

   Code:
   $ db2
   
   db2 => connect to gis user db2admin using password
   
      Database Connection Information
   
    Database server        = DB2/SUN64 9.7.4
    SQL authorization ID   = DB2ADMIN
    Local database alias   = GIS
    
   db2 => CREATE BUFFERPOOL TEMPSYSBP32 IMMEDIATE SIZE AUTOMATIC PAGESIZE 32K
   DB20000I  The SQL command completed successfully.
   
   db2 => CREATE SYSTEM TEMPORARY TABLESPACE TEMP_SYSTEM32 PAGESIZE 32k MANAGED BY SYSTEM USING (/u01/DB2/NewDB/NODE0000/SQL00001/SYSTEMTEMP') BUFFERPOOL TEMPSYSBP32
   DB20000I  The SQL command completed successfully.
   
   db2 => quit
   DB20000I  The QUIT command completed successfully.

   
   
6. Save the existing spatial indexes.
   
   

   Code:
   $ db2se save_indexes gis -messagesFile /TEMP/save_indexes.log
   GSE0000I  The operation was completed successfully.

   
   
7. Upgrade the DB2 Spatial Extender.
   
   

   Code:
   $ db2se upgrade gis -force 1 -messagesFile /TEMP/gse_upgrade.log
   Upgrading database. Please wait ...
   GSE0000I  The operation was completed successfully.

   
   
8. Restore the spatial indexes.
   
   

   Code:
   $ db2se restore_indexes gis -messagesFile /TEMP/restore_indexes.log
   GSE0000I  The operation was completed successfully.