SQL Server CDC Client

The SQL Server CDC Client origin processes data in Microsoft SQL Server change data capture (CDC) tables. The origin fetches changes in time windows. For information about supported versions, see Supported Systems and Versions in the Data Collector documentation.

Use the SQL Server CDC Client origin to generate records from CDC tables. To read data from Microsoft SQL Server change tracking tables, use the SQL Server Change Tracking origin. For more information about the differences between CDC and change tracking data, see the Microsoft SQL Server documentation. To read data from SQL Server temporal tables, use the JDBC Multitable Consumer origin or the JDBC Query Consumer origin. For more information about temporal tables, see the Microsoft documentation.

By default, the SQL Server CDC Client origin generates a record for each record in the CDC table. You can configure the origin to combine the two update records that SQL Server generates for each update. This changes the structure of the generated records and produces additional record header attributes.

The SQL Server CDC Client origin includes the CRUD operation type in a record header attribute so generated records can be easily processed by CRUD-enabled destinations. For an overview of Data Collector changed data processing and a list of CRUD-enabled destinations, see Processing changed data.

You might use this origin to perform database replication. You can use a separate pipeline with the JDBC Query Consumer or JDBC Multitable Consumer origin to read existing data. Then start a pipeline with the SQL Server CDC Client origin to process subsequent changes.

When you configure the origin, you specify the SQL Server capture instance names - the origin processes the related CDC tables. You can define groups of tables in the same database and any initial offsets to use. When you omit initial offsets, the origin processes all available data in the CDC tables.

You can enable late table processing to allow the origin to process tables that appear after the pipeline starts. You can also configure the origin to check for schema changes in processed tables and to generate an event after discovering a change.

To determine how the origin connects to the database, you specify connection information, a query interval, number of retries, and any custom JDBC configuration properties that you need. You can configure advanced connection properties. You can also use a connection to configure the destination.

The origin can generate events for an event stream. For more information about dataflow triggers and the event framework, see Dataflow triggers overview.

When a pipeline stops, the SQL Server CDC Client origin notes where it stops reading. When the pipeline starts again, the origin continues processing from where it stopped by default. You can reset the origin to process all requested data.

JDBC driver

When connecting to Microsoft SQL Server, you do not need to install a JDBC driver. Data Collector includes the JDBC driver required for SQL Server.

Supported operations

The SQL Server CDC Client origin supports the SQL Server insert and delete operations. Updates captured after the update operation are treated as update, and updates captured before the update operation are treated as an unsupported operation.

Time windows

The SQL Server CDC Client origin fetches changes from tables in time windows. By default, the origin fetches changes in one time window, beginning with the last committed offset and ending with the latest data. When you have large volumes of data, you can improve performance by configuring multiple time windows.

To configure multiple time windows, set the Maximum Transaction Length property to the desired size of the time windows. The first time window starts at the initial offset or last saved offset and ends the specified number of seconds later. The next window starts where the previous ended and ends the specified number of seconds later, and so on. Within each time window, the origin creates batches based on the batch settings.

When you enable event generation, the origin produces a no-more-data event at the end of each time window, even when subsequent time windows remain for processing.

Previewing data shows no values when there are no changes during the first time window.

Table configuration

When you configure SQL Server CDC Client, you can define multiple CDC tables using a single set of table configuration properties. You can also define multiple table configurations to process multiple groups of CDC tables.

When you define table configurations you can define the following properties for each set of tables:
Capture Instance Name
Determines the CDC tables to process. The naming convention for Microsoft SQL Server CDC tables is <capture instance name>_CT. When specifying this property, use the capture instance name, not the names of the CDC tables to be processed. For example, specify the dbo.customer source table, not the associated CDC table, dbo_customer_CT.
When you configure the Capture Instance Name property, you can specify a pattern that describes a set of capture instance names to use. If capture instance names are not set up in the database, then you specify a pattern for the table names to process.
You can use SQL-like syntax to define the set of CDC tables to process. For example, the pattern east% matches tables whose names start with "east". For more information about SQL LIKE syntax, see https://msdn.microsoft.com/en-us/library/ms179859.aspx.
When configuring the Capture Instance Name property, you can use one of the following formats:
  • To process the CDC tables that match the specified capture instance name pattern, use the following format:
    <capture instance name pattern>

    Use this format when CDC tables are created based on capture instance names. You can use the pattern to process a full set of CDC tables or to exclude some CDC tables from processing.

    For example, say you have a Sales.Accounts table with a CDC table named Sales_Accounts_CT. After adding several columns to the table, you create a new CDC table called Sales_Accounts2_CT.

    To process both CDC tables, you can specify the following capture instance name pattern: Sales_Accounts%. To process only the CDC data that occurred after the schema change, you can specify the following capture instance name: Sales_Accounts2.

  • To process all available CDC tables for the specified data tables, use the following format:
    <schema name>_<data table name pattern>

    Use this format when CDC tables are created based on data tables instead of capture instance names.

    For example, to process all available CDC tables for data tables in a Sales schema, you might use Sales_%. Or, to process the CDC tables associated with a set of data tables with the Transact prefix, you might use Sales_Transact%.

  • To process all CDC tables associated with the schema, use the following format:
    <schema name>_%

    For example, to process all tables in the sales schema, enter sales_%.

