DB2 10.5 for Linux, UNIX, and Windows

db2se import_shape command

The db2se import_shape command imports a shape file to a database that is enabled for spatial operations.

This command can import shape and attribute data into an existing table or a new table.

Authorization

The DB2® instance owner ID must have the necessary privileges on the DB2 server to create or write to the exception and messages files.

The user ID must have additional authorization requirements to run this command. The requirements vary depending on whether you are importing into an existing table or into a new table.

Requirements to import into an existing table

The user ID 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

Requirements to import into a new table

The user ID must hold one of the following authorities or privileges:

DBADM and DATAACCESS
CREATETAB authority on the database
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

Command syntax


db2se import_shape command

>>-import_shape--database_name---------------------------------->

>--+------------------------------------+----------------------->
   '- -userId--user_id-- -pw--password -'   

>-- -fileName--file_name---------------------------------------->

>--+-------------------------------------+---------------------->
   '- -inputColumnNames--input_col_names-'   

>-- -srsName--srs_name--+-----------------------------+--------->
                        '- -tableSchema--table_schema-'   

>-- -tableName--table_name-------------------------------------->

>--+----------------------------------+------------------------->
   '- -tableAttrColumns--attr_columns-'   

>--+--------------------------------+--------------------------->
   '- -createTableFlag--create_flag-'   

>--+------------------------------------------+----------------->
   '- -tableCreationParameters--tc_parameters-'   

>-- -spatialColumn--spatial_column------------------------------>

>--+---------------------------+--+-----------------------+----->
   '- -typeSchema--type_schema-'  '- -typeName--type_name-'   

>--+---------------------------+--+-----------------------+----->
   '- -inlineLength--in_length-'  '- -idColumn--id_column-'   

>--+-------------------------------+---------------------------->
   '- -idColumnIsIdentity--id_flag-'   

>--+--------------------------+--------------------------------->
   '- -restartCount--rs_count-'   

>--+-----------------------------+------------------------------>
   '- -commitScope--commit_count-'   

>--+------------------------------+----------------------------->
   '- -exceptionFile--e_file_name-'   

>--+-------------------------------+---------------------------->
   '- -messagesFile--msg_file_name-'   

>--+-----------------------+-----------------------------------><
   '- -client--client_flag-'

Command parameters

Where:

database_name

Specifies the name of the database for which you want to import the shape file.

-userId user_id

Specifies the database user ID that has DATAACCESS authority on the database indicated by database_name.

-pw password

Specifies the password for user_id.

-fileName file_name

Specifies the full path name of a shape file to which the specified data is to be imported. If you specify .shp or .SHP as the file extension, DB2 Spatial Extender first looks for an exact match of the name that you specify with the -fileName parameter. 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 complete list of files that are written on the DB2 server.

The maximum length for this parameter is 256 characters.

-inputColumnNames input_col_names

Specifies a list of attribute columns to import from the dBASE file. If this parameter is not specified, all columns in the file are imported. Use any of the following formats to specify a list of attributes:

A comma-separated list of column names to be imported from the dBASE file as shown in the following example:
```
N(COLUMN1,COLUMN5,COLUMN3,COLUMN7)
```
If the column names are not enclosed in double quotation marks, the column names are converted to uppercase. The resulting names must exactly match the column names in the dBASE file.
A comma-separated list of column numbers to be imported from the dBASE file as shown in the following example:
```
P(1,5,3,7)
```
Columns are numbered beginning with 1. Each number in the list must be separated by a comma.
An empty string "" to indicate that no attribute data is to be imported.

The maximum length for this parameter is 32,672 characters.

-srsName srs_name

Identifies the spatial reference system (SRS) to be used for the geometries that are imported into the spatial column. The srs_name value is converted to uppercase unless you enclose it in double quotation marks.

The spatial column is not registered. The 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 this file is 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 specified SRS. That is, the import process verifies that the extents lie within the minimum and maximum X, Y, Z, and M coordinates of the SRS.

-tableSchema table_schema

Specifies the schema name for the specified table_name. If you do not specify a schema name, the value in the CURRENT SCHEMA special register is used as the schema name for the table or view.

-tableName table_name

Specifies the unqualified name of the table into which the data in the shape file is to be imported. The table_name value is converted to uppercase unless you enclose it in double quotation marks.

-tableAttrColumns attr_columns

Specifies the table column names where attribute data from the dBASE file is to be stored. If this parameter is not specified, the names of the columns in the dBASE file are used.

The number of specified columns must match the number of columns to be 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.

If the column names are not enclosed in double quotation marks, the column names are converted to uppercase. The maximum length for this parameter is 32,672 characters.

-createTableFlag create_flag

