Creating and loading spatial data in a partitioned database environment

The partitioned table must have a partitioning key defined which is used to control the distribution of rows among the partitions.

Before you begin

Ensure that the database is enabled for spatial processing by creating spatial catalog tables and creating the spatial types and functions in the database. See Enabling a database for spatial operations for more information.

About this task

For optimum performance, ensure that the tables that contain spatial columns are distributed evenly across partitions. The default hashing algorithm and partition map do this.

Procedure

For best results, specify one or more columns in the CREATE TABLE statement as the partitioning key.

If you do not specify a partitioning key, Db2 selects the first numeric or character column as the partitioning key by default. This default might not distribute the rows evenly across partitions.

Example

The following example shows how to create a table to contain spatial data and use the Db2 Spatial Extender import utility to specify the partitioning key:

CREATE TABLE myschema.counties (id INTEGER PRIMARY KEY, 
   name VARCHAR(20), 
   geom db2gse.st_polygon)
   IN nodestbs                  
   DISTRIBUTE BY HASH(id);

db2se import_shape mydb
-tableName counties
-tableSchema myschema 
-spatialColumn shape
-fileName /shapefiles/counties.shp
-messagesFile counties.msg 
-createTable 0
-client 1  
-srsName NAD83_SRS_1 
-commitScope 10000
-idcolumn id
-idColumnIsIdentity 1