Table exclusion pattern
Optionally specify a regex pattern for the table names that you want to exclude from the query.

For example, say you want to process all CDC tables in the schema except for those that start with "dept". You can use the default % for the table name pattern, and enter dept* for the table exclusion pattern.

For more information about using regular expressions with Data Collector, see Regular expressions overview.

Initial offset
To process existing data, specify an initial offset. When not set, the origin processes all available CDC data.
The SQL Server CDC Client origin uses the __$start_lsn column as the offset column. To process existing data, define the offset value to use. The offset is used for all tables included in the table configuration.
Important: When you specify an initial offset, the origin starts with the first value greater than or equal to the specified offset.

Initial table order strategy

You can define the initial order that the origin uses to read the tables.

Define one of the following initial table order strategies:
None
Reads the tables in the order that they are listed in the database.
Alphabetical
Reads the tables in alphabetical order.

Allow late table processing

You can configure the SQL Server CDC Client to process data in CDC tables that appear after the pipeline starts.

When you allow late table processing, the SQL Server CDC Client origin uses a background thread to check for late CDC tables. The origin checks at regular user-defined intervals.

To enable late table processing, configure the following properties:
  • On the JDBC tab, select the Allow Late Tables property.
  • To define the time to wait before checking for new tables, configure the New Table Discovery Interval property.
  • On the Advanced tab, set the Maximum Pool Size and Minimum Idle Connections properties to 2.

Checking for schema changes

You can configure the SQL Server CDC Client origin to check for schema changes in the tables being processed. When checking for schema changes, the origin includes a schema check statement in the SQL query.

When the origin checks for schema changes, it performs the following tasks:
  1. Compares current table schemas with the original table schemas at regular intervals, based on the Query Interval property.
  2. If it determines that the schema of a table has changed, it generates a schema-change event that states the table or capture instance name with the changed schema.

    The origin generates a schema-change event each time that it finds a schema change: one for each table with a schema change.

    Note: Since the origin continues to check for schema changes at regular intervals until the pipeline stops, a single schema change can generate a large volume of events.
  3. It can write the exact column name or data type change to the Data Collector log. To enable writing to the log, the log level must be set to Trace. For information about changing the log level, see the Control Hub documentation.
To enable the SQL Server CDC Client origin to check for schema changes and generate events when discovering them, enable both of the following properties:
  • On the General tab, select the Produce Events property.
  • On the JDBC tab, select the Enable Schema Changes Event property.

Generated record

The SQL Server CDC Client origin generates records from CDC tables, placing the CDC information, such as the CDC operation type and start LSN values, in record header attributes and generating field attributes for some converted fields.

The origin generates records for updates differently depending on the value you set for the Record Format property:
  • Basic - Generates two records for updates, one with the old data and one with the changed data.
  • Basic discarding ‘Before Update’ records - Generates one record for updates, containing the changed data.
  • Rich - Generates one record for updates with data written to the Data field, OldData field, or both.

The origin generates a record header attribute named record_format, which indicates the format of the generated record: 1 indicates basic format, 2 indicates basic discarding “before update” records, and 3 indicates rich.

Basic Record Format

By default, the Record Format property is set to Basic. The origin creates one record for each record in the CDC table. SQL Server generates two records in the CDC table for each update, one with the old data and one with the changed data. Therefore, each update operation results in two records. The origin writes the data to fields that match the data fields in the CDC table.

For example, suppose a CDC table with three fields (id, name, and dt) has four records: a record for an insert, two records for an update, and a record for a delete. With the Record Format property set to Basic, the origin generates four records, one for each record in the CDC table, as shown in the following image:

Output Data showing four records

