Contents
The TIBCO StreamBase® Database Change Data Capture Input adapter allows a StreamBase application to monitor changes to an Oracle or SQL Server database (2005,2008,2010,2012,2014,2016).
The adapter monitors the database's transaction log to detect when records are inserted, updated, or deleted in a database
table. Each captured change results in a tuple emitted on the adapter's Capture output port. The tuple contains a set of adapter-supplied metadata indicating the type of operation (insert, update, or delete),
the transaction log sequence number, and a bit mask indicating which table fields changed, along with the changed fields themselves.
The application author expresses interest in a particular table column by adding a corresponding field in the adapter's Edit
Schema tab.
The adapter is configured through a collection of properties set in the adapter's Properties view within StreamBase Studio. Properties specify, among other things, the database host name or IP address and TCP port number, the database username and password, and the table for which activity is being captured.
This section describes the properties you can set for this adapter, using the Adapter Properties tab of the Properties view in StreamBase Studio.
Name: Use this field to specify or change the component's name, which must be unique in the application. The name must contain only alphabetic characters, numbers, and underscores, and no hyphens or other special characters. The first character must be alphabetic or an underscore.
Adapter: A read-only field that shows the formal name of the adapter.
Class: A field that shows the fully qualified class name that implements the functionality of this adapter. Use this class name when loading the adapter in StreamSQL programs with the APPLY JAVA statement. You can right-click this field and select Copy from the context menu to place the full class name in the system clipboard.
Start with application: If this field is set to Yes or to a module parameter that evaluates to true, an instance of this adapter starts as part of the containing StreamBase Server. If this field is set to No or to a module parameter that evaluates to false, the adapter is loaded with the server, but does not start until you send an sbadmin resume command, or until you start the component with StreamBase Manager. With this option set to No or false, the adapter does not start even if the application as a whole is suspended and later resumed. The recommended setting is selected by default.
Enable Error Output Port: Select this check box to add an Error Port to this component. In the EventFlow canvas, the Error Port shows as a red output port, always the last port for the component. See Using Error Ports and Error Streams to learn about Error Ports.
Description: Optionally enter text to briefly describe the component's purpose and function. In the EventFlow canvas, you can see the description by pressing Ctrl while the component's tooltip is displayed.
This section describes the properties on the Adapter Properties tab in the Properties view for the Change Data Capture adapter.
| Property | Description |
|---|---|
| Database Type | The type of database: Oracle or SQL Server. |
| Host | The host name or IP address of the machine on which the database is running. |
| Port | The TCP port number on which the database is listening. |
| Database Name | The name of the database. |
| JDBC URL |
Optionally the full JDBC connection URL which overrides the above connection information. Example MSSQL: jdbc:sqlserver://{host}:{port};databaseName={databaseName} Example Oracle: jdbc:oracle:thin:{host}:{port}:{databaseName} |
| Username | The username to use in connecting to the database. |
| Password | The password to use in connecting to the database. |
| Table Name | The name of the database table for which changes are being captured. For Oracle, this is the name of the capture table. |
| Log Level | Controls the level of verbosity the adapter uses to send notifications to the console. This setting can be higher than the containing application's log level. If set lower, the system log level is used. Available values, in increasing order of verbosity, are: OFF, ERROR, WARN, INFO, DEBUG, TRACE, and ALL. |
Use the Edit Schema tab to express interest in one or more columns of the target table. Each field present in this tab must have a column with an identical name and compatible type in the target table. Fields present in the Edit Schema tab, along with several additional metadata fields supplied by the adapter, appear in the schema of the adapter's Capture output port.
For general instructions on using the Edit Schema tab, see Defining Input Streams.
Use the Concurrency tab to specify parallel regions for this instance of this component, or multiplicity options, or both. The Concurrency tab settings are described in Concurrency Options, and dispatch styles are described in Dispatch Styles.
Caution
Concurrency settings are not suitable for every application, and using these settings requires a thorough analysis of your application. For details, see Execution Order and Concurrency, which includes important guidelines for using the concurrency options.
This section demonstrates how to use the Change Data Capture input adapter within a StreamBase application and describes the use of the adapter's ports. As shown in the EventFlow diagram below (from this adapter's sample application), the adapter uses two output ports to communicate with the surrounding application. As with other StreamBase adapters and operators, you can optionally enable an Error Output Port, as described in Using Error Ports and Error Streams.
Note
In this adapter's sample application, the adapter's output ports are connected directly to externally-visible output streams. However, in more complex applications these ports will typically be connected to internal StreamBase operators.
 |
The Change Data Capture input adapter's ports are used as follows:
-
Status (output): The adapter emits tuples from this port when significant events occur, such as when the adapter connects to the database, when CDC is enabled, or when the adapter fails to populate a tuple field in response to a captured change. The schema for this port has the following fields:
-
Type, string: returns one of the following values to convey the type of event:
-
Capture
-
Connection
-
Service
-
Suspend/Resume
-
UserInput
-
-
Action, string: returns one of the following values to convey the action that resulted in the event:
-
Already Enabled — CDC was already enabled for the database or table when the adapter starts
-
Already Running — For SQL Server, the SQLSERVERAGENT service is already running when the adapter starts
-
Connected — The adapter connected to the database
-
Enabled — CDC has been enabled for the database or table
-
Failed — An attempt failed to connect to the database, enable CDC, start the SQLSERVERAGENT service (SQL Server only), or populate a tuple field from a captured change.
-
Resumed — An adapter instance was resumed
-
Suspending — An adapter instance is being suspended
-
Started — The SQLSERVERAGENT service (for SQL Server)
-
Timeout — A timeout occurred waiting for the SQLSERVERAGENT service to start (SQL Server only)
-
-
Object, string: returns an event type-specific value, such as the database URL being connected to, the database or table name for which CDC has been enabled, the name of the adapter instance being suspended or resumed, or the name of the table column whose captured change could not be written to a tuple field.
-
Message, string: Returns a human-readable description of the event.
-
-
Capture (output): This is the adapter's primary output port. The adapter emits tuples on this port when changes are captured for the target database table. The port's schema contains the user-supplied fields from the Edit Schema tab along with the following metadata fields supplied by the adapter:
-
_Operation, string: contains one of the following values to convey the type of captured activity:.
-
Insert — A database record was inserted
-
Update-Old — A database record was inserted. The tuple contains the old record values.
-
Update-New — A database record was inserted. The tuple contains the new record values.
-
Delete — A database record was deleted
-
-
_CommitSeqNum, string: contains the transaction commit sequence number.
-
_SubSeqNum, string: contains the sequence number within the transaction.
-
UpdateMask, string: contains a bitmask indictating which columns changed.
-
The Change Data Capture input adapter uses typecheck messages to help you configure the adapter within your StreamBase application. In particular, the adapter generates typecheck messages for the following reasons:
-
A database host name or IP address is not specified.
-
A database TCP port number is not specified.
-
An invalid database TCP port number is specified.
-
A database username not specified.
-
A database password not specified.
-
A database name not specified.
-
A table name not specified.
The adapter generates warning messages during runtime under various conditions, including:
-
The adapter is shutting down because it failed to enable CDC.
-
A tuple field cannot be populated from a captured change.
-
A field specified in the Edit Schema tab has no corresponding column in the target database table.
-
The SQL Server agent service is not present (SQL Server only).
-
The adapter failed to start the SQL Server agent service (SQL Server only).
-
The adapter failed to read a record from the capture table.
When suspended, the adapter stops processing change data capture activity and no longer emits tuples on its primary output port.
When resumed, the adapter resumes processing change data capture activity and starts emitting tuples on its primary output port.