DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

**************************************************************** 
* 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                                     * 
****************************************************************

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 
;

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Apply DB2 V9.7 fixpack 5

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 
;

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

22.04.2011
23.01.2012
23.01.2012

9.7.FP5