The directives listed below are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the Netezza reader is NETEZZA.
DATASET
Required/Optional
Required
Mapping File Syntax
SourceDataset_NETEZZA_SPATIAL sample
Workbench Parameter
Source Netezza ODBC DSN
USER_NAME
Required/Optional
Optional
Mapping File Syntax
NETEZZA_USER_NAME bond007
Workbench Parameter
User ID
PASSWORD
Required/Optional
Optional
Mapping File Syntax
NETEZZA_PASSWORD moneypenny
Workbench Parameter
Password
DEF
The syntax of the reader definition is:
NETEZZA_SPATIAL_DEF <tableName> \
[netezza_sql_clause <sqlClause>] \
[netezza_where_clause <whereClause>] \
[<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 <tableName> will be used to retrieve a table with that name, and will be set as the feature type of all the features read from that table.
The following table summarizes the remaining DEF line parameters:
|
Parameter |
Contents |
| netezza_sql_clause |
Specifies a SQL statement to be executed. No features will be read from the specified table with name <tableName>. Instead, the result (a set of rows) of the SQL statement is output by the reader, one feature per row. If this parameter is specified, netezza_where_clause and <ReaderKeyword>_WHERE_CLAUSE will be ignored. |
|
netezza_where_clause |
Specifies the SQL WHERE clause applied to the target table to limit the rows (features) returned. If not specified, the value of the <ReaderKeyword>_WHERE_CLAUSE directive is used. Do not prefix the SQL WHERE clause with "WHERE". |
WHERE_CLAUSE
This optional specification is used to limit the rows read by the reader from each table. If a given table has no netezza_where_clause 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.
Mapping File Syntax
NETEZZA_WHERE_CLAUSE <whereClause>
Note that the WHERE clause value must not include the prefix "WHERE".
The example below selects only features whose lengths are more than 2000:
NETEZZA_WHERE_CLAUSE LENGTH > 2000
Required/Optional
Optional
Workbench Parameter
WHERE Clause
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 or writer (depending on where the SQL statement is executed) 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 or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.
Required/Optional
Optional
Workbench Parameter
End SQL
IDs
This optional specification is used to limit the available and defined database tables that will be read. If no IDs are specified, then all defined and available tables are read. The syntax of the IDs directive is:
NETEZZA_SPATIAL_IDs <featureType1>
\
<featureType2>
\
...
\
<featureTypeN>
The feature types must match those used in DEF lines.
Required/Optional
Optional
Mapping File Syntax
The example below selects only the ROADS table for input during a translation:
NETEZZA_SPATIAL_IDs ROADS
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
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.
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)