When reading from SDE, it is not necessary that the source tables be defined. This is also true when writing to existing tables. However, if the SDE writer is going to create the tables, then definitions must be supplied. This is true even when the table already exists but the SDE_DROP_TABLE parameter has been set to Yes. Within FME mapping files, SDE tables are defined using the <WriterKeyword>_DEF statement, whereas within Workbench they are defined by adding destination feature types.
When creating new tables, it is important to understand that the decision for which type of table to create is not based on the DEF line itself but rather the first feature written to the table. This means that if the first feature contains no geometry, then a business table (no spatial columns) will be created; if the first feature contains vector geometry, then a feature class (business table + layer) will be created; and if the first feature contains raster geometry, either a raster map or raster catalog will be created. The purpose of the DEF line is to specify what the table looks like.
If the table already exists in the database (and the user is not deleting it using the SDE_DROP_TABLE parameter) then its schema will not be altered. The only exception to this is when the first feature, within a translation, written to a pre-existing business table contains vector geometry and the directive ADD_LAYERS_TO_EXISTING_TABLES is set to Yes or is not specified. In this case a layer will be added to the business table. The addition of the layer turns the business table into a feature class. If the SDERASTERMAP writer is used to write to an existing raster catalog, or the SDERASTERCATALOG writer is used to write to an existing rastermap, the translation will fail.
To define a simple table with no spatial or raster column using FME, the definition is in this form:
<WriterKeyword>_DEF <tableName> \
[<columnName> <columndef>]*
A more general format of a table definition – in which a spatial column, along with attribute indices can be defined – is given here.
<WriterKeyword>_DEF <tableName> \
[<columnName> <columndef>] * \
[ [SDE_INDEX <indexName> \
SDE_INDEX_CONFIG <configKeyword> \
SDE_COLUMN_NAME <columnName>[,<columnName>]* \
SDE_UNIQUE <TRUE|FALSE|YES|NO> \
SDE_SORT_ORDER ASCEND|DESCEND
] * \
[SDE_UPDATE_FIELDS <columnName> [,<columnName>]* \
[SDE_STORAGE_TYPE <SDE_BINARY|WKB|SQL|NORMALIZED>]
SDE_LAYER <spatialColumnName> \
[SDE_COORD_SYS_ID <coordSysID#>] \
[SDE_COORD_SYS_DESCRIPTION <description>] \
[SDE_PRECISION <32 | 64>] \
SDE_GRID{0} <grid0size> \
[SDE_GRID{1} <grid1size>] \
[SDE_GRID{2} <grid2size>] \
SDE_DIMENSION < 2 | 3 > \
[SDE_CONFIG_KEYWORD <configKeyword>] \
SDE_MEASURED < Yes | No > \
SDE_ANNOTATED < Yes | No > \
SDE_AREA < Yes | No > \
SDE_LINE < Yes | No > \
SDE_POINT < Yes | No > \
SDE_SIMPLE_LINE < Yes | No > \
SDE_NIL < Yes | No > \
SDE_MULTIPART < Yes | No > \
SDE_CAD < Yes | No > \
SDE_DROP_TABLE < Yes | No > \
SDE_TRUNCATE_TABLE < Yes | No > \
SDE_XORIGIN <minimum_x> \
SDE_YORIGIN <minimum_y> \
SDE_SCALE <scale> \
SDE_ZORIGIN <minimum_y> \
SDE_ZSCALE <scale> \
SDE_MEASURED_ORIGIN <minimum_y> \
SDE_MEASURED_SCALE <scale> \
SDE_TOLERANCE <tolerance> \
SDE_MEASURED_TOLERANCE <tolerance> \
SDE_ZTOLERANCE <tolerance> \
[SDE_DESCRIPTION <layer description>] \
[SDE_MINIMUM_FID <minimumFidNumber>]
]
Another form of the general format of a table definition includes the definition of a raster column. An example is given here for a raster map.
Note: Note: During creation of a new rastermap table, the writer will create a reserved ArcSDE column called NAME. This additional column will be created and populated with the value ESRI_SDERASTERDATASET. For raster catalogs, this column is optional, and populated by default with the value of the fme_basename attribute for each row in the table.
<WriterKeyword>_DEF <tableName> \ [<columnName> <columndef>] * \ [SDE_COORD_SYS_ID <coordSysID#>] \ [SDE_COORD_SYS_DESCRIPTION <description>] \ [SDE_CONFIG_KEYWORD <configKeyword>] \ SDE_RASTER <rasterColumnName> \ [SDE_COMPRESS_TYPE < NONE | LZ77 | JPEG | JPEG2000>] \ [SDE_PYRAMID_INTERPOLATION< NONE | NEAREST_NEIGHBOR | BILINEAR | BICUBIC >] \ [SDE_PYRAMID_LEVEL_TYPE < NONE | AUTO | CUSTOM >] \ [SDE_PYRAMID_MAX_LEVEL <maxLevel>] \ [SDE_RASTER_STATS_TYPE < NONE | AUTO >] \ [SDE_DESCRIPTION <raster description>] \ [SDE_DROP_TABLE < Yes | No > \] \ [SDE_TRUNCATE_TABLE < Yes | No > \] \ [SDE_COMPRESS_COLORMAP < Yes | No > \] \ [SDE_RASTER_MOSAIC_MODE <NONE | MERGE | DELETE>]
The table definition for a raster catalog includes additional parameters for creating the spatial (footprint) column.
Note: If a table definition is given for a table that does not yet exist and the only column defined in the definition is the spatial or raster column, then the writer will create an object ID column maintained by ArcSDE called OBJECTID. This additional column will be created because ArcSDE does not allow tables to contain only a spatial/raster column.
<WriterKeyword>_DEF <tableName> \ [<columnName> <columndef>] * \ [SDE_COORD_SYS_ID <coordSysID#>] \ [SDE_COORD_SYS_DESCRIPTION <description>] \ [SDE_CONFIG_KEYWORD <configKeyword>] \ SDE_RASTER <rasterColumnName> \ [SDE_COMPRESS_TYPE < NONE | LZ77 | JPEG | JPEG2000>] \ [SDE_PYRAMID_INTERPOLATION< NONE | NEAREST_NEIGHBOR | BILINEAR | BICUBIC >] \ [SDE_PYRAMID_LEVEL_TYPE < NONE | AUTO | CUSTOM >] \ [SDE_PYRAMID_MAX_LEVEL <maxLevel>] \ [SDE_RASTER_STATS_TYPE < NONE | AUTO >] \ [SDE_DESCRIPTION <raster description>] \ [SDE_MINIMUM_FID <minimumFidNumber>] \ [SDE_DROP_TABLE < Yes | No > \] \ [SDE_TRUNCATE_TABLE < Yes | No > \] \ [SDE_XORIGIN <minimum_x>] \ [SDE_YORIGIN <minimum_y>] \ [SDE_SCALE <scale>]