Enabling a database for spatial operations

Enabling the database for spatial operations consist of having Db2® Spatial Extender supply a database with resources for creating spatial columns and manipulating spatial data.

Before you begin

Before you enable a database for spatial operations:

Ensure that your user ID have DBADM authority on the database.
Ensure that you have a system temporary table space with a page size of 8 KB or larger and with a minimum size of 500 pages.

Procedure

Enable a database for spatial operations in any of the following ways:

Issue the db2se enable_db command.
Run an application that calls the DB2GSE.ST_ENABLE_DB procedure.

You can explicitly choose the table space in which you want the Db2 Spatial Extender catalog to reside. If you do not do so, the Db2 database system will use the default table space.

If you want to use spatial data in a partitioned database environment, do not use the default table space. For best results, enable the database in a table space that is defined for a single nodes. Typically a single node table space is defined for small tables where partitioning is not helpful.

For example, you can define a single node table space using the db2se command line processor:

db2se enable_db my_db -tableCreationParameters "IN NODE0TBS"

Many queries can be performed more efficiently if the spatial reference systems table DB2GSE.GSE_SPATIAL_REFERENCE_SYSTEMS is replicated across all nodes that are used for business tables that will be queried. You can re-create the DB2GSE.GSE_SRS_REPLICATED_AST table with statements like this:

drop table db2gse.gse_srs_replicated_ast;
-- MQT to replicate the SRS information across all nodes 
-- in a partitioned database environment
CREATE TABLE db2gse.gse_srs_replicated_ast AS
   ( SELECT srs_name, srs_id, x_offset, x_scale, y_offset, z_offset, z_scale,
       m_offset, m_scale, definition
     FROM db2gse.gse_spatial_reference_systems )
   DATA INITIALLY DEFERRED
   REFRESH IMMEDIATE
   ENABLE QUERY OPTIMIZATION
   REPLICATED
   IN ts_data  partitioned_tablespace
;

REFRESH TABLE db2gse.gse_srs_replicated_ast;

CREATE  INDEX db2gse.gse_srs_id_ast
   ON db2gse.gse_srs_replicated_ast ( srs_id )
;

RUNSTATS ON TABLE db2gse.gse_srs_replicated_ast and indexes all;