DB2 10.5 for Linux, UNIX, and Windows

ST_IMPORT_SHAPE procedure

Use this stored procedure to import a shape file to a database that is enabled for spatial operations.

The stored procedure can operate in either of two ways, based on the create_table_flag parameter:

DB2® Spatial Extender can create a table that has a spatial column and attribute columns, and it can then load the table's columns with the file's data.
Otherwise, the shape and attribute data can be loaded into an existing table that has a spatial column and attribute columns that match the file's data.

Authorization

The owner of the DB2 instance must have the necessary privileges on the server machine for reading the input files and optionally writing error files. Additional authorization requirements vary based on whether you are importing into an existing table or into a new table.

When importing into an existing table, the user ID under which this stored procedure is invoked must hold one of the following authorities or privileges:
- DATAACCESS
- CONTROL privilege on the table or view
- INSERT and SELECT privilege on the table or view
When importing into a new table, the user ID under which this stored procedure is invoked must hold one of the following authorities or privileges:
- DBADM
- CREATETAB authority on the database
The user ID must also have one of the following authorities:
- IMPLICIT_SCHEMA authority on the database, if the schema name of the table does not exist
- CREATEIN privilege on the schema, if the schema of the table exists

Syntax


>>-DB2GSE.ST_IMPORT_SHAPE--(--file_name--,---------------------->

>--+-input_attr_columns-+--,--srs_name--,--+-table_schema-+----->
   '-null---------------'                  '-null---------'   

>--,--table_name--,--+-table_attr_columns-+--,------------------>
                     '-null---------------'      

>--+-create_table_flag-+--,--+-table_creation_parameters-+--,--->
   '-null--------------'     '-null----------------------'      

>--spatial_column--,--+-type_schema-+--,--+-type_name-+--,------>
                      '-null--------'     '-null------'      

>--+-inline_length-+--,--+-id_column-+--,----------------------->
   '-null----------'     '-null------'      

>--+-id_column_is_identity-+--,--+-restart_count-+--,----------->
   '-null------------------'     '-null----------'      

>--+-commit_scope-+--,--+-exception_file-+--,------------------->
   '-null---------'     '-null-----------'      

>--+-messages_file-+--,--msg_code--,--msg_text--)--------------><
   '-null----------'

Parameter descriptions

file_name

Specifies the full path name of the shape file that is to be imported. You must specify a non-null value for this parameter.

If you specify the optional file extension, specify either .shp or .SHP. DB2 Spatial Extender first looks for an exact match of the specified file name. If DB2 Spatial Extender does not find an exact match, it looks first for a file with the .shp extension, and then for a file with the .SHP extension.

See Usage notes for a list of required files, which must reside on the server machine. The stored procedure, which runs as a process that is owned by the DB2 instance owner, must have the necessary privileges on the server to read the files.

The data type of this parameter is VARCHAR(256).

input_attr_columns

Specifies a list of attribute columns to import from the dBASE file. Although you must specify a value for this parameter, the value can be null. If this parameter is null, all columns are imported. If the dBASE file does not exist, this parameter must be the empty string or null.

To specify a non-null value for this parameter, use one of the following specifications:

List the attribute column names. The following example shows how to specify a list of the names of the attribute columns that are to be imported from the dBASE file:
```
N(COLUMN1,COLUMN5,COLUMN3,COLUMN7)
```
If a column name is not enclosed in double quotation marks, it is converted to uppercase. Each name in the list must be separated by a comma. The resulting names must exactly match the column names in the dBASE file.
List the attribute column numbers. The following example shows how to specify a list of the numbers of the attribute columns that are to be imported from the dBASE file:
```
P(1,5,3,7)
```
Columns are numbered beginning with 1. Each number in the list must be separated by a comma.
Indicate that no attribute data is to be imported. Specify "", which is an empty string that explicitly specifies that DB2 Spatial Extender is to import no attribute data.

The data type of this parameter is VARCHAR(32K).

srs_name

Identifies the spatial reference system that is to be used for the geometries that are imported into the spatial column. You must specify a non-null value for this parameter.

The spatial column will not be registered. The spatial reference system (SRS) must exist before the data is imported. The import process does not implicitly create the SRS, but it does compare the coordinate system of the SRS with the coordinate system that is specified in the .prj file (if available with the shape file). The import process also verifies that the extents of the data in the shape file can be represented in the given spatial reference system. That is, the import process verifies that the extents lie within the minimum and maximum possible X, Y, Z, and M coordinates of the SRS.

The srs_name value is converted to uppercase unless you enclose it in double quotation marks.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_schema

Names the schema to which the table that is specified in the table_name parameter belongs. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the value in the CURRENT SCHEMA special register is used as the schema name for the table or view.

The table_schema value is converted to uppercase unless you enclose it in double quotation marks.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_name

Specifies the unqualified name of the table into which the imported shape file is to be loaded. You must specify a non-null value for this parameter.

The table_name value is converted to uppercase unless you enclose it in double quotation marks.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_attr_columns

