Sending and Receiving Table Properties, Mapping Tab - Connect_CDC - connect_cdc_mimix_share - 6.x

Connect CDC Getting Started Guide

Product type
Software
Portfolio
Integrate
Product family
Connect
Product
Connect > Connect CDC (MIMIX Share)
Version
6.x
Language
English
Product name
Connect CDC
Title
Connect CDC Getting Started Guide
Copyright
2024
First publish date
2003
Last updated
2024-10-15
Published on
2024-10-15T20:38:41.117981

To access this page, right-click the source table in the Tables branch, then select Properties to display the Sending and Receiving Table Properties, Mapping Dialog.

Use this page to map columns in a sending table to columns in one or more target (receiving) tables.

Note: The xml datatype mapping is allowed only for Microsoft SQL Server. You can only map the xml datatype to xml datatype. This means that the compatible columns for the xml datatype will only show the xml datatype columns.

When a receiving table is mapped to a sending table, an [M] appears next to the server and table names in the Receiving server and Receiving table lists. Once a sending table is mapped to a receiving table, the only way to unmap it is to drop the receiving table from the list of receiving tables in Connect CDC Director tree. See Unmap a receiving table.

DKey column

This column identifies the Distribution Key. By default, DKey is marked for the Primary key. You can deselect the default and add as many checkmarks as necessary. Refer to the System Reference Guide, Chapter 3, Using Replication Schema Language, section “Distribution key rules”.

Data specific decoding custom CCSID

When IBMi Change Selector reads the Journal and tries to decode column’s data and writes it to the queues, the data decoding gives an incorrect output and conversion.

If the field content has a different encoding from any given metadata, then we have to rely on an external value which is given by the RP_CCCSID definitions. It is a parametric table and will be used at runtime.

Use this command for the coding:

INSERT INTO

Example of Conditional SQL
-- Conditional------------              
                    INSERT INTO MXSHRDEMO.RP_CCCSID (rp_schema, rp_table, rp_basecolno, rp_altccsid, rp_condcolno, rp_condvalue) VALUES('MXSHR7857D', 'TB1', 2, 277, 3, 'NOR');                  
                    INSERT INTO MXSHRDEMO.RP_CCCSID (rp_schema, rp_table, rp_basecolno, rp_altccsid, rp_condcolno, rp_condvalue) VALUES('MXSHR7857D', 'TB1', 2, 037, 3, 'USA');                  
                    INSERT INTO MXSHRDEMO.RP_CCCSID (rp_schema, rp_table, rp_basecolno, rp_altccsid, rp_condcolno, rp_condvalue) VALUES('MXSHR7857D', 'TB1', 2, 278, 3, 'FIN');
Example of Constant SQL
INSERT INTO MXSHRDEMO.RP_CCCSID (rp_schema, rp_table, rp_basecolno, rp_altccsid, rp_condcolno, rp_condvalue) VALUES('MXSHR7857D', 'TB2', 2, 290, -1, '');
Value explained SQL
VALUES('<source schema>', '<source table>', <column to decode>, <source content CCSID>, <conditional column>, '<conditional value>');

If you want to convert all table columns, set RP_BASECOLNO to zero.

Warning: Be careful when using wildcard for constant or conditional. Make sure you have no other definitions because they will be ignored.

When inserting data in RP_CCCSID , values should be UPPER case except if IBM system is set to be case sensitive.

For example:
INSERT INTO MXSHR7874N.RP_CCCSID (rp_schema, rp_table, rp_basecolno, rp_altccsid, rp_condcolno, rp_condvalue) VALUES('SCHEMANAM', 'TB3', 2, 277, 4, 'NOR');                  
And
INSERT INTO MXSHR7874N.RP_CCCSID (rp_schema, rp_table, rp_basecolno, rp_altccsid, rp_condcolno, rp_condvalue) VALUES('schemanam', ‘tb3’, 2, 277, 4, 'NOR');                  
These INSERT statements are not the same. The second INSERT statement only works if:
  • system is case sensitive AND
  • name is lower case

Parameter resolution order:

The Change Selector loops through the columns and performs a lookup in RP_CCSID table. The order of priority is:
  1. All columns (Wildcard -rp_basecolno = 0)
    1. Constant
    2. Conditional
  2. Specific column
    1. Constant
    2. Conditional

Any other entries will be ignored once the first valid entry is found.

Note:
  • No controls are done for the RP_CCCSID table content so make sure your approach is consistent to avoid unexpected results.
  • For conditional usage:
    • Column “rp_condcolno” the column that is referred as condition holder, will not be converted.
    • The content has to be in native/explicit metadata CCSID. (database, table, column).
    • Consequently, the encoding can not vary and must be constant.