Introduction

This tutorial shows you how to integrate SQL Replication with InfoSphere® DataStage® to provide a continuous flow of data for dynamic warehousing and real-time insight and value from your business information.

DataStage provides robust capability for accessing data from different databases, but it cannot capture only changed data from reading the database logs. The SQL Replication function found in InfoSphere Replication Server and DB2® for Linux, UNIX, and Windows can capture just the changes but does not have the robust transformation capabilities of DataStage.

This tutorial helps you combine the best of both products to provide rapid warehouse updates and enable your organization to make business decisions based on the latest data. You will learn how to set up SQL Replication between two DB2 databases, and then configure DataStage to read directly from the target database to populate data sets for the warehouse.

The following figure shows the configuration for the tutorial.

Figure 1. Data flow from source DB2 database to InfoSphere DataStage through SQL Replication

Learning objectives

After you finish the tutorial, you should have learned the following skills:

Setting up SQL Replication between two DB2 databases by using ASNCLP command scripts
Using the ASNCLP program to generate DataStage definition files for creating DataStage jobs
Creating a DataStage project
Starting replication and verifying the initial target table load
Creating a data connection between DataStage and DB2
Importing table definitions from DB2 into DataStage
Changing the properties of individual stages within a DataStage job
Compiling and running a DataStage job
Browsing the contents of a data set to verify end-to-end integration

Time required

This tutorial requires three or four hours to finish.

Skill level

Intermediate

Audience

Replication administrators, database administrators, DataStage administrators

System requirements

The following products are required for the tutorial:

IBM® DB2 for Linux, UNIX, and Windows Version 9.7 Fix Pack 4 or later (DB2 for Linux, UNIX, and Windows includes SQL replication).
IBM InfoSphere Information Server for Linux, UNIX, and Windows Version 8.1 or later
Linux, UNIX, or Windows system that supports installation of the Information Server and client

A typical configuration for this tutorial would be a Windows or Linux system with DB2, SQL Replication, and an Information Server client installed. You could install the Information Server engine and services tiers on the same system, but if these components require more memory or disk space, they might need to run on a larger system. For more details, see the Information Server system requirements page.

Prerequisites

Install all required software products.
Download the sqlrep-ds-tutorial-files.zip file from the InfoSphere Replication Server and Data Event Publisher samples collection on IBM developerWorks®. Unzip the file on the system where you will create the target database.

< Previous | Next >