DB2 FOR LUW / DB2FORLUW / A10 - DB2

Spatial queries using a spatial constructor experience 
unexpectedly slow performance when run with small amounts of 
data. 
This is due to the compile time when the spatial value is 
constructed using the well-known text constructor. 
 
An example query is: 
 
SELECT id 
FROM my_table 
WHERE db2gse.ST_Intersects(location, db2gse.ST_Polygon('polygon 
((0 0, 10 0, 10 10, 0 10, 0 0))', 1003)) = 1"

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 10.1 Fix Pack 4                           * 
****************************************************************

1) Rewrite the query, for example, to put the spatial 
constructor in a common table expression. 
 
For the example query, rewrite like: 
 
WITH temp(geom) AS ( 
  VALUES (db2gse.ST_Polygon('polygon ((0 0, 10 0, 10 10, 0 10, 0 
0))', 1003)) 
) 
SELECT id 
FROM my_table, temp 
WHERE db2gse.ST_Intersects(location, geom) = 1 
 
2) Or another option is, if a point value is being constructed, 
use 
  db2gse.ST_Point(0, 0, 1003) 
instead of 
  db2gse.ST_Point('point (0 0)', 1003) 
 
3) Some general options are also: 
- for non-DPF systems, remove DB2GSE.GSE_SRS_REPLICATED_AST 
- use parameter markers to avoid statement re-compiles 
- set the DB2 database configuration parameter STMT_CONC to ON 
- depending on the use case, consider using 
db2gse.EnvelopesIntersect

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 10.1 Fix Pack 4

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

27.12.2013
09.06.2014
09.06.2014