IBM Support

IC75962: USING ESRI ARCGIS TO CREATE A NEW FEATURE LAYER FAILS AFTER SPATIAL UPGRADE TO DB2 V9.7

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • Customers who originally created and spatially-enabled a
    database on DB2 V7.1 or V8.1 and are using the Esri ArcGIS
    software may experience
    the following error after running the spatial upgrade to DB2
    V9.7.
    
    Failed to create the feature dataset.
    Undrlying DBMS error [GSE0214N An INSERT statement failed.
    SQLERROR = "SQL0723N An error occurred in a triggered
    SQL statement in trigger "DB2GSE.SDE_SPAT_REF_INS". Information
    returned for the error includes SQLCODE "-407",
    SQLSTATE "23502" and message tokens "TBSPACEID=2, TABLEID=146,
    COLNO=2". SQLSTATE=09000".]
    
    The problem has to do with triggers that were created in DB2 V7
    and V8.1 to handle support for deprecated spatial metadata
    tables.  These triggers now cause a problem for databases that
    were migrated from V7 to V8 to V9 to V9.7.
    

Local fix

  • Run the following query to see which triggers are obsolete and
    manually delete them:
    SELECT
     CASE WHEN
      tabname in ('GSE_COORD_REF', 'GSE_GEOCODER_ID')
    OR (UPPER(SUBSTR(text,1,1000)) LIKE '%GSE_SPATIAL_REF%' AND
    UPPER(SUBSTR(text,1,1000)) NOT LIKE
    '%GSE_SPATIAL_REFERENCE_SYSTEMS%')
    OR UPPER(SUBSTR(text,1,1000)) LIKE '%GSE_COORD_REF%'
    OR UPPER(SUBSTR(text,1,1000)) LIKE '%GSE_COORDINATE_SYSTEMS_ID%'
      THEN 'OBSOLETE'
      ELSE 'OK'
     END AS status
     ,varchar(trigschema, 10) as trigschema
     ,varchar(trigname, 18) as trigname
     ,varchar(tabname, 32) as tabname
    FROM syscat.triggers
    WHERE trigschema='DB2GSE'
    ORDER BY status
    ;
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Customers using Esri ArcGIS with DB2 database created in DB2 *
    * V7 or V8 and migrated up through DB2 V9.7                    *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Customers who originally created and spatially-enabled a     *
    * database on DB2 V7.1 or V8.1 and are using the Esri ArcGIS   *
    * software may experience                                      *
    * the following error after running the spatial upgrade to DB2 *
    * V9.7.                                                        *
    *                                                              *
    * Failed to create the feature dataset.                        *
    * Undrlying DBMS error [GSE0214N An INSERT statement failed.   *
    * SQLERROR = "SQL0723N An error occurred in a triggered        *
    * SQL statement in trigger "DB2GSE.SDE_SPAT_REF_INS".          *
    * Information returned for the error includes SQLCODE "-407",  *
    * SQLSTATE "23502" and message tokens "TBSPACEID=2,            *
    * TABLEID=146, COLNO=2". SQLSTATE=09000".]                     *
    *                                                              *
    * The problem has to do with triggers that were created in DB2 *
    * V7 and V8.1 to handle support for deprecated spatial         *
    * metadata tables.  These triggers now cause a problem for     *
    * databases that were migrated from V7 to V8 to V9 to V9.7.    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply DB2 V9.7 fixpack 5                                     *
    ****************************************************************
    

Problem conclusion

  • Apply DB2 V9.7 fixpack 5
    

Temporary fix

  • Run the following query to see which triggers are obsolete and
    manually delete them:
    SELECT
    CASE WHEN
      tabname in ('GSE_COORD_REF', 'GSE_GEOCODER_ID')
    OR (UPPER(SUBSTR(text,1,1000)) LIKE '%GSE_SPATIAL_REF%' AND
    UPPER(SUBSTR(text,1,1000)) NOT LIKE
    '%GSE_SPATIAL_REFERENCE_SYSTEMS%')
    OR UPPER(SUBSTR(text,1,1000)) LIKE '%GSE_COORD_REF%'
    OR UPPER(SUBSTR(text,1,1000)) LIKE '%GSE_COORDINATE_SYSTEMS_ID%'
      THEN 'OBSOLETE'
      ELSE 'OK'
    END AS status
    ,varchar(trigschema, 10) as trigschema
    ,varchar(trigname, 18) as trigname
    ,varchar(tabname, 32) as tabname
    FROM syscat.triggers
    WHERE trigschema='DB2GSE'
    ORDER BY status
    ;
    

Comments

APAR Information

  • APAR number

    IC75962

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-04-22

  • Closed date

    2012-01-23

  • Last modified date

    2012-01-23

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R970 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"9.7","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
23 January 2012