Create a table mapping - connect_cdc_mimix_share - 5.8

Connect CDC System Reference Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
5.8
Language
English
Product name
Connect CDC
Title
Connect CDC System Reference Guide
Copyright
2024
First publish date
2003
ft:lastEdition
2024-02-12
ft:lastPublication
2024-02-12T15:11:59.486869

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.

Note: A CREATE SHARED TABLE statement (see Create a shared table) must exist for each referenced table.
Syntax
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.

Name

Definition

ROW_MANIPULATION_TYPE

Replication: I (insert), D (delete), or U (update); the type of operation that caused the capture of the row containing this source column

Copy: NULL

SENDING_DBMS_TYPE

DBMS type value from Server Properties, Server tab

SENDING_NODE_NAME or SENDING_SERVER_NAME

Name value from Server Properties, Server tab. This is the column name actually used in the table and omnient.ini

SENDING_TABLE_NAME

Concatenation of sending table owner and sending table name

TRANSACTION_ID

  • Replication: 16-digit, unique identifier of the transaction that involved the row containing this source column

  • Copy: NULL

TRANSACTION_ROW_SEQUENCE

  • Replication: Integer value for the position (of the row that contains this source column) in the chronological sequence of individual row manipulations generated by this transaction; for example, 4, if this is the fourth row manipulated during the transaction

  • Copy: NULL

TRANSACTION_TIMESTAMP

  • Replication: Time (from the DBMS, in GMT, including fractions of a second) of the last row manipulation in the user transaction that involved the row containing this source column

  • Copy: NULL

TRANSACTION_USERNAME

  • Replication: The ID of the user who created this transaction.

  • Copy: NULL

PROGRAM_NAME

The name of the program that executed the update operation.

JOB_NAME

The name of the job under which the program was executed.

JOB_USER

The name of the user associated with the operation.

JOB _NUMBER

The job number associated with the operation.

ROW_TIMESTAMP

The date & time that the update operation occurred. This is the 18 digit DB2 timestamp, YYYYMMDDHHMMSSffff

SEND _FILE _MEMBER

The Member name of the file that was updated.

Examples

File mappings.rsl creates several explicit table mappings for tables astest.dvams.claims90, dvcole.emp, and SAIXD1.qadba.emp.
-- 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.