Adding and mapping derived columns to target columns

Derived columns let you move the processing of an expression from the target instance to the source instance.

For example, you may have already defined an expression that concatenates the values of two source columns, FIRSTNAME and LASTNAME, and mapped this expression to a target column named called FULLNAME. When you start replication, CDC Replication evaluates the expression on the target instance and stores the results in the FULLNAME target column.

However, it may become necessary in your environment to move the processing of this expression to the source instance. Using this same example, you can build a derived column on the source table named FULLNAME and define an expression that concatenates the values of the two source columns FIRSTNAME and LASTNAME. You can then map the derived column named FULLNAME to the target column named FULLNAME. When you start replication, CDC Replication evaluates the expression on the source instance and sends the results to the target column.

You can also create a derived column to:

  • Extract characters from string data by using functions such as %SUBSTRING, and then store the result in a derived column. For example, you can extract a person's initial from a column named FIRSTNAME, by using the expression SUBSTRING(FIRSTNAME,1,1).
  • Call 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.
  • Retrieve information from a lookup table using %GETCOL. You can create a derived column on the source table using %GETCOL so that you can retrieve data from a lookup table. You can then map the source table using one-to-many consolidation.

In each of these scenarios, you can then map the derived column to the appropriate target column. CDC Replication will evaluate the expression on the source table and send the results to the target column.

Important: If you create a source derived column, you will not be able to use column filtering. All critical and non-critical columns will be replicated.

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.

Since the derived expression is evaluated each time a change is replicated to the target table, the complexity of the expression can affect overall performance.