db2se export_shape command
The db2se export_shape command exports a spatial column and its associated table to a shape file.
You can 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.
Also, 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
Command parameters
- Specifies the name of the database containing the table to be exported.
- Specifies the database user ID that has DATAACCESS authority on the database indicated by database_name.
- Specifies the password for user_id.
- Specifies the full path name of a shape file to which the specified data is to be exported. See
Usage notes for a complete list of files that are written on the
Db2 server. The
maximum length for this parameter is 256.
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 as 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 with the name file_name.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 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. If the append_flag value indicates that you are not appending to an existing file, but the file already exists, Db2 Spatial Extender returns an error and does not overwrite the file.
- Indicates whether the data that is to be exported is to be appended
to an existing shape file. The possible values for this parameter
are:
- A non-zero value in append_flag to indicate that you want to append data to an existing shape file. If the existing file structure does not match the exported data, an error is returned.
- A value of 0 in append_flag to indicate that you want to export to a new file. Db2 Spatial Extender does not overwrite any existing files.
- 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 32 672 characters.
- 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 32 672 characters.
- Specifies the full path name of the file in the Db2 server to which
Db2 Spatial Extender writes
messages about the export operation. If you specify this parameter and the file already exists, an
error is returned and the export operation terminated. 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 export operation
- Error messages for data that could not be exported, for example because of different coordinate systems
The maximum length for this parameter is 256.
- 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 export operation takes place on the Db2 server and the files are created on the Db2 server.
- 1 to indicate the export operation takes place on the client and the files are created on the client.
If you do not specify this parameter, the 0 value is the default.
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 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.
| 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
db2se export_shape mydb -fileName /home/myaccount/myshapefile
-selectStatement "select * from mytable" -client 1