ST_import_shape

Authorization

Syntax

Parameter descriptions

file_name

Specifies the full path name of the shape file that is to be imported. You must specify a non-null value for this parameter.

If you specify the optional file extension, specify either .shp or .SHP. IBM Spatial Support for Db2 for z/OS first looks for an exact match of the specified file name. If IBM Spatial Support for Db2 for z/OS 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.

See Usage notes for a list of required files, which must reside on the server machine. The stored procedure, which runs as a task in the WLM environment, must have the necessary privileges on the server to read the files.

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

input_attr_columns

Specifies a list of attribute columns to import from the dBASE file. Although you must specify a value for this parameter, the value can be null. If this parameter is null, all columns are imported. If the dBASE file does not exist, this parameter must be the empty string or null.

To specify a non-null value for this parameter, use one of the following specifications:

• List the attribute column names. The following example shows how to specify a list of the names of the attribute columns that are to be imported from the dBASE file:

  
  N(COLUMN1,COLUMN5,COLUMN3,COLUMN7)
  

  If a column name is not enclosed in double quotation marks, it is converted to uppercase. Each name in the list must be separated by a comma. The resulting names must exactly match the column names in the dBASE file.
• List the attribute column numbers. The following example shows how to specify a list of the numbers of the attribute columns that are to be imported from the dBASE file:

  
  P(1,5,3,7)
  

  Columns are numbered beginning with 1. Each number in the list must be separated by a comma.
• Indicate that no attribute data is to be imported. Specify "", which is an empty string that explicitly specifies that IBM Spatial Support for Db2 for z/OS is to import no attribute data.

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

srs_name

Identifies the spatial reference system that is to be used for the geometries that are imported into the spatial column. You must specify a non-null value for this parameter.

The spatial column will not be registered. The spatial reference system (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 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 given spatial reference system. That is, the import process verifies that the extents lie within the minimum and maximum possible X, Y, Z, and M coordinates of the SRS.

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

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_schema

Identifies the schema to which the table that is specified in the table_name parameter belongs. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the value in the CURRENT SCHEMA special register is used as the schema name for the table or view.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_name

Identifies the unqualified name of the table into which the imported shape file is to be loaded. You must specify a non-null value for this parameter.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

table_attr_columns

Specifies the table column names where attribute data from the dBASE file is to be stored. Although you must specify a value for this parameter, the value can be null. If this parameter is null, the names of the columns in the dBASE file are used.

If this parameter is specified, the number of names must match the number of columns that are 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.

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

create_table_flag

Specifies whether the import process is to create a new table. Although you must specify a value for this parameter, the value can be null. If this parameter is null or any other value other than 0 (zero), a new table is created. (If the table already exists, an error is returned.) If this parameter is 0 (zero), no table is created, and the table must already exist.

If you want to create a target table in a separate table space, first create the table, and then create the LOB table space, auxiliary table, and index for the target table before using the import shape operation.

After creating the required LOB table space, auxiliary table, and index for the target table, specify 0 (zero) for the create_table_flag option to import shape data and attributes data to the table. The import shape operation does not create a LOB table space, an auxiliary table, or an index for the LOB column.

The data type of this parameter is SMALLINT.

table_creation_parameters

Specifies any options that are to be added to the CREATE TABLE statement that creates a table into which data is to be imported. Although you must specify a value for this parameter, the value can be null. If this parameter is null, no options are added to the CREATE TABLE statement.

To specify any CREATE TABLE options, use the syntax of the Db2 CREATE TABLE statement. For example, to specify a database and Unicode option for character columns, specify:

IN dbName CCSID UNICODE

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

spatial_column

Identifies the spatial column in the table into which the shape data is to be loaded. You must specify a non-null value for this parameter.

For a new table, this parameter specifies the name of the new spatial column that is to be created. Otherwise, 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.

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

type_schema

This parameter is not supported and always will be null. If you specify this parameter, the parameter is ignored.

type_name

Identifies the data type that is to be used for the spatial values. Although you must specify a value for this parameter, the value can be null. The valid data types are ST_Point, ST_MultiPoint, ST_MultiLineString, ST_MultiPolygon, or ST_Geometry.

If this parameter is null, the data type is determined by the shape file and is one of the following types:

• ST_Point
• ST_MultiPoint
• ST_MultiLineString
• ST_MultiPolygon

Note that shape files, by definition, allow a distinction between only points and multipoints, but not between polygons and multipolygons or between linestrings and multilinestrings.

If you are importing into a table that does not yet exist, this data type is also used for the data type of the spatial column.

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

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

inline_length

This parameter is not supported and always will be null. If you specify this parameter, the parameter is ignored.

id_column

Identifies a column that is to be created to contain a unique number for each row of data. The unique values for that column are generated automatically during the import process. Although you must specify a value for this parameter, the value can be null if no column (with a unique ID in each row) exists in the table or if you are not adding such a column to a newly created table. If this parameter is null, no column is created or populated with unique numbers.

Restriction: You cannot specify an id_column name that matches the name of any column in the dBASE file.

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 parameter can be any integer type (INTEGER, SMALLINT, or BIGINT).
• For a new table that is to be created, the column is added to the table when the stored procedure creates it. The column will be defined as follows:

  
  INTEGER NOT NULL PRIMARY KEY 
  

  If the value of the id_column_is_identity parameter is not null and not 0 (zero), the definition is expanded as follows:

  
  INTEGER NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
  ( START WITH 1 INCREMENT BY 1 )
  

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

The data type for this parameter is VARCHAR(128) or, if you enclose the value in double quotation marks, VARCHAR(130).

id_column_is_identity

Indicates whether the specified id_column is to be created using the IDENTITY clause. Although you must specify a value for this parameter, the value can be null. If this parameter is 0 (zero) or null, the column is not created as the identity column. If the parameter is any value other than 0 or null, the column is created as the identity column. This parameter is ignored for tables that already exist.

The data type of this parameter is SMALLINT.

restart_count

Specifies that an import operation is to be started at record n + 1. The first n records are skipped. Although you must specify a value for this parameter, the value can be null. If this parameter is null, all records (starting with record number 1) are imported.

The data type of this parameter is INTEGER.

commit_scope

Specifies that a COMMIT is to be performed after at least n records are imported. Although you must specify a value for this parameter, the value can be null. If this parameter is null, a value of 0 (zero) is used, and no records are committed.

The data type of this parameter is INTEGER.

exception_file

This parameter is not supported and always will be null. If you specify this parameter, the parameter is ignored.

messages_file

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

The messages that are written to the messages file can be:

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

The user who runs the job that calls the stored procedure must have the necessary privileges on the server to create the file. If the file already exists, the file will be overwritten.

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(4096).

When the message code that is returned is 0 (zero), the message text parameter is set to null.

Usage notes

Example

call sysproc.ST_import_shape('/tmp/officesShape',NULL,'USA_SRS_1',NULL, 
'OFFICES',NULL,0,NULL,'LOCATION',NULL,NULL,NULL,NULL, NULL,NULL,NULL,NULL,'
/tmp/import_msg',?,?) 

The two question marks at the end of this CALL statement represent the output parameters, msg_code and msg_text. The values for these output parameters are returned after the stored procedure is called.