List of Supported Formats

Search the Online Transformer Gallery

 

Reader Directives

The suffixes listed are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the DB2 Spatial reader is DB2SPATIAL.

DATASET

Required/Optional: Required

This specifies the data source name for the DB2 Spatial database. The data source name must have been set up in the Client Configuration Assistant or on the command line.

Example:

DB2SPATIAL_DATASET citySource

Workbench Parameter: Source IBM DB2 Spatial Dataset

USER_NAME

Required/Optional: Required

The name of the user who will access the database.

Example:

DB2SPATIAL_USER_NAME shadow

Workbench Parameter: User Name

PASSWORD

Required/Optional: Required

The password to access the database.

Example:

DB2SPATIAL_PASSWORD puppy

Workbench Parameter: Password

DEF

Required/Optional

Optional

The syntax of the definition is:

DB2SPATIAL_DEF <tableName> \
	[db2_type <type>] \
	[db2_envelope_minx <xmin>]	\
	[db2_envelope_miny <ymax>]	\
	[db2_envelope_maxx <xmin>]	\
	[db2_envelope_maxy <ymax>]	\
	[db2_spatial_predicate <spatialPredicate>] \
	[db2_predicate_result <predicateResult>] \
	[db2_where_clause <whereClause>]	\
	[db2_sql <sqlQuery>] \
	[<fieldName> <fieldType>] +

The <fieldType> of each field must be given, but it is not verified against the database definition for the field. In effect, it is ignored.

The column(s) which has geometry should not be specified on the DEF line. In case a feature type has more than one registered geometry column or layer, than DB2 Spatial Reader module will arbitrarily choose one as the primary geometry column and consider the other(s) as attribute columns.

The <tableName> can be either fully qualified or not. A fully qualified table name consist of two parts separated by a period (.). The first part is the <schema name> and second part is the <table name>. The <table name> part must match a table in the schema specified by the <schema name> part of the <tableName>. If a schema name is not provided as part of the table name, then the username will be considered the schema name. This will be used as the feature type of all the features read from the table. For example, if a user wants to read a table from its own schema then only the table name can be provided, but if the user wants to read from a different user’s schema, then table name should be qualified with schema name.

The definition allows specification of separate search parameters for each table. If any of the configuration parameters are given, they will override, for that table, whatever global values have been specified by the reader directives listed above. If any of these parameters is not specified, the global values will be used.

The following table summarizes the definition line configuration parameters:

Parameter

Contents

db2_type

This specifies the type of geometry the features to be read from the layer will have.

db2_geometry_column

This specifies the spatial layer or geometry column to use for reading spatial data in case the table has multiple geometry/spatial columns.

db2_envelope_minx db2_envelope_miny db2_envelope_maxx db2_envelope_maxy

These specify the spatial extent of the features to be read from the layer. If these are not all specified, the values from the <ReaderKeyword>_SEARCH_ENVELOPE directive are used.

db2_spatial_predicate

This specifies the spatial predicate to be tested for this layer. Its default value is set to INTERSECTS.

Note: This DEF line option is valid only if there is a valid spatial envelope specified by db2_envelope_minx, db2_envelope_miny, db2_envelope_maxx and db2_envelope_maxy.

db2_predicate_result

This specifies the result to be used for the Spatial predicate specified in db2_spatial_predicate option.

db2_where_clause

This specifies the SQL WHERE clause applied to the attributes of the layer’s features to limit the set of features returned. If this is not specified, the value of the <ReaderKeyword>_WHERE_CLAUSE directive is used.

db2_sql

This specifies an SQL SELECT query to be used as the source for the results. If this is specified, the DB2 Spatial reader will execute the query, and use the resulting rows as the features instead of reading from the table <layerName>. All returned features will have a feature type of <layerName>, and attributes for all columns selected by the query.

The db2_where_clause and all parameters which specify a spatial constraint – db2_envelope_minx, db2_interaction, and so on – are ignored if db2_sql is supplied.

If no <whereClause> is specified, all rows in the table will be read and returned as individual features. If a <whereClause> is specified, only those rows that are selected by the clause will be read. Note that the <whereClause> does not include the word “where”.

The db2_sql parameter allows a user to specify an arbitrary SQL SELECT query. If this is specified, FME will execute the query, and use each row of data returned from the query to define a feature. Each of these features will be given the feature type named in the DEF line, and will contain attributes for every column returned by the SELECT. In this case, all DEF line parameters regarding a WHERE clause or spatial querying is ignored, as it is possible to embed this information directly in the text of the <sqlQuery>.

The following example joins the tables ROADS and ROADNAMES, placing the resulting data into FME features with a feature type of MYROADS. Imagine that ROADS defines the geometry for the roads, and has a numeric field named ID, and that ROADNAMES joins the numeric field ID with character arrays with the roads’ names.

