Writer Directives

The directives processed by the DB2 Spatial writer are listed below. The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the DB2 Spatial writer is DB2SPATIAL when using the object model.

DATASET, USER_NAME, PASSWORD, PERSISTENT_CONNECTION, TRANSACTION_INTERVAL, BEGIN_SQL{}, and END_SQL{}

The DATASET, USER_NAME, PASSWORD, PERSISTENT_CONNECTION, TRANSACTION_INTERVAL, BEGIN_SQL{}, and END_SQL{} directives operate in the same manner as they do for the DB2 Spatial reader. The remaining writer-specific directives are listed belowDEF

Required/Optional: Optional

Each DB2 Spatial table must be defined before it can be written. The general form of a DB2 Spatial definition statement is:

DB2SPATIAL_DEF <tableName>  \
    [db2_overwrite_table <YES|NO|TRUNCATE>] \
    [db2_multi_geometry <YES|NO|FIRST_FEATURE>] \
    [db2_geometry_column <geometry>] \
    [db2_offset_x  <x offset value>] \
    [db2_offset_y <y offset value>] \
    [db2_scale_x <x scale value>] \
    [db2_scale_y <y scale value>] \
    [db2_grid_0 <finest grid size>] \
    [db2_grid_1 <middle grid size>] \
    [db2_grid_2 <coarsest grid size>] \
    [db2_sql <sql statement>] \
    [db2_update_key_columns <column>[,<column>]...] \
	 	[db2_delete_key_columns <column>[,<column>]...] \
		[<fieldName> <fieldType>]*

The table definition allows complete control of the layer that will be created. If the layer already exists, the majority of the DEF line parameters will be ignored and need not be given. As well, if the table already exists in the database, then it is not necessary to list the fields and their types – FME will use the schema information in the database to determine this. FME will ignore the field names and types specified on the DEF line, except for the one with type geometry.

If the table does not exist, then the field names and types are used to first create the table. In any case, if a <fieldType> is given, it may be any field type supported by the target database.

The DB2 Spatial writer will use db2_geometry_column parameter to set the name of geometry column for the new table. If the db2_geometry_column parameter is not specified then a default name “geometr” will be used for the geometry column.

The configuration parameters present on the definition line are described in the following table:

Parameter	Contents
db2_overwrite_table	This parameter can have one of <YES|NO|TRUNCATE> option. If YES, then the table will be dropped and created again. If TRUNCATE, then all the rows from the table will be deleted. If NO, then data will be appended to the existing table.
db2_multi_geometry	This specifies whether the db2 types for point, linestring and polygon should be written as multi-geometries or single geometries. If YES, the table created has multi-geometries (that is, the geometry column type will be ST_MULTIPOINT, and the features are coerced into multi-geometries if they are not already). If NO, the geometry column of the created table is singular (that is, ST_POINT), and multi-geometries are split. FIRST_FEATURE allows this setting to be based on the first feature in the table.   This setting is used for DB2SPATIAL-to-DB2SPATIAL translations.
db2_geometry_column	This parameter can be used to specify name of the spatial layer (geometry column name). If it is not specified db2 spatial writer module will use default name “geometry” for the spatial layer.
db2_offset_x	The x offset value for the dataset, defaults to 0. If this parameter is non-zero, then it overrides the global OFFSET_X directive.
db2_offset_y	The y offset value for the dataset, defaults to 0. If this parameter is non-zero, then it overrides the global OFFSET_Y directive.
db2_scale_x	The x scale value for the dataset, defaults to 1. If this parameter is not equal to 1, then it overrides the global SCALE_X directive
db2_scale_y	The y scale value for the dataset, defaults to 1. If this parameter is not equal to 1, then it overrides the global SCALE_Y directive.
db2_grid_0	This parameter specifies the finest spatial index grid size. If 0, then a spatial index is not created.
db2_grid_1	This parameter specifies the middle spatial index grid size. If 0, then a spatial index is not created.
db2_grid_2	This parameter specifies the coarsest spatial index grid size. If 0, then a spatial index is not created.
db2_sql	This specifies an SQL INSERT or UPDATE query to be used to define the results. If this is specified, the DB2 Spatial writer will execute the query, defining one row for each feature from FME. The values in the query are specified by embedding “?attrName” in the query itself, where attrName is the name of the FME feature’s attribute. For example: INSERT INTO MyTable VALUES(?ID,?NAME,?DESC) In this example, the attributes named ID, NAME and DESC will be taken from each feature written to <tableName>. or INSERT INTO MyTable (ID,NAME) VALUES(?ID,?NAME) In this example, the attributes named ID and NAME will be taken from each feature written to <tableName>. If not all attributes are to be written, then a column list should be specified as shown in the second example statement where 2 out of 3 columns are being written. It is also very important that the attributes named in the query must be listed on the DEF line so that FME knows what type to use. There is no necessary or implied correlation between the FME attribute name and the db2 column name.
db2_update_key_ columns	This instructs the DB2 Spatial writer to perform an UPDATE operation on the table, rather than performing an INSERT. The argument is a comma-separated list of the columns which are matched against the corresponding FME attributes’ values to specify which rows are to be updated with the other attribute values. For example: db2_update_key_columns ID,NAME In this case the FME attribute is always matched against the db2 column with the same name. Also, the target table is always the feature type specified in the DEF line. Each column listed with the db2_update_key_columns directive must be defined with a type on the DEF line, in addition to the columns whose values will be updated by the operation. This cannot be used with db2_delete_key_columns. Also, the keys cannot be of type BLOB, CLOB, or LONG_VARCHAR.
db2_delete_key_ columns	This instructs the DB2 Spatial writer to perform a DELETE operation on the table, rather than performing an INSERT. The argument is a comma-separated list of the columns which are matched against the corresponding FME attributes’ values to specify which rows are to be deleted when their values match the other attribute values. For example: db2_delete_key_columns ID,NAME would delete those rows in the table whose values match the attribute values passed in through this DEF line. The FME attribute is always matched against the DB2 Spatial column with the same name. Also, the target table is always the feature type specified in the DEF line. Each column listed with the db2_delete_key_columns directive must be defined with a type on the DEF line, in addition to the columns whose values will be updated by the operation. This cannot be used with db2_update_key_columns. Also, the keys cannot be of type BLOB, CLOB, or LONG_VARCHAR.

