Skip to content

Example: DB2 Replication with InfoCDC and AWS stack

Overview

This guide provides configuration steps for replicating IBMi DB2 database tables to external data sources in near real time using Infoview's lightweight Change Data Capture product InfoCDC and AWS messaging infrastructure.

Note: InfoConnect Hub 3.0.0 and higher enables direct IBM i replication to any JDBC compliant data platform, with no messaging infrastructure or custom coding required. Please refer to Hub Direct Database Replication Support for more details.

For this example, we use AWS stack (SNS + Lambda) for an end-to-end replication solution.

image

InfoCDC Configuration

Refer to InfoCDC installation and configuration guide

InfoConnect Hub and other services configuration

To replicate IBMi DB2 database tables data to external data sources using AWS services, complete the following steps:

  1. Configure an SNS topic.
  2. Configure Lambda function as a target for messages sent to the topic.
  3. Configure DQ poller to move messages from IBMi DB2 records to SNS topic.

Step 1: Configure an SNS topic

An Amazon SNS topic is a logical access point that acts as a communication channel.

To configure SNS topic

  1. Open Amazon SNS Console.
  2. On the Topic page, choose Create topic.
  3. On the Create topic page, do the following: - For Type, choose Standard to be able to use Lambda subscription protocol. - Enter the topic name in the Name field.
  4. Choose Create topic.

Copy the topic ARN to the clipboard. It will be used later for DQ poller configuration, for example:

arn:aws:sns:us-east-2:123456789012:AWSReplicationTopic

Amazon SNS allows you to configure more parameters for topics, including encryption, defining custom access policies, and so on. Additional information can be found here: Creating an Amazon SNS topic.

Step 2: Configure Lambda function

We will use a Lambda function to process Amazon Simple Notification Service (Amazon SNS) notifications and send them to an external database.

To configure Lambda function

  1. Open AWS Lambda Console.
  2. Choose Create function.
  3. On the Create function page, select Author from scratch.
  4. In Basic configuration, do the following: - Enter the function name, for example, AWSDataReplication. - For Runtime, choose Python 3.9 (other supported languages can also be used).
  5. Choose Create function.
  6. In the opened page with function configuration, paste the following code:
#importing packages
import json
import boto3
from decimal import Decimal

#function definition
def lambda_handler(event, context):
    dynamodb = boto3.resource('dynamodb')
    message = event['Records'][0]['Sns']['Message']
    print('Sns message: ' + message)

    #table name
    table = dynamodb.Table('awsdemo')

    #inserting values into table
    response = table.put_item(Item=json.loads(message, parse_float=Decimal))

    return message

The following code processes a message from SNS and sends it into a DynamoDB table with name awsdemo as an example. For InfoCDC data replication, it is also possible to use a different target database.

  1. Choose Deploy.
  2. In Function overview, choose Add trigger, then select SNS as a source.
  3. For SNS topic, choose the previously configured topic from the list.
  4. Choose Add.

Step 3: Configure DQ poller to move messages from IBMi DB2 records to an SNS topic

DQ poller component allows moving data from an IBMi DB2 record to a provided broker (JMS, SNS, Azure Service Bus brokers for now).

To configure DQ poller for polling data from IBMi into Amazon SNS

  1. Configure connection to specified IBMi server.

Use:

POST http://public-DNS-provided-by-Amazon:8080/admin/connections

with the sample request shown below.

Sample Connection request 
{
  "connectionName": "hubdemo",
  "endpoint": "your-as400-endpoint",
  "userId": "userid",
  "password": "encrypted-password",
  "libraryList": "comma-separated-list-of-libraries",
  "libraryListMode": "ADD_LAST",
  "jobTrace": false,
  "secureConnection": true,
  "tlsConfig": {
    "tlsIsInsecure": false,
    "tlsKeystoreConfigured": false,
    "tlsTruststoreConfigured": true,
    "tlsFileName": "info400new.truststore",
    "truststorePassword": "encrypted-password"
  },
  "connectionPoolConfig": {
    "operationType": 2,
    "preStartCountDataQueue": 2,
    "preStartCountCommand": 2,
    "maxConnections": 10,
    "maxInactivity": 3600000,
    "maxLifeTime": 86400000,
    "maxUseCount": -1,
    "maxUseTime": -1,
    "cleanupInterval": 300000,
    "pretestConnections": true,
    "runMaintenance": true,
    "threadUsed": true,
    "CCSID": 0
  },
  "transportConfig": {
    "licenseFileProtocol": "FILE",
    "truststoreFileProtocol": "S3",
    "licenseFileName": "as400-license.lic",
    "FILE": {
      "filePath": "license-file-location-in-shared-storage"
    },
    "S3": {
      "s3Bucket": "as400-bucket",
      "s3Region": "us-east-2",
      "s3AccessKey": "encrypted-aws-access-key",
      "s3SecretKey": "encrypted-aws-secret-key"
    }
  }
}
  1. Configure DQ poller with SNS broker.

Use:

POST http://public-DNS-provided-by-Amazon:8080/admin/connections/{connection-name}/dqpollers

with the sample request shown below.

Make sure that snsTopicName in snsProperties matches a topic ARN that you created before.

Sample DQ poller request for data replication with SNS 
{
  "pollerName": "sns-poller",
  "dqName": "HUBDQ",
  "libraryName": "INFOCDC",
  "messageBroker": "SNS",
  "messageBrokerProperties": {
    "snsProperties": {
      "snsTopicName": "arn:aws:sns:us-east-2:123456789012:AWSReplicationTopic",
      "accessKey": "encrypted-aws-access-key",
      "secretKey": "encrypted-aws-secret-key",
      "region": "us-east-2"
    }
  },
  "threadCount": 10,
  "dqFormatFileName": "HUBFT",
  "dqFormatFileLibrary": "INFOCDC"
}