DB2SPATIAL_DEF MYROADS	\
	db2_sql “SELECT * FROM ROADS, \
       		     ROADNAMES WHERE ROADS.ID = ROADNAMES.ID”

IDs

Required/Optional: Optional

This optional specification is used to limit the available and defined database tables files that will be read. If no IDs are specified, then all defined and available tables are read. The syntax of the IDs directive is:

DB2SPATIAL_IDs <featureType1> \
	<featureType2> \
	<featureTypeN>

The feature types must match those used in DEF lines.

The example below selects only the ROADS table for input during a translation:

DB2SPATIAL_IDs ROADS

Workbench Parameter: Feature Types to Read

SIMPLIFY_AGGREGATES

Required/Optional: Optional

This directive specifies whether multi-geometry or aggregate features with one member are read as stored or simplified and read as single member. e.g. an aggregate of points or multipoint features with only one point will be returned as a simple point if the value of this directive is YES.

Values: YES | NO

Default value: NO

Example:

The syntax of the DB2SPATIAL_SIMPLIFY_AGGREGATES directive is:

DB2SPATIAL_SIMPLIFY_AGGREGATES YES

Workbench Parameter: Simplify Aggregate Geometry

SPATIAL_PREDICATE

Required/Optional: Optional

This specifies the type of spatial relationship which must exist between the search envelope and the geometry in the target layer. Any supported relationship, in combination with the SPATIAL_PREDICATE_RESULT directive, can be used to filter the features being read.

Values: CONTAINS, CROSSES, DISJOINT, EQUALS, INTERSECTS, ORDERINGEQUALS, OVERLAPS, TOUCHES, WITHIN

Default value: INTERSECTS

For example,

DB2SPATIAL_SPATIAL_PREDICATE INTERSECTS
DB2SPATIAL_SPATIAL_PREDICATE_RESULT FALSE

This would result in a spatial filter using DB2 Spatial’s native spatial function

DB2GSE.ST_Intersects( g1 geometry, g2 geometry) = 0

where g1 is the search envelope and g2 is the target feature. This will cause FME to return only those features that satisfy the spatial predicate above.

The following table lists the valid spatial predicate relationships.

Search Method

Description

CONTAINS

Determines whether the search envelope is completely contained by the target feature.

CROSSES

Determines whether the intersection of search envelope and the target feature results in a geometry object whose dimension is one less than the maximum dimension of the source geometries. Also determines if the intersection object contains points that are interior to both source geometries and are not equal to either of the source objects.

DISJOINT

Determines whether the intersection of search envelope with the target feature is an empty set.

EQUALS

Determines whether the search envelope and target feature are of the same type and have identical x,y coordinate values.

INTERSECTS

Determines whether the intersection of search envelope and target feature does not result in an empty set. This is the exact opposite of DISJOINT.

ORDERINGEQUALS

Determines whether the search envelope and target feature are equal and the coordinates are in the same order.

OVERLAPS

Determines whether the search envelope and target feature overlap each other.

TOUCHES

Determines whether any of the points common to search envelope and target feature intersect the interiors of both geometries. At least one geometry must be a linestring, polygon, multilinestring, multipolygon.

WITHIN

Determines whether the target feature is completely within the search envelope.

This is exactly opposite to CONTAINS.

For more details on Spatial predicate, please refer to the IBM DB2 Spatial Extender User’s Guide and Reference.

Workbench Parameter: Spatial Relationship to Search Envelope

WHERECLAUSE

Required/Optional: Optional

This specifies an SQL WHERE clause, which is applied to the table’s columns to limit the resulting features. This feature is currently limited to apply only to the attributes of the target table, and does not allow for joining multiple tables together. The effect of table joins can be achieved using the object model, by specifying the entire queries in the DEF line with a db2_sql parameter.

By default, there is no WHERE clause applied to the results, so all features in the layer are returned.

Example:

DB2SPATIAL_WHERECLAUSE “se_row_id > 45”

Workbench Parameter: WHERE Clause

TRANSACTION_INTERVAL

Required/Optional: Optional

The features can be read from the DB2 Spatial database using a bulk reading technique to maximize performance. Normally 1000 rows of data are read from the database at a time. However, when we are reading LOB (BLOBs or CLOBs) data , we are restricted to a transaction interval of size 1. Since geometry columns are normally BLOB types, reading of spatial features will not be affected by this directive.

This directive allows users to tune the performance of the reader. It specifies how many rows are read from the database at a time.

Example:

DB2SPATIAL_TRANSACTION_INTERVAL “se_row_id > 45”

Workbench Parameter: Transaction Buffer Size

BEGIN_SQL{n}

Sometimes, you must execute some SQL statements prior to opening a table. For example, it may be necessary to ensure that a view exists before attempting to read from it.

Upon opening a connection to read from a database, the reader looks for the directive <ReaderKeyword>_BEGIN_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

Required/Optional

Optional

Workbench Parameter

Begin SQL

