Reader Directives
The suffixes listed are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the DB2 reader is DB2.
DEF
Required/Optional
Optional
Each database table must be defined before it can be read. There are two forms that the definition may take.
The syntax of the first form is:
DB2_DEF <tableName> \ [SQL_WHERE_CLAUSE <whereClause>] \ [<fieldName> <fieldType>] +
In this form, the fields and their types are listed. 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 <tableName> must match a table in the database. This will be used as the feature type of all the features read from the table.
If no <whereClause> is specified, all rows in the table will be read and returned as individual features, unless limited by a global directive:
<ReaderKeyword>_WHERE_CLAUSE
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.”
In this example, the all records whose ID is less than 5 will be read from the supplier table:
DB2_DEF supplier \ SQL_WHERE_CLAUSE "id < 5" \ ID integer \ NAME char(100) \ CITY char(50)
The syntax of the second form is:
DB2_DEF <tableName> \ SQL_STATEMENT <sqlStatement>
In this form, an arbitrary complete <sqlStatement> will be executed. The statement is passed untouched to the database (and therefore may include non-portable database constructions). The results of the statement will be returned, one row at a time, as features to FME. This form allows the results of complex joins to be returned to FME.
Note: If the table has a column of type BIGINT then use the DB2’s CHAR() function to convert it to a string. This also applies when an arbitrary SQL statement is passed to FME using @SQL() function or the SQLExecutor transformer in Workbench. For example,
SELECT CHAR(myBigIntColumn), myID FROM myTable
All features will be given the feature type <tableName>, even though they may not necessarily have come from that particular table. Indeed, with this form, the <tableName> need not exist as a separate table in the database.
In this example, the results of joining the employee and city tables are returned. All attributes from the two tables will be present on each returned feature. The feature type will be set to complex.
DB2_DEF complex \ SQL_STATEMENT \ "SELECT * FROM EMPLOYEE, CITY WHERE EMPLOYEE.CITY = CITY.NAME"
Using DEF Lines to Read from an ODBC Datasource
This example illustrates how the two forms of the DEF lines can be used to read from an ODBC database source, which is named rogers.
READER_TYPE DB2 DB2_DATASET sampledb DB2_USER_NAME <userName> DB2_PASSWORD <password> # Form 1 of the DEF line is used like this -- it reads just # the two fields we list and applies the where clause DB2_DEF supplier \ db2_where_clause "id < 5" \ ID integer \ CITY char(50) # Form 2 of the DEF line is used like this -- we let SQL # figure out what fields we want and do a complex join # involving 3 tables. The FME features will have whatever # fields are relevant. The "feature type" as far as # FME is concerned is whatever was put on the DEF line. # In this case "complex" is the feature type, even though no # table named "complex" is present in the database. DB2_DEF complex \ db2_sql "SELECT CUSTOMER.NAME, CUSTOMER.ID,
VIDEOS.ID, VIDEOS.TITLE FROM RENTALS, CUSTOMER,
VIDEOS WHERE RENTALS.customerID = CUSTOMER.ID AND
VIDEOS.ID = RENTALS.videoID AND CUSTOMER.ID = 1" # Finally, define the NULL writer as the output -- this will # log everything we read to the log file for inspection. WRITER_TYPE NULL NULL_DATASET null FACTORY_DEF * SamplingFactory \ INPUT FEATURE_TYPE * @Log()
WHERE_CLAUSE
Required/Optional
Optional
This optional specification is used to limit the rows read by the reader from each table. If a given table has no SQL_WHERE_CLAUSE or SQL_STATEMENT specified in its DEF line, the global <ReaderKeyword>_WHERE_CLAUSE value, if present, will be applied as the WHERE specifier of the query used to generate the results. If a table’s DEF line does contain its own SQL_WHERE_CLAUSE or SQL_STATEMENT, it will override the global WHERE clause.
The syntax for this clause is:
DB2_WHERE_CLAUSE <whereClause>
Note: The <whereClause> does not include the word “WHERE.”
The example below selects only the features whose lengths are more than 2000:
DB2_WHERE_CLAUSE LENGTH > 2000
Workbench Parameter
WHERE Clause
IDs
Required/Optional: Optional
This optional specification is used to limit the available and defined database table files that will be read. If no IDs are specified, then all defined and available tables are read. The syntax of the IDs keyword is:
DB2_IDs <featureType1> \ <featureType2> … \ <featureTypeN>
The feature types must match those used in DEF lines.
The example below selects only the HISTORY table for input during a translation:
DB2_IDs HISTORY
Workbench Parameter: Feature Types to Read
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 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)
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:
DB2_PERSISTENT_CONNECTION YES
Workbench Parameter: Persistent Connection
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
Writer Directives
The directives processed by the DB2 Writer are listed below. The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the DB2 writer is DB2.
ABORT_ON_BAD_DATA
Required/Optional: Optional
Some features may contain out-of-range or invalid attribute values. These features 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 features with rejected feature will not be written to the database.
Values: YES | NO
Default: NO
Example:
DB2_ABORT_ON_BAD_DATA YES
Workbench Parameter: Abort Translation on Bad Data
DEF
Required/Optional: Optional
Each database table must be defined before it can be written. For the DB2 writer, only one form of the DEF line is used:
DB2_DEF <tableName> \ [db2_overwrite_table (YES|NO|TRUNCATE)] \ [<fieldName> <fieldType>] +
In this form, the fields and their types are listed. If the table already exists in the database, and db2_overwrite_table is not specified with a parameter of YES, FME will write features to the existing database table. In this case, it is not necessary to list the fields and their types – FME will use the schema information in the database. If the fields and types are listed, they must match those in the database. However, not all fields must be listed.
If the table does not exist, or db2_overwrite_table is specified with a value of YES, 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.
If db2_overwrite_table is set to TRUNCATE and the table exists, all the rows from the table will be deleted.
This example defines the SUPPLIER table for the FME. If the table did not exist, it will be created just before the first SUPPLIER row is written. If the table already exists, the data will be appended to the existing table.
DB2_DEF SUPPLIER \ ID integer \ NAME char(100) \ CITY char(50)
The following example is exactly the same, except that it replaces any existing table named SUPPLIER with a new table having the specified definition. If the table SUPPLIER does not exist in the database, then a new table is simply created.
DB2_DEF SUPPLIER \ db2_overwrite_table YES \ ID integer \ NAME char(100) \ CITY char(50)
In the following example, the definition line only make the pre-existing EMPLOYEE table known to FME:
DB2_DEF EMPLOYEE
Features may later be routed to this table.
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:
DB2_PERSISTENT_CONNECTION YES
Workbench Parameter: Persistent Connection
TRANSACTION_INTERVAL
This statement informs FME about the number of features to be placed in each transaction before a transaction is committed to the database.
If the DB2_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. |
Example:
DB2_TRANSACTION_INTERVAL 5000
Workbench Parameter: Transaction Interval