Create a table mapping - connect_cdc_mimix_share - Latest

Usage

Creates a table mapping, the association of a source table to a target table. Explicitly maps source columns with target columns between two tables at connecting servers. CREATE TABLE MAPPING is used for vertical partitioning of the tables when

• Table or column names on the source and target servers do not match.

• Data enhancement is required.

If the table or column names on the source and target servers do match (ignoring case), you can apply a default mapping to the source and target columns, as described in Create a method.

CREATE TABLE MAPPING
   TARGET shared-table-name AT [NODE | SERVER] target-server-name, ...
   FROM SOURCE shared-table-name AT [NODE | SERVER] source-server-name
   [JOURNAL]
   [DISTRIBUTION distname, ...]
   [GATE_CONDITION ‘text of expression-embed single quotes by doubling’]
   [DEFAULT]
      { target-column-name [DKEY] :
            { source-column-name |
         method-name ( {source-column-name |
                           'literal-value' |
      system-variable-name }, … ) |
         EXPRESSION 'text of expression-embed single quotes by doubling'
         [ [JOINEDTAB tablename [CORRELATION 'corname'] … ]
      }
   }, …
;

Distribution key rules

For each mapping to a target table, at least one target column must be specified as a distribution key, that is, a column or set of columns that uniquely identifies each row in the target table. These distribution key columns act as a “primary key” in the data modeling sense, and they ought to be defined as primary or unique keys in the DBMS catalog. However, Connect CDC relies on the DBMS constraint checking and does not enforce the uniqueness rule beyond issuing a warning.   

A distribution key column may be the target of multiple source distribution key columns.

The Connect CDC Director initially makes target table primary keys the default distribution keys, which you can change.

• If a table has no primary key but at least one unique index, the first unique index is defined as the distribution key.

• If a table has no primary key or unique index, the Connect CDC Director selects the target columns corresponding to the source table keys, if possible.

Violation of any of the following rules produces a model validation error or warning:

• Every table mapping (except Journal Mappings, see above) in an EDMM must have a defined distribution key, which may consist of one or more columns.

• The sum of the lengths of all distribution key columns in a table cannot exceed 256. This restriction does not apply to Oracle sources.

• Each distribution key column must be mapped. The input parameters to a method that produces a target distribution key column may be key columns, constants, system variables, or non-key columns.

• A column used as a distribution key may not be:

• long column, that is, one that contains data-only large objects

• "medium-sized" column, one whose datatype is categorized Character, but whose length exceeds a certain value (254 for DB2, 255 for MS SQL Server) but whose length exceeds a certain value (254 for DB2, 255 for MS SQL Server). See Supported datatypes per DBMS for more information on datatype categories.

• bit type

• binary type

• Approximate numeric (REAL, FLOAT, DOUBLE) types, but this is only a WARNING.

• In bidirectional replication, the distribution keys in each direction must be mapped in a “reversible” fashion. For example, if the mapping from A to B concatenates two fields in A to form a distribution key in B, the mapping from B to A must separate the field in B into the two fields in A.

In protected replication involving Oracle or DB2, at least one distribution key column must be defined as NOT NULL at each server. If only other DBMSs are involved, all key columns may be nullable.

System variable names

See the table below for additional information on the following system variable names:

• ROW_MANIPULATION_TYPE

• TRANSACTION_ID

• TRANSACTION_ROW_SEQUENCE

• SENDING_NODE_NAME or SENDING_SERVER_NAME

• SENDING_DBMS_TYPE

• SENDING_TABLE_NAME

• TRANSACTION_TIMESTAMP

• TRANSACTION_USERNAME

Note the following:

• Table names are case-sensitive.

• Method names require double-quotation marks if they contain a space between two words.

• The JOURNAL parameter creates a transaction journal.

Examples

-- mappings.rsl
CREATE TABLE MAPPING
   TARGET DVCOLE.EMP AT AXQ4
   FROM SOURCE astest.dvams.claims90 AT I10HP4
   EMPID [DKEY]: claim_no, EMPNAME: claimee, STATE: location
   TRAN_ID: "Copy input" (TRANSACTION_ID),
   TRAN_SEQ: "Copy input" (TRANSACTION_ROW_SEQUENCE)
;
 
CREATE TABLE MAPPING
   TARGET astest.dvams.claims90 AT I10HP4
   FROM SOURCE DVCOLE.EMP AT AXQ4
   claim_no [DKEY]: EMPID, claimee: EMPNAME, location: STATE
;
 
CREATE TABLE MAPPING
   TARGET SAIXD1.qadba.emp AT SAIXD1
   FROM SOURCE DVCOLE.EMP AT AXQ4
   empid [DKEY]: EMPID, empname: EMPNAME, state: STATE
;
 
CREATE TABLE MAPPING
   TARGET SAIXD1.qadba.emp AT SAIXD1
   FROM SOURCE DVCOLE.EMP AT AXQ4
   empid [DKEY]: EMPID, empname: EMPNAME, sal: Foo(EMPID, EMPNAME)
;
 
CREATE TABLE MAPPING
   TARGET SAIXD1.qadba.emp AT SAIXD1
   FROM SOURCE astest.dvams.claims90 AT I10HP4
   empid [DKEY]: claim_no, empname: claimee, state: location
;
 
CREATE TABLE MAPPING
   TARGET astest:dvams.claims90 AT I10HP4
   FROM SOURCE SAIXD1.qadba.emp AT SAIXD1
   claim_no [DKEY]: empid, claimee: empname, location: state
;

Journal mapping

Specify the JOURNAL option if you are creating a table mapping from a source table to a target table for a transaction journal. A transaction journal contains one row for each insert, update, or delete at the source table.

You must make sure you follow the target set-up requirements for transaction journals, which include the creation of special target distribution keys and additional target columns for system variables.

The CREATE TABLE MAPPING statement will need to specify a data enhancement method and system variable for one or more column mappings.

Prepared statement

Specify the prepared_stmt option if a a table mapping uses a prepared statement. If the mapping does not use prepared statement then this attribute will not be added.

Batch journal inserts

Specify the BATCH_JOURNAL_INSERTS option if you select the Batch Journal Insert option in the Table Mapping page.

Suppress delete

Specify the SUPPRESS_DELETE option if you select the Keep the target row if UPDATE causes a change in scope option in the Gate Condition page.

CLRPFM

Specify the replicate_clrpfm option with the appropriate value.