CDC DB Listener activity

The CDC-DB Listener activity listens to the operations performed on database tables. When CDC is enabled for a table the data change events for the respective table are logged. The Debezium engine collects these data change event logs and returns the details of the data change.

By default, the plug-in initializes Debezium to capture change data events for Insert (c), Update (u), Delete (d), and Snapshot operations.

However, you can pass the appropriate Operation Options value so that the data changes specific to these activities performed on a table are captured.

Note: The CDC-DB listener activity does not support concurrency, as the events are read sequentially.

Note: The Flowlimit is not supported in the CDC-DB listener activity due to the behavior of the Debezium engine.

CDC Database Configuration Preferences

You can specify the Database preferences by setting the default values for the Operation Options and Output Formats.

The Operation Options and Output Formats fields are also available on the CDC-DB Listener's Advanced tab. The default values listed in the CDC Database Configuration Preferences window are preferred until you add the CDC-DB activity to the Palette.

Procedure

From the menu, click Window > Preferences to open the Preferences Wizard.
In the Preferences wizard, click CDC Plugin and then click CDC Database Configuration Preferences to open the CDC Database Configuration Preferences wizard.
By default the Offset Flush Interval is set to 100 milliseconds. Choose the desired Operation Options and Output Formats, click Apply to set these values as default.

Note: Encrypt Database is supported only for MSSQL database server in configuration preferences setting.

Now, click Apply and Close.

Note: In the CDC Database Configuration Preferences Wizard, the Database Encrypt checkbox can trust server-signed certificates or use a trust store for the server's signer certificates. In the latter case, the path to trust store and the trust store password should be provided as configuration properties ("database.ssl.truststore", and "database.ssl.truststore.password") using the com.tibco.bw.palette.cdc.additionalDbProperties module property.

General

The General tab contains the following fields.

Property	Module Property?	Visual Diff?	Description
Name	Yes	Yes	Name of the palette activity
JDBC Connection	Yes	Yes	The name to be displayed as the label for the JDBC connection resource.
Table Name	Yes	Yes	The table name displayed as the label
Output Format	No	Yes	The following are the output formats JSON XML Avro Note: For MSSQL and Oracle, for Output Format JSON and XML, if data is "null" as literal string, then it is treated as database null instead of "null" as literal string.

Description

Provide a short description for the activity.

Advanced

The Advanced tab contains the following fields.

Property Module Property? Visual Diff? Description

Sequence Key No Yes This field contains an XPath expression that specifies which processes should run in sequence. Process instances with sequencing keys evaluating to the same value, are executed sequentially in the sequence the process instance was created.

Custom Job Id No Yes This field contains an XPath expression that specifies a custom ID for the process instance.

Operation Options

Yes

Property	Module Property?	Visual Diff?	Description
Sequence Key	No	Yes	This field contains an XPath expression that specifies which processes should run in sequence. Process instances with sequencing keys evaluating to the same value, are executed sequentially in the sequence the process instance was created.
Custom Job Id	No	Yes	This field contains an XPath expression that specifies a custom ID for the process instance.
Operation Options	Yes	Yes	Facilitate the selection of the appropriate operation. Insert: When the Insert checkbox is selected and insert operation is performed on DB, we receive the event with Opcode value `c.` Update: When the Update checkbox is selected and update operation is performed on DB, we receive the event with Opcode value `u.` Delete: When the Delete checkbox is selected and delete operation is performed on DB, we receive the event with Opcode value `d`. Snapshot: Scans database tables or objects and creates reference point to capture change data information and we receive the event with Opcode value `r`. Note: This Snapshot option performs read operation when app is running for the first time. Process Nullable Columns For Avro: When Process Nullable Columns For Avro checkbox is selected for Output Format Avro, it handles the Null values or datatype mismatch. Note: The Process Nullable Columns For Avro does not support Visual Diff.

Facilitate the selection of the appropriate operation.

Insert: When the Insert checkbox is selected and insert operation is performed on DB, we receive the event with Opcode value c.
Update: When the Update checkbox is selected and update operation is performed on DB, we receive the event with Opcode value u.
Delete: When the Delete checkbox is selected and delete operation is performed on DB, we receive the event with Opcode value d.
Snapshot: Scans database tables or objects and creates reference point to capture change data information and we receive the event with Opcode value r.

Note: This Snapshot option performs read operation when app is running for the first time.

Process Nullable Columns For Avro: When Process Nullable Columns For Avro checkbox is selected for Output Format Avro, it handles the Null values or datatype mismatch.

Note: The Process Nullable Columns For Avro does not support Visual Diff.

Module Property

Any additional database or Debezium property can be specified using the com.tibco.bw.palette.cdc.additionalDbProperties module property.

Note: If the include column list is specified in the module property and the Primary key column is not included in the list, the Primary key column is not shown in the activity output.

Note: When the provide.transaction.metadata property is set to true, unable to get the transaction metadata in response.

Note: If multiple additionalDbProperties are provided, split them by semicolon. For example: prop_name1=prop_value1;prop_name2=prop_value2.

Note: For Oracle in case of update we need to run below query, so all field values are seen in source and target data:
ALTER TABLE <TABLE NAME> ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

Conversations

You can use the Conversations tab to initiate or join a conversation.

Conversations receive a message after the creation of the process instance, which is initiated or joined by an activity. This activity can be used to initiate a conversation. Click the Add New Conversation icon to initiate a conversation. For more information about conversations, see TIBCO ActiveMatrix BusinessWorks™ Application Design.

Output

The following is the output for the activity.

Output Item Data Type Description

Global Scope String The Global scope checkbox makes output variable of the activity from current scope to global level which is at the process level.

item?

String

Header: Based on the output formats, the Output item differs

dbName?: The database name with which the activity is performed
schema?: The schema with which the activity is performed
table?: The table on which operations are performed
opcode?: The opcode value that is set to perform a specific operation
timestamp?: The time required to perform the specific activity
date?: The date when an event is performed
time?: The time at which an event occurs
messageID? It is an Unique ID for an event

Body: Data of records that undergo change based on the output formats that are chosen.

For JSON:

data?: Depending on the insert, update, and delete operations performed, we obtain respective source and target values.
- For Insert operation the source is null, target consists of values which are inserted
- For Update operation the source consists of old values and target consists of new updated values
- For Delete operation the source consists of deleted values and target is null
For XML:

Source Column and Target Column: Depending on the insert, update, and delete operations performed, we obtain respective Source Column and Target Column values.
- For Insert operation the Source Column is null, whereas the Target Column consists of values which are inserted
- For Update operation the Source Column consists of old values and the Target Column consists of new updated values
- For Delete operation the Source Column consists of deleted values and the Target Column is null
For Avro:

sourceData? and targetData?: Depending on the insert, update, and delete operations performed, we obtain respective sourceData? and targetData? values
- For Insert operation there is no sourceData? obtained, whereas the targetData? consists of newly created values
- For Update operation the sourceData? consists of old values and the targetData? consists of new updated values
- For Delete operation the sourceData? consists of deleted values and there is no targetData? obtained

Fault

The Fault tab lists exceptions that are generated by this activity.

Error Schema Element	Data Type	Description
msg	string	The error message returned by the plug-in.
msgCode	string	The error code returned by the plug-in

CDCPluginException: The exception is generated when the plug-in cannot send the request or parse the response.
CDCDBException: The exception is generated when any database related error occurs.

Did you find this helpful?

Yes No