Specifies whether the import process is to create a table. The possible values for this parameter are:

A non-zero value in create_flag to create a table. If the table exists, an error is returned.
A value of 0 in create_flag to use an existing table.

If this parameter is not specified, a new table is created.

-tableCreationParameters tc_params

Specifies any options that are to be added to the CREATE TABLE statement that creates the specified table_name.

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

IN tsName INDEX IN indexTsName LONG IN longTsName

The maximum length for this parameter is 32,672 characters.

-spatialColumn spatial_column

Specifies the name of the spatial column in the table into which the shape data is to be imported.

For a new table, this parameter specifies the name of the new spatial column that is to be created. For an existing table, 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.

-typeSchema type_schema

Indicates the schema name of the spatial data type specified in the type_name value. If this parameter is not specified, DB2GSE is used as the schema name.

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

-typeName type_name

Specifies the data type name to be used for the spatial values. If this parameter is not specified, the data type is determined by the shape file from any of the following data types:

ST_Point
ST_MultiPoint
ST_MultiLineString
ST_MultiPolygon

Shape files, by definition, allow a distinction only between points and multipoints. There is no distinction between polygons and multipolygons or between linestrings and multilinestrings.

If you are importing into a new table, type_name data type is also used for the data type of the spatial column. In this 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.

-inlineLength inline_length

For a new table, specifies the maximum number of bytes that are to be allocated for the spatial column within the table. If this parameter is not specified, the default inline length is used.

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

The typical sizes that are needed for various spatial types are as follows:

One point: 292 bytes.
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.

For a complete description of the inline_length value, see the CREATE TABLE statement in the DB2 documentation. Use the ADMIN_EST_INLINE_LENGTH table function to help you estimate the inline length required for geometries in existing tables.

-idColumn id_column

Specifies the column name to be created to contain a unique number for each row of data. The unique values for that column are generated automatically during the import process. You must not specify an id_column name that matches the name of any column in the dBASE file. Some spatial tools require a column with a unique identifier.

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 such as INTEGER, SMALLINT, or BIGINT.
For a new table, the column is defined as follows:
```
INTEGER NOT NULL PRIMARY KEY
```
If id_column_is_identity has a non-zero value, 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.

-idColumnIsIdentity id_column_is_identity

Indicates whether the specified id_column is to be created using the IDENTITY clause. If you specify a non-zero value in id_column_is_identity, the id_column column is created as an identity column. This parameter is ignored for tables that already exist.

-restartCount restart_count

Specifies that the import operation starts with record n + 1. The first n records are skipped. If this parameter is not specified, all records, starting with record number 1, are imported.

-commitScope commit_scope

Specifies that a COMMIT is to be performed after at least n records are imported. If this parameter is not specified, 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.

-exceptionFile exception_file

Specifies the full path name of a shape file in which the shape data that could not be imported is written. If the parameter is not specified, an exception file is not created.

If you specify a value for the parameter and include an optional file extension, specify either .shp or .SHP. If the extension you do not specify an extention, the .shp extension is appended to exception_file.

The exception file contains the complete set of rows for the insert statement that failed. One insert statement can add a multiple number of rows. 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 with incorrect shape data. Because the insert statement fails, the entire set of 20 rows is written to the exception file.

Records that 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, records cannot be 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 DB2 server. See Usage notes for an explanation these files.

If the exception_file file already exists, the command returns an error.

The maximum length for this parameter is 256 characters.

-messagesFile msg_file_name

Specifies the full path name of the file in the DB2 server to which DB2 Spatial Extender writes messages about the import operation. If you do not specify this parameter, DB2 Spatial Extender does not create a messages file.

The following type of messages are written to the messages file:

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 error messages correspond to the shape data that is stored in the specified exception_file exception file.

If the msg_file_name file already exists, the command returns an error.

The maximum length for this parameter is 256 characters.

-client client_flag

Specifies whether the import operation takes place on the client or the DB2 server and where the files are created. The possible values for this parameter are:

0 to indicate the import operation takes place on the DB2 server and the files are accessed from the DB2 server.
1 to indicate the import operation takes place on the client and the files are accessed from the client.

If you do not specify this parameter, the 0 value is the default.

Usage notes

You can perform the import process on the client where the command is executed. This is often more convenient as it does not require access to the DB2 server file system.

The db2se import_shape creates or writes to the following 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

The following command imports the data from the myfile shape file located in the client to the MYTABLE table. The spatial data in myfile is inserted into the MYCOLUMN column in the MYTABLE table.

db2se import_shape mydb -fileName myfile -srsName NAD83_SRS_1 
        -tableName mytable -spatialColumnName mycolumn -client 1