db2se import_shape command

The db2se import_shape command imports shape file data to a database that is enabled for spatial operations. This command can import shape and attribute data into an existing table or into 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

Command parameters

database_name

The name of the database into which the shape file is to be imported.

-sa 1

The imported data is to be processed using the Spatial Analytics component.

-userId

The database user ID that has DATAACCESS authority for the specified database.

-pw

The password of the user ID that has DATAACCESS authority for the specified database.

-fileName

The fully-qualified name of the shape file from which data is to be imported. If you specify .shp or .SHP as the file extension, Spatial Analytics first looks for an exact match of the name that you specify. If it 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.

The maximum length for this parameter is 256 characters.

-inputAttrColumns

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 32672 characters.

-srsName

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

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

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

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

Specifies whether the import process is to create a new table or use an existing table:

A non-zero value indicates that data is to be imported into a new table. If the table already exists, an error is returned. This is the default.
A value of 0 indicates that data is to be imported into an existing table.

-tableCreationParameters

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

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

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

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

-typeName

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

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

The name of the ID column, which is a column that contains a unique identifier for each row of data. The unique identifiers are generated automatically during the import process. The name specified for this column cannot match the name of any column in the dBASE file. The specified name is converted to uppercase unless it is enclosed in double quotation marks.

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 can be any binary integer type (INTEGER, SMALLINT, or BIGINT).
For a new table, the definition of the ID column depends on the setting of the id_column_is_identity parameter.

-idColumnIsIdentity

If the target table is new, this parameter determines whether the ID column is to be created using the IDENTITY clause. If the target table already exists, this parameter is ignored.

A non-zero value indicates that the ID column is to be created as an identity column:
```
INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 )
```
This is the default.
A value of 0 indicates that the ID column is not to be created as an identity column:
```
INTEGER NOT NULL PRIMARY KEY
```

-restartCount

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

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

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 specified file already exists, the command returns an error.

The maximum length for this parameter is 256 characters.

-messagesFile

Specifies the fully-qualified name of the file on the Db2 server in which messages about the import operation are to be recorded:

Informational messages, such as a summary of the import operation.
Error messages for data that could not be imported, for example due to different coordinate systems. These error messages correspond to the shape data that is stored in the specified exception file.

If you do not specify this parameter, no messages are recorded. If the specified file already exists, the command returns an error.

The maximum length for this parameter is 256 characters.

-client

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. This is the default.
1 to indicate the import operation takes place on the client and the files are accessed from the client.

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 shapefile (.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. The spatial routine 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. The spatial routine 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 the spatial routine 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 shape file data from the file myfile.shp, which is located on the client, into the column MYCOLUMN of the table MYTABLE:

db2se import_shape mydb -sa 1 -fileName myfile -srsName NAD83_SRS_1 
        -tableName MYTABLE -spatialColumnName MYCOLUMN -client 1