|
Problem IC75962
|
Status: Closed |
USING ESRI ARCGIS TO CREATE A NEW FEATURE LAYER FAILS AFTER SPATIAL UPGRADE TO DB2 V9.7 |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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. |
| 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 *
**************************************************************** |
| 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
; |
| available fix packs: |
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
|
| Solution |
Apply DB2 V9.7 fixpack 5 |
| Workaround |
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
; |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.04.2011
23.01.2012
23.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
