Mapping an expression to a target column

An expression defines the value placed in a column for each row inserted or updated in a target table. Expressions are stored and evaluated on target columns.

About this task

For example, you can create an expression that:
  • Converts integer data on the source column to character data using the column function %TOCHAR. An expression such as %TOCHAR(OrderID, 6) would convert integer data to a string of six characters in the source column OrderID so that it is compatible with the target column that you want to populate.
  • Calls a stored procedure that you have configured in a user exit program. You can specify an expression that contains a valid call to the %STPROC column manipulation function. If you are calling a stored procedure that is not owned by the CDC Replication user, you must provide the name in the form <schema>.<stored procedure name>.

When you are mapping expressions to target columns, keep in mind that many databases have column name length limitations, which can affect how some expressions, user exits, and derived columns are handled. Column name length limitations can cause CDC Replication to describe a column alias to the target when the source column name length exceeds the column name length limit on the target. The restriction is 30 characters for most CDC Replication compatible databases (IBM® DB2® for Linux, UNIX, and Windows and Oracle). Microsoft SQL Server has a limit of 128 characters.

Procedure

  1. Click Configuration > Subscriptions.
  2. Select the subscription.
  3. Click the Table Mappings view and select the table mapping from the Source Table column.
  4. Right-click and select Open Details....
  5. Click the Column Mappings tab.
  6. Expand the Expressions list and drag and drop New Expression to the target column you want to map.
  7. Build an expression.
  8. Click OK.

    You should see the expression mapped to the target column on the Column Mappings tab.

  9. Click Save.

Results

When you start replication, CDC Replication evaluates the expression on the target column.