ST_EXPORT_SHAPE procedure

Authorization

The user ID under which this stored procedure is invoked must have the necessary privileges to execute the routine and 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, the spatial routine creates the file with the specified file_name value. If you do not specify the optional file extension, the spatial routine 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, the spatial routine first looks for an exact match of the name that you specify for the file_name parameter. If the spatial routine 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, the spatial routine 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, the spatial routine 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 spatial 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.

Example

call ST_EXPORT_SHAPE('/tmp/export_file',0,NULL,
     'select * from customers','/tmp/export_msg',?,?)