English

How To: 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

The following steps show the restore and upgrade processes that must be performed to successfully upgrade the geodatabase after upgrading the DB2 database.

  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.