Contents
The Spotfire Streaming 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 required field to specify or change the name of this instance of this component. The name must be unique within the current EventFlow module. The name can contain alphanumeric characters, underscores, and escaped special characters. Special characters can be escaped as described in Identifier Naming Rules. The first character must be alphabetic or an underscore.
Adapter: A read-only field that shows the formal name of the adapter.
Class name: Shows the fully qualified class name that implements the functionality of this adapter. If you need to reference this class name elsewhere in your application, you can right-click this field and select Copy from the context menu to place the full class name in the system clipboard.
Start options: This field provides a link to the Cluster Aware tab, where you configure the conditions under which this adapter starts.
Enable Error Output Port: Select this checkbox 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 to learn about Error Ports.
Description: Optionally, enter text to briefly describe the purpose and function of the component. In the EventFlow Editor 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. |
| Capture Instance Name | The capture instance name of the for which changes are being captured. This value is only valid for SQL Server; if left blank the value will be generated using {tablename}_capture_instance. |
| Perform Enable CDC Calls | If enabled the adapter will try to enable CDC on the given database and table. You can disable this option if you have already manually enabled CDC on the given database and table. |
| Start SQL Server Agent Service | If enabled the adapter will try to also start the SQL server Agent Service. If you are connecting to a SQL Server database from a non-windows machine, or a machine without SQL Server installed then disable this option. |
| 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. |
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 settings in this tab to enable this operator or adapter for runtime start and stop conditions in a multi-node cluster. During initial development of the fragment that contains this operator or adapter, and for maximum compatibility with releases before 10.5.0, leave the Cluster start policy control in its default setting, Start with module.
Cluster awareness is an advanced topic that requires an understanding of StreamBase Runtime architecture features, including clusters, quorums, availability zones, and partitions. See Cluster Awareness Tab Settings on the Using Cluster Awareness page for instructions on configuring this tab.
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 Spotfire Streaming 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 indicating 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.