db2se export_shape command

The db2se export_shape command exports spatial data to a shape file.

Use this command to create a spatial reference system definition. A spatial reference system is defined by the coordinate system, the precision, and the extents of coordinates that are represented in the specified spatial reference system. The extents are the minimum and maximum possible coordinate values for the X, Y, Z, and M coordinates. The information about the coordinate system is available through the DB2GSE.ST_SPATIAL_REFERENCE_SYSTEMS catalog view.

Authorization

The user must have the necessary privileges to successfully execute the SELECT statement from which the data is to be exported.

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

Command syntax

db2se export_shape command

Command parameters

Where:

database_name

Specifies the name of the database containing the table to be exported.

-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 to which data is to be exported.

When exporting to a new file, you can specify the file extension .shp or .SHP, or you can decline to specify a file extension. If you do not specify a file extension, .shp is used as a default. If the file already exists, an error is returned.
When appending data to an existing file, 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.

-appendFlag

Indicates whether the data that is to be exported is to be appended to an existing shape file:

A value of 0 indicates that data is to be exported to a new file. Spatial Analytics does not overwrite any existing files.
A non-zero value indicates that data is to be appended to an existing shape file. If the existing file structure does not match the exported data, an error is returned.

-outputColumnNames

Specifies one or more column names (separated by commas) that are used for non-spatial columns in the output dBASE file. If this parameter is not specified, the column names from the SELECT statement are used.

If the column names are not enclosed in double quotation marks, the column names are converted to uppercase. The number of specified columns must match the number of columns that are returned from the SELECT statement indicated in the select_statement parameter, excluding the spatial column.

The maximum length for this parameter is 32672 characters.

-selectStatement

Specifies the subselect that returns the data that is to be exported. The subselect must reference exactly one spatial column and any number of attribute columns. The maximum length for this parameter is 32672 characters.

-messagesFile

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

Informational messages, such as a summary of the export operation.
Error messages for data that could not be exported, for example due to different coordinate systems.

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 export 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 export only one spatial column at a time.

You can perform the export 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 export_shape creates or writes to the following four files:

The main shape file (.shp extension).
The shape index file (.shx extension).
A dBASE file that contains data for non-spatial columns (.dbf extension). This file is created only if attribute columns actually need to be exported
A projection file that specifies the coordinate system that is associated with the spatial data, if the coordinate system is not equal to "UNSPECIFIED" (.prj extension). The coordinate system is obtained from the first spatial record. An error occurs if subsequent records have different coordinate systems.

The following table describes how Db2 data types are stored in dBASE attribute files. All other Db2 data types are not supported.

Table 1. Storage of Db2 data types in attribute files
SQL type	.dbf type	.dbf length	.dbf decimals	Comments
SMALLINT	N	6	0
INTEGER	N	11	0
BIGINT	N	20	0
DECIMAL	N	precision+2	scale
REAL FLOAT(1) through FLOAT(24)	F	14	6
DOUBLE FLOAT(25) through FLOAT(53)	F	19	9
CHARACTER, VARCHAR, LONG VARCHAR, and DATALINK	C	len	0	length ≤ 255
DATE	D	8	0
TIME	C	8	0
TIMESTAMP	C	26	0

All synonyms for data types and distinct types that are based on the types listed in the preceding table are supported.

Examples

The following example exports a spatial column named MYCOLUMN and its associated table, MYTABLE, to a shapefile that is located in the client.

db2se export_shape mydb -sa 1 -fileName /home/myaccount/myshapefile -selectStatement "select * from MYTABLE" -client 1