Specifies the table column names where attribute data from the dBASE file is to be stored. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the names of the columns in the dBASE file are used.

If this parameter is specified, the number of names must match the number of columns that are imported from the dBASE file. If the table exists, the column definitions must match the incoming data. See Usage notes for an explanation of how attribute data types are mapped to DB2 data types.

The data type of this parameter is VARCHAR(32K).

create_table_flag

Specifies whether the import process is to create a new table. Although you must specify a value for this parameter, the value can be null. If this parameter is null or any other value other than 0 (zero), a new table is created. (If the table already exists, an error is returned.) If this parameter is 0 (zero), no table is created, and the table must already exist.

The data type of this parameter is INTEGER.

table_creation_parameters

Specifies any options that are to be added to the CREATE TABLE statement that creates a table into which data is to be imported. Although you must specify a value for this parameter, the value can be null. If this parameter is null, no options are added to the CREATE TABLE statement.

To specify any CREATE TABLE options, use the syntax of the DB2 CREATE TABLE statement. For example, to specify a table space in which to create the tables, specify:

IN tsName INDEX IN indexTsName LONG IN longTsName

The data type of this parameter is VARCHAR(32K).

spatial_column

Name of the spatial column in the table into which the shape data is to be loaded. You must specify a non-null value for this parameter.

For a new table, this parameter specifies the name of the new spatial column that is to be created. Otherwise, this parameter specifies the name of an existing spatial column in the table.

The spatial_column value is converted to uppercase unless you enclose it in double quotation marks.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

type_schema

Specifies the schema name of the spatial data type (specified by the type_name parameter) that is to be used when creating a spatial column in a new table. Although you must specify a value for this parameter, the value can be null. If this parameter is null, a value of DB2GSE is used.

The type_schema value is converted to uppercase unless you enclose it in double quotation marks.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

type_name

Names the data type that is to be used for the spatial values. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the data type is determined by the shape file and is one of the following types:

ST_Point
ST_MultiPoint
ST_MultiLineString
ST_MultiPolygon

Note that shape files, by definition, allow a distinction only between points and multipoints, but not between polygons and multipolygons or between linestrings and multilinestrings.

If you are importing into a table that does not yet exist, this data type is also used for the data type of the spatial column. In that case, the data type can also be a super type of ST_Point, ST_MultiPoint, ST_MultiLineString, or ST_MultiPolygon.

The type_name value is converted to uppercase unless you enclose it in double quotation marks.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

inline_length

Specifies, for a new table, the maximum number of bytes that are to be allocated for the spatial column within the table. Although you must specify a value for this parameter, the value can be null. If this parameter is null, no explicit INLINE LENGTH option is used in the CREATE TABLE statement, and DB2 defaults are used implicitly.

Spatial records that exceed this size are stored separately in the LOB table space, which might be slower to access.

Typical sizes that are needed for various spatial types are as follows:

One point: 292.
Multipoint, line, or polygon: As large a value as possible. Consider that the total number of bytes in one row should not exceed the limit for the page size of the table space for which the table is created.

See the DB2 documentation about the CREATE TABLE SQL statement for a complete description of this value. See also the db2dart utility to determine the number of inline geometries for existing tables and the ability to alter the inline length.

The data type of this parameter is INTEGER.

id_column

Names a column that is to be created to contain a unique number for each row of data. (ESRI tools require a column named SE_ROW_ID.) The unique values for that column are generated automatically during the import process. Although you must specify a value for this parameter, the value can be null if no column (with a unique ID in each row) exists in the table or if you are not adding such a column to a newly created table. If this parameter is null, no column is created or populated with unique numbers.

Restriction: You cannot specify an id_column name that matches the name of any column in the dBASE file.

The requirements and effect of this parameter depend on whether the table already exists.

For an existing table, the data type of the id_column parameter can be any integer type (INTEGER, SMALLINT, or BIGINT).
For a new table that is to be created, the column is added to the table when the stored procedure creates it. The column will be defined as follows:
```
INTEGER NOT NULL PRIMARY KEY 
```
If the value of the id_column_is_identity parameter is not null and not 0 (zero), the definition is expanded as follows:
```
INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
( START WITH 1 INCREMENT BY 1 )
```

The id_column value is converted to uppercase unless you enclose it in double quotation marks.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

id_column_is_identity

Indicates whether the specified id_column is to be created using the IDENTITY clause. Although you must specify a value for this parameter, the value can be null. If this parameter is 0 (zero) or null, the column is not created as the identity column. If the parameter is any value other than 0 or null, the column is created as the identity column. This parameter is ignored for tables that already exist.

The data type of this parameter is SMALLINT.

restart_count

Specifies that an import operation is to be started at record n + 1. The first n records are skipped. Although you must specify a value for this parameter, the value can be null. If this parameter is null, all records (starting with record number 1) are imported.

The data type of this parameter is INTEGER.

commit_scope

Specifies that a COMMIT is to be performed after at least n records are imported. Although you must specify a value for this parameter, the value can be null. If this parameter is null, a value of 0 (zero) is used, and a COMMIT is performed at the end of the operation. This can result in large logfile utilization and data lost on operations that are interrupted.

