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.