TRANSACTION_INTERVAL

This statement informs the FME about the number of features to be placed in each transaction before a transaction is committed to the database.

If the DB2SPATIAL_TRANSACTION_INTERVAL statement is not specified, then a value of 1000 is used as the transaction interval.

Parameter	Contents
<transaction_interval>	The number of features in a single transaction.

Default: 1000

Example:

DB2SPATIAL_TRANSACTION_INTERVAL 2500

Workbench Parameter: Transaction Interval

PERSISTENT_CONNECTION

Required/Optional: Optional

A user may want to keep a connection to a database for reuse during a particular FME session. For example, when running a batch of 100 mapping files on the same database connection, it may be desirable to keep a connection open and save the processing time required to make and break a database connection.

A database connection will be determined to be the same when the database name, the username, the password, and the transaction interval are the same.

Values: YES | NO

Default: NO

Example:

DB2SPATIAL_PERSISTENT_CONNECTION YES

Workbench Parameter: Persistent Connection

ABORT_ON_BAD_DATA

Required/Optional: Optional

Some features’ geometries may fail DB2 Spatial Extender’s check constraints based on the offset, scale, and coordinate system values. These features, as well as others with out-of-range or invalid attribute values, will be rejected and cannot be written to the database. If the value of this directive is YES then the translation will be aborted immediately after encountering such a problem. If this directive is set to NO then the translation will continue but the rejected features will not be written to the database.

Values: YES | NO

Default: YES

Example:

DB2SPATIAL_ABORT_ON_BAD_DATA YES

Workbench Parameter: Abort Translation On Bad Data

OFFSET_X

Required/Optional: Optional

This directive can be used to set the global x offset for the entire translation. If a dataset contains many different tables but the same x offset applies to all of them, then this is a convenient way of setting the x offset. This value can be overridden by DEF line parameter db2_offset_x.

Default: 0

Example:

DB2SPATIAL_OFFSET_X -12456

Workbench Parameter: Offset X

OFFSET_Y

Required/Optional: Optional

This directive can be used to set the global y offset for the entire translation. If a dataset contains many different tables but the same y offset applies to all of them, then this is a convenient way of setting the y offset. This value can be overridden by DEF line parameter db2_offset_y.

Default: 0

Example:

DB2SPATIAL_OFFSET_Y -1245

Workbench Parameter: Offset Y

SCALE_X

Required/Optional: Optional

This directive can be used to set the global x scale value for the entire translation. If a dataset may contains many different tables but the same x scale applies to all of them, then this is a convenient way of setting the x scale value. This value can be overridden by DEF line parameter db2_scale_x.

Default: 1

Example:

DB2SPATIAL_SCALE_X 1000

Workbench Parameter: Scale X

SCALE_Y

Required/Optional: Optional

This directive can be used to set the global y scale value for the entire translation. If a dataset contain many different tables but the same y scale applies to all of them, then this is a convenient way of setting the y scale value. This value can be overridden by DEF line parameter db2_scale_y.

Default: 1

Example:

DB2SPATIAL_SCALE_Y 1000

Workbench Parameter: Scale Y

GRID_0

Required/Optional: Optional

This directive can be used to set the global finest grid size for the spatial grid index . If a dataset contains many different tables but the same finest grid size applies to all of them, then this is a convenient way of setting the finest grid size value. This value can be overridden by DEF line parameter db2_grid_0.

Default: 0

Example:

DB2SPATIAL_GRID_0 10

Workbench Parameter: Finest Spatial Grid Index Size

GRID_1

Required/Optional: Optional

This directive can be used to set the global middle grid size for the spatial grid index. If a dataset contains many different tables but the same middle grid size applies to all of them, then this is a convenient way of setting the middle grid size value. This value can be overridden by DEF line parameter db2_grid_1.

Default: 0

Example:

DB2SPATIAL_GRID_1 100

Workbench Parameter: Middle Spatial Grid Index Size

GRID_2

Required/Optional: Optional

This directive can be used to set the global coarsest grid size for the spatial grid index. If a dataset contains many different tables but the same coarsest grid size applies to all of them, then this is a convenient way of setting the coarsest grid size value. This value can be overridden by DEF line parameter db2_grid_2.

Default: 0

Example:

DB2SPATIAL_GRID_2 1000

Workbench Parameter: Coarsest Spatial Grid Size