Configuring Connection Details

This section provides instructions for configuring the connection details required to establish communication between the Micro-Integration and your third-party system.

For information about configuring the connection to the event broker, see Connecting to Your Event Broker.

This Micro-Integration supports workflows only in the direction of:

  • IBM Db2 to Solace

The name of the binder for IBM Db2 CDC is db2cdc.

To share settings across all workflows in a Micro-Integration, configure the parameters using default instead of a workflow-specific identifier (for example, input-0). This reduces repetition when multiple workflows use the same parameter values. Workflow-specific settings override default settings when both are present.

IBM Db2 CDC Database Prerequisites

For the Micro-Integration to capture change events from IBM Db2 tables, a database administrator must configure the database for Change Data Capture (CDC). Unlike some other databases, IBM Db2 requires additional setup to enable SQL Replication (ASN), which is the mechanism the Micro-Integration uses to read database changes.

The following procedure uses Debezium user-defined functions (UDFs) to enable ASN and put tables into capture mode. Alternatively, you can use IBM Db2 control commands. After setup, you can adjust the capture agent configuration to optimize performance.

Before you run the following steps, ensure that:

  • You are logged in to IBM Db2 as the db2inst1 user.

  • On the IBM Db2 host, the Debezium management UDFs are available in the $HOME/asncdctools/src directory. UDFs are available from the Debezium examples repository.

  • The IBM Db2 command bldrtn is on PATH, for example by running export PATH=$PATH:/opt/ibm/db2/V11.5.0.0/samples/c/ with IBM Db2 11.5.

Procedure:Closed

To configure your database for CDC, perform the following steps:

  1. Compile the Debezium management UDFs on the IBM Db2 server host by using the bldrtn command provided with IBM Db2:

    cd $HOME/asncdctools/src
bldrtn asncdc

  2. Start the database if it is not already running. Replace DB_NAME with the name of the database that you want to connect to:

    db2 start db DB_NAME

  3. Ensure that JDBC can read the IBM Db2 metadata catalog:

    cd $HOME/sqllib/bnd
db2 connect to DB_NAME
db2 bind db2schema.bnd blocking all grant public sqlerror continue

  4. Ensure that the database was recently backed up. The ASN agents must have a recent starting point to read from. If you need to perform a backup, run the following commands, which prune the data so that only the most recent version is available. If you do not need to retain the older versions of the data, specify dev/null for the backup location.

    1. Back up the database. Replace DB_NAME and BACK_UP_LOCATION with appropriate values:

      db2 backup db DB_NAME to BACK_UP_LOCATION

    2. Restart the database:

      db2 restart db DB_NAME

  5. Connect to the database to install the Debezium management UDFs. Because you are logged in as the db2inst1 user, the UDFs are installed on the db2inst1 user:

    db2 connect to DB_NAME

  6. Copy the Debezium management UDFs and set permissions for them:

    cp $HOME/asncdctools/src/asncdc $HOME/sqllib/function
chmod 777 $HOME/sqllib/function

  7. Enable the Debezium UDF that starts and stops the ASN capture agent:

    db2 -tvmf $HOME/asncdctools/src/asncdc_UDF.sql

  8. Create the ASN control tables:

    db2 -tvmf $HOME/asncdctools/src/asncdctables.sql

  9. Enable the Debezium UDF that adds tables to capture mode and removes tables from capture mode:

    db2 -tvmf $HOME/asncdctools/src/asncdcaddremove.sql

    After you set up the IBM Db2 server, you use the UDFs to control IBM Db2 replication (ASN) with SQL commands. Some of the UDFs expect a return value, in which case you use the SQL VALUE statement to invoke them. For other UDFs, you use the SQL CALL statement.

  10. Start the ASN agent from an SQL client:

    VALUES ASNCDC.ASNCDCSERVICES('start','asncdc');

    Alternatively, run the following command from from the command prompt:

    db2 "VALUES ASNCDC.ASNCDCSERVICES('start','asncdc');"

    The preceding statement returns one of the following results:

    • asncap is already running

    • start --> <COMMAND>

      In this case, enter the specified <COMMAND> in the command prompt. The command looks like the following example:

      /database/config/db2inst1/sqllib/bin/asncap capture_schema=asncdc capture_server=SAMPLE &

  11. Invoke the following statement for each table that you want to put into capture mode. Replace MYSCHEMA with the name of the schema that contains the table you want to put into capture mode. Likewise, replace MYTABLE with the name of the table to put into capture mode:

    CALL ASNCDC.ADDTABLE('MYSCHEMA', 'MYTABLE');

  12. Reinitialize the ASN service:

    VALUES ASNCDC.ASNCDCSERVICES('reinit','asncdc');