END_SQL{n}

Sometimes you must execute some SQL statements after a set of tables has been read. For example, it may be necessary to clean up a temporary view after creating it in BEGIN_SQL.

Just before closing a connection on a database, the reader looks for the directive <ReaderKeyword>_END_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

Required/Optional

Optional

Workbench Parameter

End SQL

PERSISTENT_CONNECTION

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 value: NO

Example:

DB2SPATIAL_PERSISTENT_CONNECTION YES

Workbench Parameter: Persistent Connection

RETRIEVE_ALL_SCHEMAS

This parameter is applicable only when generating a mapping file, generating a workspace, or when retrieving schemas in an FME Objects application.

When set to yes, schemas for all of the tables and feature classes in the database are returned.

When set to no (or missing), and if RETRIEVE_ALL_TABLE_NAMES is similarly set to no (or missing), only schemas requested by the IDs parameter are returned.

Required/Optional

Optional

Values

YES | NO (default)

NO: The reader will return the schemas for the feature types specified in the IDs. If no features are specified in IDs, then:

  • for Enterprise Geodatabases, FME will not return any schema features;
  • for Personal and File Geodatabases, FME returns the schema features for all the tables.

If this value is not specified, then it is assumed to be No.

YES: Indicates to the reader to return all the schemas of the tables in the database.

Mapping File Syntax

Not applicable.

FME Objects applications would include RETRIEVE_ALL_SCHEMAS followed by “YES” in the parameters array passed to IFMEUniversalReader::open().

Workbench Parameter

Not applicable

RETRIEVE_ALL_TABLE_NAMES

This parameter is only applicable when generating a mapping file, generating a workspace or when retrieving schemas in an FME Objects application.

When set to yes, and if RETRIEVE_ALL_SCHEMAS is set to no (or missing), names for all of the tables and feature classes in the database are returned. When set to no (or missing), and if RETRIEVE_ALL_SCHEMAS is similarly set to no (or missing), the schemas requested by the IDs directive are returned.

Note: If RETRIEVE_ALL_SCHEMAS is also set to Yes, then RETRIEVE_ALL_SCHEMAS takes precedence.

Required/Optional

Optional

Values

YES | NO (default)

Mapping File Syntax

Not applicable.

FME Objects applications would include RETRIEVE_ALL_TABLE_NAMES followed by “YES” in the parameters array passed to IFMEUniversalReader::open().

Workbench Parameter

Not applicable (used when you browse a Table List)

SEARCH_ENVELOPE

Using the minimum and maximum x and y parameters, define a bounding box that will be used to filter the input features. Only features that interact with the bounding box are returned.

If all four coordinates of the search envelope are specified as 0, the search envelope will be disabled.

Mapping File Syntax

<ReaderKeyword>_SEARCH_ENVELOPE <minX> <minY> <maxX> <maxY>

Note: If all four coordinates of the search envelope are specified as zero, the search envelope will be disabled.

Required/Optional

Optional

Workbench Parameter

Minimum X, Minimum Y, Maximum X, Maximum Y

SEARCH_ENVELOPE_COORDINATE_SYSTEM

This directive specifies the coordinate system of the search envelope if it is different than the coordinate system of the data.

The COORDINATE_SYSTEM directive, which specifies the coordinate system associated with the data to be read, must always be set if the SEARCH_ENVELOPE_COORDINATE_SYSTEM directive is set.

If this directive is set, the minimum and maximum points of the search envelope are reprojected from the SEARCH_ENVELOPE_COORDINATE_SYSTEM to the reader COORDINATE_SYSTEM prior to applying the envelope.

Required/Optional

Optional

Mapping File Syntax

<ReaderKeyword>_SEARCH_ENVELOPE_COORDINATE_SYSTEM <coordinate system>

Workbench Parameter

Search Envelope Coordinate System

CLIP_TO_ENVELOPE

This directive specifies whether or not FME should clip features to the envelope specified in the SEARCH_ENVELOPE directive.

Values

YES | NO (default)

Mapping File Syntax

<ReaderKeyword>_CLIP_TO_ENVELOPE [yes | no]

Workbench Parameter

Clip To Envelope

EXPOSED_ATTRS

This directive allows the selection of format attributes to be explicitly added to the reader feature type.

This is similar to exposing format attributes on a reader feature type once it has been generated; however, it is even more powerful because it enables schema-driven applications other than Workbench to access and leverage these attributes as if they were explicitly on the schema as user attributes.

The result of picking a list of attributes is a comma-separated list of attribute names and types that will be added to the schema features. Currently all reader feature types will receive the same set of additional schema attributes for a given instance of the reader.

Required/Optional

Optional

Mapping File Syntax

Not applicable.

While it is possible for FME Objects applications to invoke this directive, the required format is not documented.

This directive is intended for use in our GUI applications (for example, Workbench) only.

Workbench Parameter

Additional Attributes to Expose