Basic Discarding Before Update Record Format

When you set the Record Format to Basic discarding Before Update records, the origin creates one record for each record in the CDC table. For updates, the record contains the changed data.

For example, suppose a CDC table with three fields (id, name, and dt) has four records: a record for an insert, two records for an update, and a record for a delete. With the Record Format property set to Basic discarding Before Update records, the origin generates three records, one for the insert record, one for the delete record, and a single update record showing the changed data, as shown in the following image:

Output Data showing three records

Rich Record Format

When you set the Record Format property to Rich, the origin combines the data from the two CDC records that SQL Server creates for an update operation into a single record. With the Record Format property set to Rich, the origin writes the data to the Data field, OldData field, or both, depending on the operation type:
  • For an insert operation, the origin writes the inserted data in the Data map field and does not create an OldData field.
  • For an update operation, the origin writes the data before the update in an OldData map field and the data after the update in a Data map field.
  • For a delete operation, the origin writes the data before the deletion in the OldData map field and does not create a Data field.
Tip: Use processors to restructure the record if necessary.

You might set the Record Format property to Rich to produce one record for each update, similar to how other origins generate records.

For example, suppose a CDC table with three fields (id, name, and dt) has four records: a record for an insert, two records for an update, and a record for a delete. With the Record Format property set to Rich, the origin generates three records, as shown in the following image:

Output Data showing three records

Note that the update record includes the previous data in an OldData map field, and the updated data in a Data map field. If all you need is the updated data, you can use the Field Remover processor to drop the OldData field from the record. And you could use a Field Flattener processor to flatten the fields in the Data field.

Record header attributes

The SQL Server CDC Client origin generates JDBC record header attributes that provide the SQL Server CDC data for each record, such as the start or end log sequence numbers (LSNs).

The origin also includes the sdc.operation.type attribute, record_format attribute, and information from the SQL Server CDC tables. The SQL Server CDC Client header attributes are prefixed with jdbc. The names of the SQL Server CDC column names are included in the header attribute name, as follows: jdbc.<CDC column name>.

You can use the record:attribute or record:attributeOrDefault functions to access the information in the attributes. For more information about working with record header attributes, see Working with header attributes.

The origin provides the following header attributes:
Header Attribute Name Description
sdc.operation.type
Provides one of the following values to represent the operation type:
  • 1 for Insert
  • 2 for Delete
  • 3 for Update, including updates captured after the update operation
  • 5 for unsupported operations, including updates captured before the update operation
record_format Provides one of the following values to represent the generated record format:
  • 1 for Basic
  • 2 for Basic discarding Before Update records
  • 3 for Rich
jdbc.cdc.source_schema_name Provides the source schema for the CDC data.
jdbc.cdc.source_name Provides the source table for the CDC data.
jdbc.tables
Provides a comma-separated list of source tables for the fields in the record.
Note: Not all JDBC drivers provide this information.
jdbc.<column name>.jdbcType Provides the numeric value of the original SQL data type for each field in the record. See the Java documentation for a list of the data types that correspond to numeric values.

Because the record read from the SQL Server CDC table includes CDC columns, the generated record also includes corresponding jdbc.<column name>.jdbcType header attributes for those columns.

For example, since the original data includes a __$start_lsn column, the resulting record has a jdbc.__$start_lsn.jdbc.Type header attribute. It also has a jdbc.__$start_lsn attribute that is generated separately by the origin, as described below.
jdbc.<column name>.jdbc.precision Provides the original precision for all numeric and decimal fields.
jdbc.<column name>.jdbc.scale Provides the original scale for all numeric and decimal fields.
jdbc.primaryKeySpecification Provides a JSON-formatted string that lists the columns that form the primary key in the table and the metadata for those columns.
For example, a table with a composite primary key contains the following attribute: 
jdbc.primaryKeySpecification = 
     {{"<primary key column 1 name>":
         {"type": <type>, 
          "datatype": "<data type>", 
          "size": <size>, 
          "precision": <precision>, 
          "scale": <scale>, 
          "signed": <Boolean>,  
          "currency": <Boolean> }},
          ...,
     {"<primary key column N name>":
         {"type": <type>, 
          "datatype": "<data type>", 
          "size": <size>, 
          "precision": <precision>, 
          "scale": <scale>, 
          "signed": <Boolean>,  
          "currency": <Boolean> } } }
A table without a primary key contains the attribute with an empty value: 
jdbc.primaryKeySpecification = {}
jdbc.primaryKey.before.<primary key column name> Provides the old value for the specified primary key column.

