The suffixes shown are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for reading vector data is SDE30, the default for reading raster data is SDERASTER.
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)
PERSISTENT_CONNECTION
Specifies whether to create a connection to SDE that persists and can be shared by other SDE Readers, Writers, and SDE30QueryFactories. When set to YES, the connection will remain open until FME shuts down, even if this reader is finished using it. Otherwise, the connection will be closed when the reader is shut down (unless another reader/writer/queryfactory is still using the connection).
Creating a new connection is an expensive operation. Depending on how FME is being used (that is, if there are multiple instances of the SDE Reader/Writer being used, or if the SDE30QueryFactory is being used to query/update the same SDE), the performance may improve by setting this directive to YES.
Required/Optional
Optional
Values
YES | NO (default)
Mapping File Syntax
SDE30_PERSISTENT_CONNECTION YES
Workbench Parameter
Make Connection Persistent
WHERE
Required/Optional: Optional
Note: Currently only valid for vector features.
The specified WHERE clause is passed to the SDE for processing. WHERE clauses may be arbitrarily complex, limited only by the underlying RDBMS. The WHERE clause may specify join operations; for example, when a retrieval operation is from multiple tables.
When a join operation is being performed, the FME features have all of the attributes from the various tables combined into each feature. If more than one table has a column with the same name, then the feature attributes for those columns will be qualified with the table name to ensure that each attribute is unique. If a query operation is performed across multiple tables, the spatial component will only be taken from the primary table. In fact, none of the secondary tables should have a spatial column (layer or raster column). If the primary table does not have any spatial component, then the feature will not have any spatial component. See IDs for a description of how compound IDs are specified.
If a table contains a layer (vector spatial column), then it is also possible to select one particular feature using the following WHERE clause syntax:
SE_ROW_ID = <integer>
where <integer> is some integer value and SE_ROW_ID is a virtual column provided by the SDE. Only the ‘=’ operator can be used when the query involves SE_ROW_ID. A query involving SE_ROW_ID cannot be arbitrarily complex. It can ONLY be of the form SE_ROW_ID = <integer>.
In the event of an error, the FME will log the entire <WHERE clause>. This clause can then be debugged in the native RDBMS.
Value: <WHERE clause>
The attribute constraint used to limit the features which are retrieved based on attribution. When the WHERE clause references multiple tables, these tables must all be specified as a compound SDE30_ID value. See IDs for a complete discussion.
Example 1:
The WHERE clause specified below instructs the FME to retrieve features from the database for the table(s) identified by <ReaderKeyword>_IDs, and for those rows in which the NUMLANES column has a value of 2 and the SURFACE column has a value of ‘GRAVEL’. Notice the use of the double quotes around the compound WHERE clause.
SDE30_Where “NUMLANES=2 AND SURFACE=’GRAVEL’”
Example 2:
The WHERE clause below illustrates how to perform a join on 3 different tables named REPLICATION, DIST_CENTER, and REPLICATION_LAYERS. Each of these tables must be specified in the associated SDE30_IDs clause for this example to work. Again, notice the use of the double quotes around the compound WHERE clause. Also note the use of the continuation characters when building a long WHERE clause.
SDE30_WHERE \ “REPLICATION.REPLICATION_DATE IS NULL AND“ \ “REPLICATION.AREA_CD = DIST_CENTER.AREA_CD AND“ \ “REPLICATION.IDENT = REPLICATION_LAYERS.IDENT AND“ \ “DIST_CENTER.TOWN_CD = ‘$(TOWN)’“
Workbench Parameter
Where Clause
REMOVE_TABLE_QUALIFIER
Required/Optional: Optional
Specifies whether to keep or remove the table name prefix. If the ArcSDE resides on a database (that is, Microsoft SQL Server) where a specific value for database is set, then the full name for a table is <database_name>.<owner_name>.<table_name>. If the ArcSDE is located on a database (that is, Oracle) that does not require the database field, then the full name of a table is <owner_name>.<table_name>. Setting this keyword to YES indicates that the reader should return the table name without any prefixes. This is useful when:
- creating a workspace that will be passed on to another organization using the same table names,
- performing a translation to another database format but with a different user name, and
- when writing to a file-based format but not wanting the prefix in the name of the feature type.
When this keyword is set to YES during the generation of a mapping file or workspace, the source feature types will be the table names without any prefix; otherwise, they will contain the owner name as a prefix. It is recommended that this keyword not be changed in value after generating the mapping file/workspace as it is possible for no features to be successfully passed onto the writer (since the writer is expecting feature types with different names).
Note that even when REMOVE_TABLE_QUALIFIER is set to YES, if the table is owned by a user other than the current user, the <owner_name> prefix will not be dropped so that the reader will find the correct table; however, the <database_name> prefix will still be dropped.
Value: YES | NO
Default Value: NO
Example:
SDE30_REMOVE_TABLE_QUALIFIER YES
Workbench Parameter: Remove Table Qualifier
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
SEARCH_FEATURE
Required/Optional: Optional
Workbench Parameter: Search Feature
SEARCH_METHOD
Required/Optional: Required when SEARCH_FEATURE is used
Example:
SDE30_SEARCH_METHOD SDE_AREA_INTERSECT
Workbench Parameter: Search Method
SEARCH_METHOD_FILTER
Required/Optional: Optional
Specifies if the features returned will or will not satisfy the spatial constraint. If FALSE is specified, then all features returned will not satisfy the spatial constraint. If TRUE is specified, then the features returned will satisfy the spatial constraint. Specifying FALSE enables you to select all features which are not contained by a feature.
Value: TRUE | FALSE
Default Value: TRUE
Example:
SDE30_SEARCH_METHOD_FILTER FALSE
Workbench Parameter: Search Method Filter
SEARCH_ORDER
Required/Optional: Optional
Note: Currently only valid for vector features.
Default Value: OPTIMIZE
Example:
SDE30_SEARCH_ORDER ATTRIBUTE_FIRST
Workbench Parameter: Search Method Order
IDs
Required/Optional: Required
This statement specifies the tables from which features are to be retrieved. There may be multiple SDE30_IDs statements within a single FME mapping file, in which case the input set of tables comprises the union of all SDE30_IDs statements. The SDE reader module only extracts features from the identified tables.
If a read operation is performing a join operation then the SDE30_IDs for the join operation are specified using a compound ID.
Compound IDs have the following form:
SDE30_IDs A(B,C)
where the primary table in the query is A and the join operation during the read is joining with secondary tables B, and C. Secondary tables cannot contain layers (vector spatial columns) or raster columns.
The join operation that combines the tables is specified on the SDE30_WHERE clause. See the section WHERE, for a description of the SDE30_WHERE clause. The second example below illustrates how the SDE30_IDs are specified when the reader performs a multi-table join operation. Joins are not supported when reading rasters.
The general form of SDE_IDs is the name of the tables from which features are retrieved, separated with spaces:
<[table name[(sec_table[,sec_table]*)]]+>
Example 1:
For simple single table extracts, the SDE30_IDs is a list of table names as shown below, where features are read from the tables roads, then streets. No table combine operation occurs here. Each ID is treated as a separate query to the database.
SDE30_IDs roads streets
Example 2:
For more sophisticated queries in which join operations are desired, the SDE30_IDs has the following form. Note that there are no spaces between tables associated with a single query – spaces are used to separate different table identifiers. Each table identifier may be simple, as in Example 1, or compound, as in this example. The following ID statement is the counterpart to the second example for the SDE30_WHERE clause.
SDE30_IDs REPLICATION(DIST_CENTER,REPLICATION_LAYERS)
Note: Joins are not supported when reading rasters.
ENVELOPE_QUERY_OPTIMIZATION
Required/Optional: Optional
Note: Valid only for vector features.
By default (that is, when this directive is set to NO or is not specified at all), the SDE Reader compares the envelope query used (if an envelope query is being used) to the largest possible extents for a given layer (vector spatial column). The largest possible extents are calculated using the X, Y origin of the layer and the X,Y scale of the layer. If the query envelope completely covers the largest possible extents for the current layer being read, then the envelope query is ignored for the current layer.
If this directive is specified with a value of YES, then the SDE Reader compares the envelope query used (if an envelope query is being used), to the extents of the layer as indicated by running the SDE administration command:
sdelayer -o describe_long -l <table name, layer name>
However, for this optimization to work, it is important that the extents of the layer are correct before running a translation. The extents can be updated using the SDE administration command:
sdelayer -o alter -l <table name, layer name> -E calc
If the extents are actually larger than currently set in SDE, running a translation may result in ignoring the query envelope used and returning erroneous features (i.e., features that would not be within the query envelope). However, if the extents are correct and if the query envelope completely covers the extents of the current layer, it is okay to ignore the envelope query for the current layer.
The purpose of this directive is to attempt to reduce the amount of time SDE spends executing a query on a table. One query is performed on each table being read (a multi-table join is considered one table). The greater the number of rows in a table, the longer a query may take.
If an envelope query is not being used, then setting this directive to either YES or NO will have no effect.
Value: YES | NO
Default Value: NO
Example:
SDE30_ENVELOPE_QUERY_OPTIMIZATION YES
CHILD_VERSION_NAME
Required/Optional: Optional
Workbench Parameter: Child Version Name
MAX_FEATURES
Required/Optional: Optional
When specified, determines the maximum number of features the reader is allowed to read. Setting this keyword to zero means no limits are imposed on how many features can be read. This can be useful when it is uncertain how many features satisfy a given query, or when you want to read all the features in a given table unless there are many more than expected.
Value: positive integer
Default Value: 0
Example:
SDE30_MAX_FEATURES 0
Workbench Parameter: Max features to read
RASTER_PYRAMID_LEVEL_TO_READ
Required/Optional: Optional
When specified, determines the reduced resolution pyramid level to read raster data from. Zero denotes the base, full resolution pyramid. The raster must have had pyramids built when it was written to ArcSDE, and the pyramid level specified must be in the range of valid pyramid levels built.
Value: positive integer
Default Value: 0
Example:
SDERASTER_RASTER_PYRAMID_LEVEL_TO_READ 2
USE_UNIFIED_DATE_ATTRS
Required/Optional: Optional
Specifies whether we want to use unified date attributes, where the date and time are read into one attribute, or whether we want to use split date attributes, where two attributes are produced, one with only the date and another with both the date and time.
The value of this keyword should not be changed. It is automatically set to YES in new mapping files and workspaces. To maintain backwards compability, if this keyword is not present, the reader will behave as though the keyword is set to NO.
Value: YES | NO
Default Value: YES (in new mapping files and workspaces), NO otherwise
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