Application migration

Both PureData® System for Analytics and Db2® follow the OGC and SQL/MM standards. Consequently, the vast majority of the SQL syntax that these products use for their spatial functions is compatible. However, due to flexibility in the standards, and because some non-mandatory functions differ between PureData System for Analytics and Db2, some PureData System for Analytics applications might require modification to run on Db2.

This information describes how to migrate an application from nzspatial_esri syntax to Db2 syntax. To migrate an application that uses legacy nzspatial syntax, review the differences between nzspatial and nzspatial-esri before migrating.

General SQL differences

For general differences between PureData System for Analytics and Db2, see IBM PureData System for Analytics (Netezza) to Db2 migration. The following differences affect spatial applications in particular:

A spatial function that uses a Boolean value in PureData System for Analytics uses an integer value (1 for TRUE; 0 for FALSE) in Db2. For example:

In PureData System for Analytics:

SELECT ST_Intersect(table1.shape, table2.shape) as newshape FROM table1, table2 
WHERE ST_Intersects(table1.shape, table2.shape)

In Db2:

SELECT ST_Intersect(table1.shape, table2.shape) as newshape FROM table1, table2 
WHERE ST_Intersects(table1.shape, table2.shape) = 1

In Db2, every SELECT statement must include a FROM clause, which can be a dummy FROM clause. If you want to exclude a FROM clause, you must use either a VALUES statement or a host variable instead of a SELECT statement. For example:
- In PureData System for Analytics:
```
SELECT ST_AsText( ST_Point(10,40)
```
- In Db2:
```
SELECT ST_AsText( ST_Point(10,40)) FROM sysibm.sysdummy1
or
VALUES ST_AsText( ST_Point(10,40))
or
SET :HV = ST_AsText( ST_Point(10,40) )
```

General spatial differences

Prefix

In PureData System for Analytics, spatial procedures or functions, spatial views, and spatial data types are all prefixed with inza.., for example, inza..ST_AsText(). To migrate your PureData System for Analytics application to Db2, replace the inza.. prefix with one of the following schema names:

Spatial procedures or functions: sysproc.
Spatial views: sysgeo.
Spatial data types: sysibm.

For example:

In PureData System for Analytics:

SELECT inza..ST_AsText(shape) from table1

In Db2:
```
SELECT ST_AsText(shape) from table1
```

Size specification for ST_GEOMETRY data

Both PureData System for Analytics and Db2 use the spatial data type ST_Geometry:

In PureData System for Analytics, nzspatial-esri allows the specification of a size modifier to define the maximum length of a column of type ST_GEOMETRY.
In Db2, a column of type ST_GEOMETRY has a fixed length that allows for the storage, after internal compression, of up to 4 MB. You cannot specify a size modifier to define the length of a column of type ST_GEOMETRY. However, you can assign to a column an inline length that determines the page size and thereby improves performance. For more information, see Specifying inline lengths for geospatial columns.

For more information about how to specify an inline length, see CREATE TABLE statement.

For example:

In PureData System for Analytics, the following statement sets the size of the geometry column to a maximum of 64000 Bytes:
```
CREATE TABLE table1 (id INTEGER, geometry ST_GEOMETRY(64000))
```
In Db2, the geometry column has a fixed length of 4 MB. The following statement sets the inline length of the geometry column to 4000 Bytes.
```
CREATE TABLE table1 (id INTEGER, geometry ST_GEOMETRY inline length 4000))
```

Table orientation

In Db2, spatial data can be stored in both column-oriented and row-oriented tables. The default table orientation is column-oriented. To use a row-oriented table, add the ORGANIZE BY ROW clause to the corresponding CREATE TABLE statement. For example:

To create a column-oriented table:

CREATE TABLE table1 (id INTEGER, geometry ST_GEOMETRY) ORGANIZE BY COLUMN

To create a row-oriented table with the maximum inline page size of 32 KB:
```
CREATE TABLE table2 (id INTEGER, geometry ST_GEOMETRY) ORGANIZE BY ROW
```

Default units

In PureData System for Analytics, the default unit is meter; in Db2, the default unit is the unit associated with the SRS being used. For example, for a geographic SRS, the default unit is degree.

Maximum string constant size

In PureData System for Analytics, the string constant that is passed into functions such as WKTToSQL is limited to 64 KB; in Db2, it is limited to 32 KB. Larger strings of up to 2 GB can be passed, but these require the data type of the input to be CLOB.

Differences for particular spatial functions

Unavailable functions

In Db2, no equivalent exists for the following PureData System for Analytics spatial functions:

ST_Ellipse
ST_GrandMBR
ST_Collect (To generate a multipoint geometry based on single points, use the ST_Multipoint function instead.)

User Defined Aggregate functions

The following functions exist only as scalar functions in Db2:

ST_Intersection
ST_Intersects
ST_Union

ST_GeometryType

In Db2, the ST_GeometryType function includes the schema name (SYSIBM) in the value that it returns. For example:

In PureData System for Analytics:
```
ST_POINT
ST_GEOMETRYCOLLECTION
```

In Db2:

SYSIBM.ST_POINT               
SYSIBM.ST_GEOMCOLLECTION

ST_Point

Since Spatial Analytics in Db2 uses integers to represent Boolean values (0 for FALSE; 1 for TRUE), two variants of ST_Point don’t exist because their signatures would overlap with other variants. For example:

ST_Point(DOUBLE x, DOUBLE y, DOUBLE ZorM, INT isZ) 
overlaps with 
ST_Point(DOUBLE x, DOUBLE y, DOUBLE z, DOUBLE m )
 
ST_Point(DOUBLE x, DOUBLE y, DOUBLE ZorM, INT isZ, INTEGER srsID)
overlaps with
ST_Point(DOUBLE x, DOUBLE y, DOUBLE z, DOUBLE m, INTEGER srsId)

ST_Crosses

In PureData System for Analytics, the function ST_Crosses returns one of two possible Boolean values. NULL is never returned. In Db2, the Boolean values are replaced by integer values (0 for FALSE; 1 for TRUE), and for some geometry constellations, NULL is returned.