Boolean Support

IBM Db2 provides native support for the BOOLEAN data type; however, SQL replication does not support BOOLEAN columns for CDC. Due to this limitation, the Micro-Integration cannot retrieve change data capture (CDC) events from tables that contain BOOLEAN columns.

Debezium can perform only initial snapshots on tables that contain BOOLEAN columns. Continuous change data capture (CDC) is not supported for these tables.

To avoid this limitation, we recommend that you do not to use BOOLEAN columns directly in tables for which you want to capture CDC events. As an alternative, you can model boolean values using numeric columns, typically SMALLINT, where 1 represents true and 0 represents false.

To enable automatic conversion of these columns to Boolean values in CDC events, specify the columns that represent boolean values by adding one or more regular expressions to the db2BooleanSelector property in application.yml, as shown in the following example, where the required column is named SMALLINT_COLUMN:

db2cdc:
  bindings:
    input-0:
      consumer:
        endpoint:
          query-parameters:
            skipDeleteEvents: false
            schemaHistoryInternalStoreOnlyCapturedTablesDdl: true
            db2BooleanSelector: .*.SMALLINT_COLUMN

IBM Db2 CDC Connection Details

To manually configure the IBM Db2 CDC connection details, set the following properties in application.yml:

spring:
  cloud:
    stream:
      db2cdc:
        bindings:
          input-0:
            consumer:
              endpoint:
                query-parameters:
                  databaseHostname: <hostname>
                  databasePort: <port>
                  databaseDbname: <database name>
                  databaseUser: <username>
                  databasePassword: <password>
                  <authentication parameter>: <authentication parameter value>
                  <authentication parameter>: <authentication parameter value>
                  ...

Where:

  • databaseHostname is the hostname or IP address of the IBM Db2 database server.

  • databasePort is the connection port of the IBM Db2 database server.

  • databaseDbname is the IBM Db2 database name.

  • databaseUser is the IBM Db2 user to log in as.

  • <authentication parameter> is one of the parameters required for authenticating with the IBM Db2 database server using client certificate authentication. For more information, see Client Certificate Authentication Parameters.

Client Certificate Authentication Parameters

For SSL/TLS certificate-based authentication, configure the following SSL properties in addition to the basic connection parameters.

Field Type Description

db2SslConnection

String

Specifies whether to use an encrypted connection. Set to true to enable SSL/TLS.
db2SslTruststoreLocation String The path to the truststore file containing trusted certificates for the IBM Db2 server.
db2SslTruststorePassword String The password for the truststore file.
db2SslTruststoreType String

The type of the SSL truststore. Specify one of:

  • JKS

  • PKCS12

Checkpoint Store Configuration

The IBM Db2 CDC stores the information about the current progress of processing change events in a checkpoint store backed by a Last Value Queue (LVQ) on a Solace event broker.

For example, add the following settings to the application.yml file:

solace:
  db2cdc:
    checkpoint:
      lvqName: <LVQ name>
      autoProvisionLvq: true

The following table lists the configuration options for the Checkpoint Store.

Config Option Type Valid Values Default Value Description

solace.db2cdc.checkpoint.lvqName

String

Required.

The name of the event broker Last Value Queue (spool size 0) to be used for checkpointing. The queue must exist on the same event broker and Message VPN as the target queue.

If the LVQ is deleted (administratively) or a message from the LVQ is deleted or consumed by another consumer, the IBM Db2 CDC will not be able to resume from the last checkpoint. In addition, the LVQ should not be shared by multiple instances of the IBM Db2 CDC.

solace.db2cdc.checkpoint.autoProvisionLvq

boolean

true, false

false

Optional.

Set to true to auto-provision the LVQ queue if it does not exist.

IBM Db2 CDC Binder Configuration Options

The following properties are available at the IBM Db2 CDC binder level.

Config Option Type Valid Values Description

spring.cloud.stream.bindings.input-<workflow-id>.destination

String

Format:

schemaName.tableName

For example, INVENTORY.PRODUCTS

The IBM Db2 table from which to consume change events. The destination must include the database name and the name of the table.

