Skip to content

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. 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 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 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 topic name in 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 to configure more parameters for topics, including encryption, defining custom access polices and so on. Additional information you can find 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 to external database

To configure Lambda function 1. Open AWS Lambda Console 2. Choose Create function 3. On Create function page, select Author from scratch 4. In Basic configuration, do the following: - Enter function name for example AWSDataReplication - For Runtime, choose Python 3.9 (can be used other of provided languages) 5. Choose Create function 6. In opened page with function configuration, paste following for 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 process message from SNS and send into DynamoDB table with name awsdemo as example. For InfoCDC data replication also possible to use different target database. 7. Choose Deploy 8. In Function overview choose Add trigger, then select SNS as a source 9. For SNS topic, choose a name previously configured topic from the list 10. Choose Add

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

DQ poller component allows to move data from IBMi DB2 record to provided broker (JMS, SNS, Azure ServiceBus 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 similar to 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 similar to 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" }