ST_EXPORT_SHAPE procedure

Use this stored procedure to export a spatial column and its associated table to a shape file.

Authorization

The user ID under which this stored procedure is invoked must have the necessary privileges to successfully execute the SELECT statement from which the data is to be exported.

The stored procedure, which runs as a process that is owned by the Db2® instance owner, must have the necessary privileges on the server machine to create or write to the shape files.

Syntax

Parameter descriptions

file_name

Specifies the full path name of a shape file to which the specified data is to be exported. You must specify a non-null value for this parameter.

You can use the ST_EXPORT_SHAPE stored procedure to export a new file or to export to an existing file by appending the exported data to it:

If you are exporting to a new file, you can specify the optional file extension as .shp or .SHP. If you specify .shp or .SHP for the file extension, Db2 Spatial Extender creates the file with the specified file_name value. If you do not specify the optional file extension, Db2 Spatial Extender creates the file that has the name of the file_name value that you specify and with an extension of .shp.
If you are exporting data by appending the data to an existing file, Db2 Spatial Extender first looks for an exact match of the name that you specify for the file_name 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.

If the value of the append_flag parameter indicates that you are not appending to an existing file, but the file that you name in the file_name parameter already exists, Db2 Spatial Extender returns an error and does not overwrite the file.

See Usage notes for a list of files that are written 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 machine to create or write to the files.

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

append_flag

Indicates whether the data that is to be exported is to be appended to an existing shape file. Although you must specify a value for this parameter, the value can be null. Indicate whether you want to append to an existing shape file as follows:

If you want to append data to an existing shape file, specify any value other than 0 (zero) and null. In this case, the file structure must match the exported data; otherwise an error is returned.
If you want to export to a new file, specify 0 (zero) or null. In this case, Db2 Spatial Extender does not overwrite any existing files.

The data type of this parameter is SMALLINT.

output_column_names

Specifies one or more column names (separated by commas) that are to be used for non-spatial columns in the output dBASE file. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the names that are derived from the SELECT statement are used.

If you specify this parameter but do not enclose column names 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, as specified in the select_statement parameter, excluding the spatial column.

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

select_statement

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. You must specify a non-null value for this parameter.

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

messages_file

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

The messages that are sent to this messages file can be:

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

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.

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

You can export only one spatial column at a time.

The ST_EXPORT_SHAPE stored procedure 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.

Example

This example shows how to use the Db2 command line processor to invoke the ST_EXPORT_SHAPE stored procedure. This example uses a Db2 CALL command to export all rows from the CUSTOMERS table to a shape file that is to be created and named /tmp/export_file:


call DB2GSE.ST_EXPORT_SHAPE('/tmp/export_file',0,NULL,
     'select * from customers','/tmp/export_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.