spring.cloud.stream.bindings.input-<workflow-id>.binder

String

db2cdc

This property must be set to db2cdc.

IBM Db2 CDC Consumer Configuration Options

The following table lists the source-specific properties for Debezium IBM Db2 CDC.

All properties must be prefixed with spring.cloud.stream.db2cdc.bindings.input-<workflow-id>.consumer.endpoint.query-parameters.

Example configuration:

spring:
  cloud:
    stream:
      db2cdc:
        bindings:
          input-0:
            consumer:
              endpoint:
                query-parameters:
                  <connection parameters>
                  skipDeleteEvents: true
                  snapshotMode: initial
                  ...
Config Option Type Valid Values Default Value Description
db2BooleanSelector String   None A regular expression to select SMALLINT columns that represent boolean values.
skipDeleteEvents boolean [true | false] false When true, indicates that the Micro-Integration skips DELETE operation events.

snapshotMode

String

initial, initial_only, schema_only, never, no_data, when_needed

no_data

Controls initial snapshot behavior. Select one of the following snapshot options:

  • initial—The Micro-Integration performs a database snapshot as described in the default workflow for creating an initial snapshot. After the snapshot completes, the Micro-Integration begins to stream event records for subsequent database changes.

  • initial_only—The Micro-Integration performs a database snapshot. After the snapshot completes, the Micro-Integration stops, and does not stream event records for subsequent database changes.

  • schema_only—Deprecated, see no_data.

  • never, no_data—The Micro-Integration does not run a snapshot. Upon first startup, the Micro-Integration immediately begins reading from the beginning of the log, but it does not create READ events to represent the data set at the point of the Micro-Integration's startup.

  • when_needed—After the Micro-Integration starts, it performs a snapshot only if it detects one of the following circumstances:

    • It cannot detect any topic offsets.

    • A previously recorded offset specifies a log position that is not available on the server.

maxBatchSize

Integer  

2048

The maximum number of events to batch.

maxQueueSize

Integer  

8192

The maximum size of the queue for change events read from the database log but not yet recorded or forwarded. Always set this value to be larger than the maximum batch size.
includeSchemaChanges boolean [true | false] false When true, indicates that the Micro-Integration captures DDL schema changes.
tombstonesOnDelete boolean [true | false] true Controls whether a delete event is followed by a tombstone event:

  • true—A delete operation is represented by a delete event and a subsequent tombstone event.

  • false—Only a delete event is emitted.

eventProcessingFailureHandlingMode

String fail, warn, skip

fail

Specifies how the Micro-Integration handles exceptions during processing of events. The possible values are:

  • fail—The Micro-Integration logs the offset of the problematic event and stops processing.

  • warn—The Micro-Integration logs the offset of the problematic event and continues processing with the next event.

  • skip—The Micro-Integration skips the problematic event and continues processing with the next event.
schemaNameAdjustmentMode String none, avro, avro_unicode None

Controls how schema and table names are adjusted to comply with serialization constraints:

  • avro_unicode—replaces the underscore or characters that cannot be used in the Avro type name with corresponding unicode like _uxxxx. (Note: _ is an escape sequence like backslash in Java).

  • none—does not apply any adjustment (default).
decimalHandlingMode String double, string, precise double

Specifies how DECIMAL and NUMERIC types are represented in change events:

  • precise—Uses java.math.BigDecimal to represent values, which are encoded in the change events using a binary representation and Kafka Connect’s org.apache.kafka.connect.data.Decimal type.

  • string—Uses string to represent values.

  • double—Represents values using Java's double, which may not offer the same precision but is easier to use in consumers.
timePrecisionMode String adaptive, connect adaptive

Determines how temporal types are represented in emitted events. Values include:

  • adaptive—Bases the precision of time, date, and timestamp values on the database column's precision.

  • connect—Represents time, date, and timestamp values using millisecond precision regardless of the database columns' precision.
tableExcludeList String   null A comma-separated list of regular expressions that match the fully-qualified names of tables to be excluded from monitoring.

skippedOperations

String c, u, d, t

t, d

A comma-separated list of the operation types that you want the Micro-Integration to skip during streaming. You can configure the Micro-Integration to skip the following types of operations:

  • c (insert/create)

  • u (update)

  • d (delete)

  • t (truncate)
schemaHistoryInternalStoreOnlyCapturedTablesDdl String [true | false] true Property that controls the Debezium DDL; if set to false, it captures all available tables.