Generated when the record represents an update operation on a table with a primary key.

jdbc.primaryKey.after.<primary key column name> Provides the new value for the specified primary key column.

Generated when the record represents an update operation on a table with a primary key.
jdbc. __$command_id Data from the SQL Server CDC __$command_id column.
jdbc.__$end_lsn Data from the SQL Server CDC __$end_lsn column.
jdbc.__$operation The CRUD operation type using SQL Server codes, as defined in the SQL Server CDC __$operation column.
jdbc.__$seqval Data from the SQL Server CDC __$seqval column.
jdbc.__$start_lsn Data from the SQL Server CDC __$start_lsn column.
jdbc.__$update_mask Data from the SQL Server CDC __$update_mask column.

For details about the CDC attributes, see the SQL Server documentation.

CRUD operation header attributes

When generating records, the SQL Server CDC Client origin specifies the operation type in both of the following record header attributes:
sdc.operation.type
The SQL Server CDC Client origin writes the operation type to the sdc.operation.type record header attribute.
The origin uses the following values in the sdc.operation.type record header attribute to represent the operation type:
  • 1 for Insert
  • 2 for Delete
  • 3 for Update, including updates captured after the update operation
  • 5 for unsupported operations, including updates captured before the update operation

If you use a CRUD-enabled destination in the pipeline such as JDBC Producer or Elasticsearch, the destination can use the operation type when writing to destination systems. When necessary, you can use an Expression Evaluator processor or any scripting processor to manipulate the value in the header attribute. For an overview of Data Collector changed data processing and a list of CRUD-enabled destinations, see Processing changed data.

When using CRUD-enabled destinations, the destination looks for the operation type in the sdc.operation.type attribute only.
jdbc.__$operation
The SQL Server CDC Client origin places the values from the SQL Server __$operation column in the jdbc.__$operation record header attribute. As a result, the jdbc.__$operation record header attribute contains the CRUD operation type as defined using SQL Server CDC codes.
Note that CRUD-enabled stages only use the sdc.operation.type header attribute, they do not check the jdbc.__$operation header attribute.
SQL Server CDC uses the following codes to define the operation type:
  • 1 for delete
  • 2 for insert
  • 3 for updates captured before the update operation
  • 4 for updates captured after the update operation

Field attributes

The SQL Server CDC Client origin generates field attributes for columns converted to the Decimal or Datetime data types in Data Collector. The attributes provide additional information about each field.

The following data type conversions do not include all information in the corresponding Data Collector type:
  • SQL Server Decimal and Numeric data types are converted to the Data Collector Decimal data type, which does not store scale and precision.
  • SQL Server Datetime, Datetime2, and Smalldatetime data types are converted to the Data Collector Datetime data type, which does not store nanoseconds.
To preserve this information during data type conversion, the origin generates the following field attributes for these Data Collector data types:
Data Collector Data Type Generated Field Attribute Description
Decimal precision Provides the original precision for every decimal or numeric column.
Decimal scale Provides the original scale for every decimal or numeric column.
Datetime nanoSeconds Provides the original nanoseconds for every datetime, datetime2, or smalldatetime column.

You can use the record:fieldAttribute or record:fieldAttributeOrDefault functions to access the information in the attributes. For more information about working with field attributes, see Field attributes.

Event generation

The SQL Server CDC Client origin can generate events that you can use in an event stream. When you enable event generation, the origin generates an event when it completes processing the data returned by the specified queries for all tables.

If you enable schema change event generation, the origin also generates an event each time it finds a schema change.

SQL Server CDC Client events can be used in any logical way. For example:

For more information about dataflow triggers and the event framework, see Dataflow triggers overview.

Event record

Event records generated by SQL Server CDC Client origin have the following event-related record header attributes:
Record Header Attribute Description
sdc.event.type Event type. Uses one of the following types:
  • no-more-data - Generated when the origin completes processing all the requested CDC data in a time window.
  • schema-change - Generated only when checking for schema changes is enabled and when the origin determines that a schema change has occurred for one of the tables being processed.
sdc.event.version Integer that indicates the version of the event record type.
sdc.event.creation_timestamp Epoch timestamp when the stage created the event.
The SQL Server CDC Client origin can generate the following types of event records:
no-more-data

The origin generates a no-more-data event record when the origin completes processing all available data in a time window and the number of seconds configured for event generation delay elapses without any new files appearing to be processed. The origin generates the event record even when subsequent time windows remain for processing.