The data type of this parameter is INTEGER.

exception_file

Specifies the full path name of a shape file in which the shape data that could not be imported is stored. Although you must specify a value for this parameter, the value can be null. If the parameter is null, no files are created.

If you specify a value for the parameter and include the optional file extension, specify either .shp or .SHP. If the extension is null, an extension of .shp is appended.

The exception file holds the complete block of rows for which a single insert statement failed. For example, assume that one row cannot be imported because the shape data is incorrectly encoded. A single insert statement attempts to import 20 rows, including the one that is in error. Because of the problem with the single row, the entire block of 20 rows is written to the exception file.

Records are written to the exception file only when those records can be correctly identified, as is the case when the shape record type is not valid. Some types of corruption to the shape data (.shp files) and shape index (.shx files) do not allow the appropriate records to be identified. In this case, no records are written to the exception file, and an error message is issued to report the problem.

If you specify a value for this parameter, four files are created on the server machine. See Usage notes for an explanation these files. The stored procedure, which runs as a process that is owned by the DB2 instance owner, must have the necessary privileges on the server to create the files. If the files already exist, the stored procedure returns an error.

The data type of this parameter is VARCHAR(256).

messages_file

Specifies the full path name of the file (on the server machine) that is to contain messages about the import operation. Although you must specify a value for this parameter, the value can be null. If the parameter is null, no file for DB2 Spatial Extender messages is created.

The messages that are written to the messages file can be:

Informational messages, such as a summary of the import operation
Error messages for data that could not be imported, for example because of different coordinate systems

These messages correspond to the shape data that is stored in the exception file (identified by the exception_file parameter).

The stored procedure, which runs as a process that is owned by the DB2 instance owner, must have the necessary privileges on the server to create the file. If the file already exists, the stored procedure returns an error.

The data type of this parameter is VARCHAR(256).

Output parameters

msg_code: Specifies the message code that is returned from the stored procedure. The value of this output parameter identifies the error, success, or warning condition that was encountered during the processing of the procedure. If this parameter value is for a success or warning condition, the procedure finished its task. If the parameter value is for an error condition, no changes to the database were performed.
The data type of this output parameter is INTEGER.
msg_text: Specifies the actual message text, associated with the message code, that is returned from the stored procedure. The message text can include additional information about the success, warning, or error condition, such as where an error was encountered.
The data type of this output parameter is VARCHAR(1024).

Usage notes

The ST_IMPORT_SHAPE stored procedure uses from one to four files:

The main shape file (.shp extension). This file is required.
The shape index file (.shx extension). This file is optional. If it is present, performance of the import operation might improve.
A dBASE file that contains attribute data (.dbf extension). This file is required only if attribute data is to be imported.
The projection file that specifies the coordinate system of the shape data (.prj extension). This file is optional. If this file is present, the coordinate system that is defined in it is compared with the coordinate system of the spatial reference system that is specified by the srs_id parameter.

The following table describes how dBASE attribute data types are mapped to DB2 data types. All other attribute data types are not supported.

Table 1. Relationship between DB2 data types and dBASE attribute data types
.dbf type	.dbf length¹	.dbf decimals ²	SQL type	Comments
N	< 5	0	SMALLINT
N	< 10	0	INTEGER
N	< 20	0	BIGINT
N	len	dec	DECIMAL(len,dec)	len<32
F	len	dec	REAL	len + dec < 7
F	len	dec	DOUBLE
C	len		CHAR(len)
L			CHAR(1)
D			DATE

Note: This table includes the following variables, both of which are defined in the header of the dBASE file:

len, which represents the total length of the column in the dBASE file. DB2 Spatial Extender uses this value for two purposes:
- To define the precision for the SQL data type DECIMAL or the length for the SQL data type CHAR
- To determine which of the integer or floating-point types is to be used
dec, which represents the maximum number of digits to the right of the decimal point of the column in the dBASE file. DB2 Spatial Extender uses this value to define the scale for the SQL data type DECIMAL.

For example, assume that the dBASE file contains a column of data whose length (len) is defined as 20. Assume that the number of digits to the right of the decimal point (dec) is defined as 5. When DB2 Spatial Extender imports data from that column, it uses the values of len and dec to derive the following SQL data type: DECIMAL(20,5).

Example

This example shows how to use the DB2 command line processor to invoke the ST_IMPORT_SHAPE stored procedure. This example uses a DB2 CALL command to import a shape file named /tmp/officesShape into the table named OFFICES:

call DB2GSE.ST_IMPORT_SHAPE('/tmp/officesShape',NULL,'USA_SRS_1',NULL,
                     'OFFICES',NULL,0,NULL,'LOCATION',NULL,NULL,NULL,NULL,
                     NULL,NULL,NULL,NULL,'/tmp/import_msg',?,?)

The two question marks at the end of this CALL command represent the output parameters, msg_code and msg_text. The values for these output parameters are displayed after the stored procedure runs.