B SNP_REV Tables Reference

This appendix provides a description of the Oracle Data Integrator SNP_REV tables. These tables are stored in a design-time repository and are used as staging tables for model metadata.

Customized Reverse-engineering processes load these tables before integrating their content into the repository tables describing the models.

See Chapter 3, "Reverse-Engineering Strategies" for more information.

B.1 SNP_REV_SUB_MODEL

SNP_REV_SUB_MODEL describes the sub-models hierarchy to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
SMOD_CODE	varchar(35)	Yes	Sub-model code
SMOD_NAME	varchar(400)	No	Sub-model name
SMOD_PARENT_CODE	varchar(35)	No	Parent sub-model code
IND_INTEGRATION	varchar(1)	No	Deprecated.
TABLE_NAME_PATTERN	varchar(35)	No	Automatic assignment mask used to distribute datastores in this sub-model
REV_APPY_PATTERN	varchar(1)	No	Datastores distribution rule: 0: No distribution 1: Automatic distribution of all datastores not already in a sub-model 2: Automatic distribution of all datastores
REV_PATTERN_ORDER	varchar(10)	No	Order into which the pattern is applied.

B.2 SNP_REV_TABLE

SNP_REV_TABLE describes the datastores (tables, views, etc.) to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
TABLE_NAME	varchar(128)	Yes	Datastore name
RES_NAME	varchar(400)	No	Resource Name: Physical table or file name.
TABLE_ALIAS	varchar(128)	No	Default datastore alias
TABLE_TYPE	varchar(2)	No	Datastore type: T: Table or File V: View Q: Queue AT: Table Alias SY: Synonym ST: System Table
TABLE_DESC	varchar(250)	No	Datastore description
IND_SHOW	varchar(1)	No	Datastore visibility: 0: Hidden 1: Displayed
R_COUNT	numeric(10)	No	Estimated row count
FILE_FORMAT	varchar(1)	No	Record format (applies only to files and JMS messages): D: Delimited file F: Fixed length file
FILE_SEP_FIELD	varchar(24)	No	Field separator (only applies to files and JMS messages)
FILE_ENC_FIELD	varchar(2)	No	Text delimiter (only applies to files and JMS messages)
FILE_SEP_ROW	varchar(24)	No	Record separator (only applies to files and JMS messages)
FILE_FIRST_ROW	numeric(10)	No	Number of header records to skip (only applies to files and JMS messages)
FILE_DEC_SEP	varchar(1)	No	Default decimal separator for numeric fields of the file (only applies to files and JMS messages)
SMOD_CODE	varchar(35)	No	Code of the sub-model containing this datastore. If null, the datastore is in the main model.
OLAP_TYPE	varchar(2)	No	OLAP Type: DH : Slowly Changing Dimension DI : Dimension FA : Fact Table
WS_NAME	varchar(400)	No	Data service name.
WS_ENTITY_NAME	varchar(400)	No	Data service entity name.
SUB_PARTITION_METH	varchar(1)	No	Partitioning method: H: Hash R: Range L: List
PARTITION_METH	varchar(1)	No	Sub-partitioning method: H: Hash R: Range L: List

B.3 SNP_REV_COL

SNP_REV_COL lists the datastore columns to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
TABLE_NAME	varchar(128)	Yes	Datastore name
COL_NAME	varchar(128)	Yes	Column name
COL_HEADING	varchar(128)	No	Short description of the column
COL_DESC	varchar(250)	No	Long description of the column
DT_DRIVER	varchar(35)	No	Data type of the column. This data type should match the data type code as defined in Oracle Data Integrator Topology for this technology
POS	numeric(10)	No	Position of the column (not used for fixed length columns of files)
LONGC	numeric(10)	No	Logical length of the column (precision for numeric)
SCALEC	numeric(10)	No	Logical scale of the column
FILE_POS	numeric(10)	No	Starting position of the column (used only for fixed length files)
BYTES	numeric(10)	No	Number of physical bytes to read from file (not used for table columns)
IND_WRITE	varchar(1)	No	1/0 to indicate whether the column is writable.
COL_MANDATORY	varchar(1)	No	1/0 to indicate whether the column is mandatory.
CHECK_FLOW	varchar(1)	No	1/0 to indicate whether to include the mandatory constraint check by default in the static control.
CHECK_STAT	varchar(1)	No	1/0 to indicate whether to include the mandatory constraint check by default in the static control.
COL_FORMAT	varchar(35)	No	Column format. Typically this field applies only to files and JMS messages to define the date format.
COL_DEC_SEP	varchar(1)	No	Decimal separator for the column (applies only to files and JMS messages)
REC_CODE_LIST	varchar(250)	No	Record code to filter multiple record files (applies only to files and JMS messages)
COL_NULL_IF_ERR	varchar(1)	No	Indicate behavior in case of error with this column: 0: Reject Error 1: Null if error (inactive trace) 2: Null if error (active trace)
DEF_VALUE	varchar(100)	No	Default value for this column.
SCD_COL_TYPE	varchar(2)	No	Slowly Changing Dimension type: CR: Current Record Flag ET: Ending Timestamp IR: Add Row on Change NK: Natural Key OC: Overwrite on Change SK: Surrogate Key ST: Starting Timestamp
IND_WS_SELECT	varchar(2)	No	0/1 to indicate whether this column is selectable using data services
IND_WS_UPDATE	varchar(2)	No	0/1 to indicate whether this column is updatable using data services
IND_WS_INSERT	varchar(2)	No	0/1 to indicate whether data can be inserted into this column using data services