The no-more-data event record generated by the origin has the sdc.event.type set to no-more-data and does not include any additional fields.

schema-change

The origin generates a schema-change event record only when you enable the origin to check for schema changes, and the origin discovers a schema change.

The schema-change event record generated by the origin has the sdc.event.type set to schema-change and includes the following fields:
Event Record Field Description
capture-instance-name The name of the capture instance or CDC table associated with the table with the schema change.
source-table-schema-name The name of the schema that contains the data table.
source-table-name The name of the data table that has a schema changes.

Configuring a SQL Server CDC Client origin

Configure a SQL Server CDC Client origin to process data in Microsoft SQL Server CDC tables.
  1. In the Properties panel, on the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Produce Events Generates event records when events occur. Use for event handling.
    On Record Error Error record handling for the stage:
    • Discard - Discards the record.
    • Send to Error - Sends the record to the pipeline for error handling.
    • Stop Pipeline - Stops the pipeline.
  2. On the JDBC tab, configure the following properties:
    JDBC Property Description
    Connection Connection that defines the information that is required to connect to an external system.

    To connect to an external system, you can select a connection that contains the details, or you can directly enter the details in the pipeline. When you select a connection, Control Hub hides other properties so that you cannot directly enter connection details in the pipeline.

    JDBC Connection String

    Connection string used to connect to the database.
    Record Format Format of update records generated from CDC tables:
    • Basic - Generates two records for updates, one with the old data and one with the changed data.
    • Basic discarding Before Update records - Generates one record for updates, containing the changed data.
    • Rich - Generates one record for updates with data written to the Data field, OldData field, or both.
    Use Direct Table Query Queries the CDC table directly.

    When enabled, the origin fetches data directly from the CDC table, which typically uses fewer connections to the database. Otherwise, the origin fetches data from the CDC table using system queries, which can require more connections to the database.

    Note: When disabled, Data Collector obtains a lock on the change tracking table, which can prevent a built-in stored procedure that performs a cleanup job from running until the pipeline completes.
    Fetch Size Maximum number of rows to fetch and store in memory on the Data Collector machine. The size cannot be zero.

    Default is 1,000.
    Use Credentials Enables entering credentials. Use when you do not include credentials in the JDBC connection string.
    Queries per Second Maximum number of queries to run in a second across all partitions and tables. Use 0 for no limit.

    Default is 10.

    Max Batch Size (records) Maximum number of records to include in a batch.
    Max Clob Size (characters) Maximum number of characters to be read in a Clob field. Larger data is truncated.
    Max Blob Size (bytes)

    Maximum number of bytes to be read in a Blob field.

    Number of Retries on SQL Error Maximum number of times the origin tries to execute the query after encountering a SQL error. After retrying this number of times, the origin handles the error based on the error handling configured for the origin.

    Use to handle transient network or connection issues that prevent the origin from submitting a query.

    Default is 0.

    Allow Late Tables Allows the origin to process tables that appear after the pipeline starts.

    When enabled, the origin uses a background thread to check for additional tables to process. For information about adjusting related configuration properties, see Allow late table processing.
    New Table Discovery Interval Time to wait before checking for additional tables to process.

    Available if you enable Allow Late Tables.
    Enable Schema Changes Event Enables regular checks for schema changes.

    When enabled, the origin checks for schema changes for all processed tables at regular intervals based on the Query Interval property. The origin generates a schema change event each time it discovers a schema change.

    Maximum Transaction Length Size of the time windows that the origin uses to fetch data. Specify in seconds or with a time expression. Default value is -1.

    When set to -1, the origin fetches changes in one time window, beginning with the last committed offset and ending with the latest data. Setting a value improves performance with large volumes of data.

    When you set a value, the origin fetches changes over multiple time windows. The property defines the size of the time windows.

    No-more-data Event Generation Delay (seconds) Number of seconds to delay generation of the no-more-data event after processing all rows. Use to allow time for additional data to arrive before generating the no-more-data event.
    Convert Timestamp To String Enables the origin to write timestamps as string values rather than datetime values. Strings maintain the precision stored in the source system. For example, strings can maintain the precision of a high-precision SQL Server datetime2 field.

    When writing timestamps to Data Collector date or time data types that do not store nanoseconds, the origin stores any nanoseconds from the timestamp in a field attribute.
    Additional JDBC Configuration Properties Additional JDBC configuration properties to use. To add properties, click Add and define the JDBC property name and value.

    Use the property names and values as expected by JDBC.

  3. On the CDC tab, define one or more table configurations. Using simple or bulk edit mode, click the Add icon to define another table configuration.
    CDC Property Description
    Capture Instance Name Determines the set of CDC tables to process. Use SQL LIKE syntax to define a table name pattern for the table names. Use one of the following formats:
    • To process the CDC tables that match the specified capture instance name pattern, use the following format:
      <capture instance name pattern>
    • To process all available CDC tables for the specified data tables, use the following format:
      <schema name>_<data table name pattern>
    • To process all CDC tables associated with the schema, use the following format:
      <schema name>_%

    Default is dbo_%, which processes all available CDC tables in the default dbo schema.
    Table Exclusion Pattern Pattern of the table names to exclude from being read for this table configuration. Use a Java-based regular expression, or regex, to define the pattern.

    Leave empty if you do not need to exclude any tables.

    To view and configure this option, click Show Advanced Options.
    Initial Offset Offset value to use for this table configuration. When the pipeline starts, the offset value determines where the origin starts processing:
    • 0 or higher - Start with the first value in the offset column greater than or equal to the offset value.
    • Blank - Start with the lowest value in the offset column.
    • -1 - Start with new, incoming changes, ignoring existing data.

    To view and configure this option, click Show Advanced Options.
  4. To enter JDBC credentials separately from the JDBC connection string, on the Credentials tab, configure the following properties:
    Credentials Property Description
    Username User name for the JDBC connection. The user account must have the correct permissions or privileges in the database.
    Password Password for the account.
    Tip: To secure sensitive information such as user names and passwords, you can use runtime resources or credential stores. For more information about credential stores, see Credential Stores in the Data Collector documentation.
  5. When you use JDBC versions older than 4.0, on the Legacy Drivers tab, optionally configure the following properties:
    Legacy Drivers Property Description
    JDBC Class Driver Name Class name for the JDBC driver. Required for JDBC versions older than version 4.0.
    Connection Health Test Query Optional query to test the health of a connection. Recommended only when the JDBC version is older than 4.0.
  6. On the Advanced tab, optionally configure the following properties:
    The defaults for these properties should work in most cases:
    Advanced Property Description
    Initial Table Order Strategy Initial order used to read the tables:
    • None - Reads the tables in the order that they are listed in the database.
    • Alphabetical - Reads the tables in alphabetical order.
    On Unknown Type Action to take when encountering an unsupported data type:
    • Stop Pipeline - Stops the pipeline after completing the processing of the previous records.
    • Convert to String - When possible, converts the data to string and continues processing.
    Maximum Pool Size Maximum number of connections to create.

    Default is 1.
    Minimum Idle Connections Minimum number of connections to create and maintain. To define a fixed connection pool, set to the same value as Maximum Pool Size.

    Default is 1.

    Connection Timeout (seconds) Maximum time to wait for a connection. Use a time constant in an expression to define the time increment.
    Default is 30 seconds, defined as follows: 
    ${30 * SECONDS}
    Idle Timeout (seconds) Maximum time to allow a connection to idle. Use a time constant in an expression to define the time increment.

    Use 0 to avoid removing any idle connections.

    When the entered value is close to or more than the maximum lifetime for a connection, Data Collector ignores the idle timeout.

    Default is 10 minutes, defined as follows: 
    ${10 * MINUTES}
    Max Connection Lifetime (seconds) Maximum lifetime for a connection. Use a time constant in an expression to define the time increment.

    Use 0 to set no maximum lifetime.

    When a maximum lifetime is set, the minimum valid value is 30 minutes.

    Default is 30 minutes, defined as follows: 
    ${30 * MINUTES}
    Auto Commit Determines if auto-commit mode is enabled. In auto-commit mode, the database commits the data for each record.

    Default is disabled.
    Enforce Read-only Connection Creates read-only connections to avoid any type of write.

    Default is enabled. Disabling this property is not recommended.

    Transaction Isolation Transaction isolation level used to connect to the database.

    Default is the default transaction isolation level set for the database. To override the database default, select one of the following levels:

    • Read committed
    • Read uncommitted
    • Repeatable read
    • Serializable
    Init Query SQL query to perform immediately after the stage connects to the database. Use to set up the database session as needed.

    The query is performed after each connection to the database. If the stage disconnects from the database during the pipeline run, for example if a network timeout occurrs, the stage performs the query again when it reconnects to the database.