Connect CDC data transformation options enable you to:
-
Specify an expression whose result (true or false) determines whether a source row is sent to a target table. This is known as a “gate condition” or condition expression.
-
Specify an expression, associated with a target column, whose result when calculated at runtime will be the value sent to the target for that column. This is referred to as a “result expression.”
-
Use column values from SQL tables, the default being the source table.
-
Use any of multiple predefined system data transformation methods (including the methods supplied in earlier versions of the product, as well as the set of special variables:
-
Row manipulation type (INSERT, UPDATE, DELETE)
-
Sending DBMS type (DB2, Oracle, etc.)
-
Sending server name
-
Sending table name
-
Transaction identifier
-
Transaction sequence
-
Transaction timestamp
-
Transaction user name
-
Create user data transformation methods, that can be invoked from either a user gate condition or result column expression. Coding in the C language is not required and little or no configuration is required.
-
Handle NULL values in SQL columns.
The expression language supports the following grammatical constructs:
-
Arithmetic operations using: addition (+), subtraction (-), multiplication (*), division (/), and remainders (%).
-
Comparison operators allowing the construction of arbitrarily complex predicates: equal (EQ, ==), not equal (NE, !=, <>), greater than (>, GT), less than (<, LT), greater than or equal (>=, GE), and less than or equal (<=, LE).
-
Boolean operations AND and OR for joining predicates together: AND (&&), and OR (||).
-
A variety of literal values: integer (both base-10 decimal and base-16 hexidecimal), float, decimal, character, and the null literal.
-
Variable references that are either columns from an SQL table or temporary variables defined in the expression language used for holding intermediate results
-
Function calls to either Connect CDC system-defined methods or user-defined methods
-
A variety of datatypes: bit, integer, double, Boolean, date, time, timestamp, character, and decimal.
However, columns that have the following datatypes may not be used as Connect CDC expressions:
-
For MS SQL Server, ntext, binary, varbinary, IMAGE and TEXT are not allowed to be used in expressions.
-
For Sybase, binary and varbinary, IMAGE and TEXT are not allowed in expressions.
-
For Oracle, BLOB, CLOB, LONG, LONG RAW