Creating inline spatial columns
An inline spatial column is defined with a LOB data type, or a distinct type that is based on a LOB data type. After you choose a coordinate system and determine which spatial reference system to use for your data, you can create an inline spatial column in an existing table or import spatial data into a new table.
Before you begin
Before you create an inline spatial column, your user ID must hold the authorizations that are needed for the Db2 SQL CREATE TABLE statement or ALTER TABLE statement.
About this task
You can gain performance improvements for data loading, index creation, and queries by using inline LOB columns to store certain types of geometries. These geometry types are ST_LineString, ST_Polygon, ST_MultiPoint, ST_MultiLineString and ST_MultiPolygon, which are based on the BLOB data type. However, inline LOB columns use more storage for a base table space than LOB or non-LOB columns.
Procedure
To create an inline spatial column:
- If your Db2 subsystem
is not enabled for spatial support, to create an inline spatial column:
- Set the LOB_INLINE_LENGTH subsystem parameter to the appropriate value.
- Restart Db2.
- Enable you Db2 subsystem for spatial support.
- Issue the CREATE TABLE statement to create a new table with a spatial column. The non-point column is created as an inline spatial column. The length of the column is inherited from the value of the LOB_INLINE_LENGTH subsystem parameter.
- If your Db2 subsystem is enabled for spatial support, issue the CREATE TABLE statement and specify the INLINE LENGTH clause to specify the inline length for the column.
- For an existing table with non-point geometry columns, issue
the ALTER TABLE ALTER column-alternation statement
to change the column to be an inline spatial column.Tip: After changing an existing column to an inline spatial column, run the REORG TABLESPACE utility.
- For an existing table without geometry columns, issue the ALTER TABLE ADD column-definition statement to add an inline spatial column.
Example
The following example assumes that you set the LOB_INLINE_LENGTH subsystem parameter to 1000 bytes, and then enabled spatial support for your Db2 subsystem. To create a new table with an inline spatial column, issue the following statement:
CREATE TABLE SYSADM.REAL_ESTATE(ID INTEGER NOT NULL,
PROPERTY DB2GSE.ST_MULTIPOLYGON);The PROPERTY column is defined as an inline spatial column with a length of 1000 bytes.
You can override the value of the LOB_INLINE_LENGTH subsystem parameter by issuing the CREATE TABLE statement with the INLINE LENGTH clause, as in the following example:
CREATE TABLE SYSADM.REAL_ESTATE(ID INTEGER NOT NULL,
PROPERTY DB2GSE.ST_MULTIPOLYGON INLINE LENGTH 500 );The new length of the PROPERTY column is 500 bytes.
In the next example, assume you want to change a column to be an inline spatial column in an existing table with non-point geometry columns. After you issue the following ALTER TABLE ALTER column-alternation statement and run the REORG TABLESPACE utility, the PROPERTY column will have a length of 700 bytes:
ALTER TABLE SYSADM.REAL_ESTATE ALTER PROPERTY SET INLINE LENGTH 700;Finally, assume you created a table by issuing the following CREATE TABLE statement:
CREATE TABLE SYSADM.REAL_ESTATE1(ID INTEGER NOT NULL, PRICE DECIMAL(9,2));Now you want to add an inline spatial column to this existing table that does not have geometry columns. Issue the following ALTER TABLE ADD column-definition statement:
ALTER TABLE SYSADM.REAL_ESTATE1 ADD COLUMN PROPERTY DB2GSE.ST_MULTIPOLYGON;By issuing this statement, the PROPERTY column inherits the inline LOB length from the LOB_INLINE_LENGTH subsystem parameter, which is set to 1000 bytes. If you want to override the LOB_INLINE_LENGTH subsystem parameter, issue the following statement:
ALTER TABLE SYSADM.REAL_ESTATE1 ADD COLUMN PROPERTY1
DB2GSE.ST_MULTIPOLYGON INLINE LENGTH 700;The length of the PROPERTY column is 700 bytes.