B.4 SNP_REV_KEY

SNP_REV_KEY describes the datastore primary keys, alternate keys and indexes to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
TABLE_NAME	varchar(128)	Yes	Name of the datastore containing this constraint
KEY_NAME	varchar(128)	Yes	Key or index name
CONS_TYPE	varchar(2)	Yes	Key type: PK: Primary key AK: Alternate key I: Index
IND_ACTIVE	varchar(1)	No	0/1 to indicate whether this constraint is active.
CHECK_FLOW	varchar(1)	No	1/0 to indicate whether to include this constraint check by default in the flow control.
CHECK_STAT	varchar(1)	No	1/0 to indicate whether to include this constraint check by default in the static control.

B.5 SNP_REV_KEY_COL

SNP_REV_KEY_COL lists the columns participating to the primary keys, alternate keys and indexes to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
TABLE_NAME	varchar(128)	Yes	Name of the datastore containing this constraint
KEY_NAME	varchar(128)	Yes	Key or index name
COL_NAME	varchar(128)	Yes	Name of the column in the key or index
POS	numeric(10)	No	Position of the column in the key

B.6 SNP_REV_JOIN

SNP_REV_JOIN describes the datastore references (foreign keys) to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
FK_NAME	varchar(128)	Yes	Reference (foreign key) name
TABLE_NAME	varchar(128)	Yes	Name of the referencing table
FK_TYPE	varchar(1)	No	Reference type: D: Database reference U: User-defined reference C: Complex user reference
PK_CATALOG	varchar(128)	No	Catalog of the referenced table (if different from the catalog of the referencing table)
PK_SCHEMA	varchar(128)	No	Schema of the referenced table (if different from the schema of the referencing table)
PK_TABLE_NAME	varchar(128)	No	Name of the referenced table
IND_ACTIVE	varchar(1)	No	0/1 to indicate whether this constraint is active.
CHECK_FLOW	varchar(1)	No	1/0 to indicate whether to include this constraint check by default in the flow control.
CHECK_STAT	varchar(1)	No	1/0 to indicate whether to include this constraint check by default in the static control.
DEFER	varchar(1)	No	Deferred constraint: D : Deferrable I : Immediate N: Not Deferrable Not that this field is not used.
UPD_RULE	varchar(1)	No	On Update behavior: C: Cascade N: No Action R : Restrict S : Set Null
DEL_RULE	varchar(1)	No	On Delete behavior: C: Cascade N: No Action R : Restrict S : Set Null

B.7 SNP_REV_JOIN_COL

SNP_REV_JOIN_COL lists the matching columns participating to the references (foreign keys) to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
FK_NAME	varchar(128)	Yes	Reference (foreign key) name
FK_COL_NAME	varchar(128)	Yes	Name of the column in the referencing table
FK_TABLE_NAME	varchar(128)	No	Name of the referencing table
PK_COL_NAME	varchar(128)	Yes	Name of the column in the referenced table
PK_TABLE_NAME	varchar(128)	No	Name of the referenced table
POS	numeric(10)	No	Position of the column in the reference

B.8 SNP_REV_COND

SNP_REV_COND describes the datastore condition and filters to reverse-engineer.

Column	Type	Mandatory	Description
I_MOD	numeric(10)	Yes	Model ID
TABLE_NAME	varchar(128)	Yes	Name of the datastore containing this constraint
COND_NAME	varchar(128)	Yes	Condition or check constraint name
COND_TYPE	varchar(1)	Yes	Condition type: C: Oracle Data Integrator condition D: Database condition F: Filter
COND_SQL	varchar(250)	No	SQL expression for applying this condition or filter
COND_MESS	varchar(250)	No	Error message for this condition
IND_ACTIVE	varchar(1)	No	0/1 to indicate whether this constraint is active.
CHECK_FLOW	varchar(1)	No	1/0 to indicate whether to include this constraint check by default in the flow control.
CHECK_STAT	varchar(1)	No	1/0 to indicate whether to include this constraint check by default in the static control.