Switch to standard view 
  Sybase logo
 
 
 



Replication to DB2 gatewayless through Open Server 4.0

 

Overview

This report documents the process of configuring the replication server in order to replicate from a Sybase Adaptive Server to IBM?s DB2 gatewayless through the Open Server 4.0 product. It is not intended to address how to set-up the Open Server 4.0 product to connect to IBM?s DB2 gatewayless.

Test Environment

This section explains the test environment on which this process was developed. While this demonstrates the working environment, it is not meant to preclude the combination of other library (DLL) versions or other hardware/software configurations.

Client/Server Hardware

System

Hardware Description

Sybase Replication Server 11.51

Intel-based 586 system with 96MB RAM

Sybase Open Server 4.0

IBM 3090

Microsoft Windows NT 4.0

Intel-based 586 system with 96MB RAM

Client/Server Software

Product

Version

Sybase Replication Server

Version 11.5 running on Windows NT 4.0

Open Server for CICS

Version 4.0

Open Client / Open Server

Version 11.1.1 running on Windows NT 4.0

Host Information

Product

Version

IBM?s DB2

Version 5.1

 

Architecture

The Replication Server maintains replicated copies of tables at multiple sites on a network. This architecture can be used to create a separate copy of a production on-line transaction processing (OLTP) system to allow decision support applications to run separately from the OLTP system. Providing a separate, reference-only copy of the data for decision support applications allows the OLTP systems to continue unobstructed.

Data updates are made to the primary table and replicated to the replicate copies by the Replication Server.

 

The replicated copies are initially populated through the process of materialization. A copy of the primary table can be materialized at the replicate site by the Replication Server via a ?select? SQL statement routed through a Sybase Interoperability product.

The Replication Server system is administered through a LAN based interface. The system administrator creates subscriptions to primary tables through Replication Command Language (RCL). The subscriptions and replication definitions define the columns and rows to be managed by the Replication Server at the replicate sites. Destinations and routes can be defined by the system administrator to manage network traffic.

 

 

Setup

This section explains how to set up Replication Server for replication of data to DB2. The setup involves the following steps:

  1. Installation of Replication Server and Open Server 4.0.
  2. Define Replication Server function string class groups for a set of function strings used with the replicate database.
  3. Define Replication Server function strings that contain the database-specific instructions for replication into the replicate database.
  4. Prepare the replicate databases for replication.
  5. Create a Replication Server error class for DB2.
  6. Create and configure a connection from Replication Server to the replicate database.
  7. Create a sample replication definition and setup the sample subscription for a table in the primary database.

Replication from ASE to Open Server 4.0 gatewayless

  1. Install Replication Server according to the product installation documentation.
  2. Install Open Server according to the product installation documentation and configure it to access DB2 gatewayless.
  3. Create an interfaces file entry for the Open Server to point to the Mainframe TCP/IP address and the port of the Open Server and put it in the replication server?s interfaces file. For the purpose of this report, the server entry in the interfaces file will be called "osgtwls"
  4. Define the DB2 Function String Class:

The DB2 function string class groups a set of function strings used when replicating into a DB2 database. The function string class contains function strings for the system functions and for any user-defined functions. The function string class must be created before you can create any functions strings belonging to that class.

Log into the Replication Server and create the DB2 function string class using the following command:

 

  • create function string class db2_function_class

 

Note: "db2_function_class" can be any name you choose. Once you choose the name for your class, you will need it for all subsequent actions on that function string class.

 

 

  1. Define the DB2 Function Strings

Replication Server converts functions to commands for destination data servers, and submits them to these data servers. For example, a new row inserted in a primary database causes Replication Server to distribute a rs_insert function to replicate databases with subscriptions for that row. The function strings therefore contain the database-specific instructions for executing a particular function. The following is an example of how to create the function strings that are necessary for replicating into DB2 via the Open Server 4.0 product in a gatewayless configuration.

Log into the Replication Server and create the following DB2 function strings:

 

 

  • create create function string rs_begin

for db2_function_class output language 'BEGIN TRANSACTION'

 

  • create function string rs_commit

for db2_function_class output language

'delete from RS_LASTCOMMIT where origin=?rs_origin!sys?;

insert into RS_LASTCOMMIT (ORIGIN,ORIGIN_QID,SECONDARY_QID) values

(?rs_origin!sys?,"?rs_origin_qid!sys_raw?","?rs_secondary_qid!sys_raw?");commit'

 

  • create function string rs_rollback

for db2_function_class output language 'rollback'

 

  • create function string rs_usedb

for db2_function_class output language 'set current sqlid=USER?

 

  • create function string rs_get_lastcommit

for db2_function_class output language

'select ORIGIN,ORIGIN_QID,SECONDARY_QID from RS_LASTCOMMIT'

  • create function string rs_get_sortorder

for db2_function_class output language

'select BINARY from RS_SERVER_INFO'

 

When executed by Replication Server, the above function string will return the string value "BINARY" to Replication Server. This tells Replication Server that the sort order "binary" is used with the DB2 data server. The Replication Server Maintenance ID that Replication Server uses to log into DB2 must have the correct privilege to access the RS_SERVER_INFO table.

 

  • create function string rs_get_charset

for db2_function_class output language

'select ISO_1 from RS_SERVER_INFO'

 

When executed by Replication Server, the above function string will return the string value "ISO_1" to Replication Server. This tells Replication Server that the DB2 data server is using the "iso_1" character set. The Replication Server Maintenance ID that Replication Server uses to log into DB2 must have the correct privilege to access the RS_SERVER_INFO table.

 

 

 

  • create function string rs_marker

for db2_function_class output language

'insert into RS_MARKER values(?rs_api!param?)'

 

Replication Server uses rs_marker during subscription materialization to pass the active subscription and validate subscription commands to the primary Replication Server. If subscription materialization is not being done, the empty string '' can be used instead.

 

The LTM for the primary database must recognize this function and pass the @rs_api parameter. The value for rs_api is a character string that contains the command being passed.

  1. Define the DB2 Error Class

Replication Server allows user-defined error processing for data server errors. This is accomplished by creating an error class for a database and specifying responses for each class that the data server returns when the error is encountered in the database. The data server returns the defined errors to Replication Server. You can define a single error class to use with all databases managed by the same type of data server. For example, you can use the default DB2 error class with any DB2 database. An error class is a name used to group error actions assignments. The default error action for all errors returned by a data server is "stop_replication". This action suspends replication for the database. To assign less severe actions to errors you want to handle differently, use the "assign action" command. See the Replication Server Administration Guide for more information.

Log into the Replication Server and create a DB2 Error Class using this command:

 

  • create error class db2_error_class
  1. Prepare the DB2 Database

You must create the RS_LASTCOMMIT System table in DB2. This table is used when replicating into DB2. When Replication Server connects to the DB2, Replication Server queries this table to determine where in the transaction stream to begin sending transactions. Replication Server requests information from this table to find the last transaction committed from each primary database. The table must be created with the user ID that is specified in the next section where the connection between Replication Server and DB2 is defined. This is the user ID that Replication Server will use when logging into DB2 for replication and updating of the RS_LASTCOMMIT table. It is referred to as the maintenance user ID in Replication Server documentation.

Log into DB2 and create the RS_LASTCOMMIT table using this command:

 

(ORIGIN INTEGER,

ORIGIN_QID CHAR(72),

SECONDARY_QID CHAR(72))

IN DATABASE.TABLESPACE

(THE DATABASE AND TABLESPACE MUST ALLREADY EXIST IN DB2)

.

  • CREATE UNIQUE INDEX LASTCOMMIT_IX on RS_LASTCOMMIT (ORIGIN_QID)

 

 

 

Now create the RS_SERVER_INFO and RS_MARKER tables using these commands:

 

 

  • CREATE TABLE RS_SERVER_INFO

(BINARY CHAR(6) NOT NULL,

ISO_1 CHAR(5) NOT NULL)

IN DATABASE.TABLESPACE

(THE DATABASE AND TABLESPACE MUST ALLREADY EXIST IN DB2)

 

  • insert into RS_SERVER_INFO

values('BINARY','ISO_1')

 

The RS_SERVER_INFO table is used by the rs_get_sortorder and rs_getcharset function strings.

 

  • CREATE TABLE RS_MARKER

(RS_API VARCHAR(255))

IN DATABASE.TABLESPACE

(THE DATABASE AND TABLESPACE MUST ALLREADY EXIST IN DB2)

 

RS_API contains information needed for subscription materialization.

 

  1. Creating and Configuring the DB2 Connection

To specify where data is replicated to, you create a connection from a Replication Server to the database. Replication servers distribute transactions received from primary databases through connections to the replicate databases they manage. A Replication Server may have connections to several databases, but each database can have only one connection to it. If your replication environment includes a Replication Server talking to another Replication Server, then a Route from one Replication Server to another is also necessary. See the Replication Server Administration Guide for more details on Routes. For the purpose of this document, there is only one Replication Server and it is replicating directly to the secondary DB2 target.

Log into the Replication Server and create the Connection from Replication Server to DB2 using the following commands (note: the thread is suspended at the time it is created and later resumed once the connection is configured properly):

 

  • create connection to osgtwls.db51

set error class db2_error_class

set function string class db2_function_class

set usernameDB2USERID

set password DB2PASSWD

with dsi_suspended

 

This command causes an error to be recorded in the Replication Server log and suspends the connection. Ignore the error as it occurs because the connection is not yet defined. The value you substitute for "db51" can be any name you wish. You should remember it because you will use it when creating subscriptions and in commands that specify a connection. "osgtwls" must also be a valid entry defined in the Replication Server interfaces file. "DB2USERID" and "DB2PASSWD" are the user ID and password that was used to create the RS_LASTCOMMIT table in DB2. It is referred to as the maintenance user ID in Replication Server documentation.

 

 

Configure the connection so that the data type representation in the SQL generated by the Replication Server is compatible with DB2. Issue the following command :

 

  • configure connection to osgtwls.db51 set dsi_sql_data_style to 'DB2'

 

Configure the statement separator character to semi-colon. This separator is used when Replication Server sends batch commands to DB2. Issue the following command:

 

 

Configure the batch option on. When batch is set to ?on?, Replication Server sends is able to

send multiple commands to the data server as a single batch.

 

configure connection to osgtwls.db51 set batch to ?on?

 

Now, resume the connection to the database with the following command:

 

  • resume connection to osgtwls.db51

 

 

  1. Create a replication definition and subscription for a primary table to be replicated

 

Replication Server lets you define the data and stored procedures you want to replicate at remote databases, as well as specify the destination databases themselves. In general, a source database contains primary data and may be called the "primary database", while a destination database contains replicate data and may be called the "replicate database". Transactions are replicated throughout the replication system from primary to replicate databases.

You create a replication definition to describe each replicated table. A replication definition lists a replicated table?s columns and data types, lists the columns that make up the primary key, specifies the location of the primary version of the table, and lists columns that can be used in subscribing to the primary data. You then create subscriptions based on this replication definition. The steps that follow are an EXAMPLE of how to create a replication definition and subscription. It is assumed that the table is already created in both the Primary and Replicate databases.

Log into the Replication Server and create the replication definition using the following command:

with primary at Dataserver.Database

with all tables named 'rstest'

(AU_ID char(11),

DEBIT decimal,

CREDIT decimal,

DTFIELD datetime)

primary key (AU_ID)

 

Where "rstest_defr" is the name of the replication definition you choose and Dataserver.Database is the database server and database name of the primary database being replicated (in this case, the connection name to DB2).

Once you have created the replication definition, you need to mark the table in the primary database for replication using the stored procedure sp_setreplicate.

 

  1. Defining function strings for the sample replication definition

The following function strings must be created using the commands listed below. Log into the Replication Server and create the following function strings using these commands:

 

  • create function string rstest_defr.rs_insert for db2_function_class

 

  • create function string rstest_defr.rs_update for db2_function_class

 

  • create function string rstest_defr.rs_delete for db2_function_class

Notice the output templates are omitted from the function strings, causing Replication Server to generate the default function strings.

After creating the function strings associated with the replication definition, you are ready to create the subscription.

  1. Setup sample subscription for Primary table

By default, creating a subscription causes Replication Server to copy the requested data from the primary database to the replicate database. How you create subscriptions depends on the materialization method you choose. Replication Server offers two basic methods for creating subscriptions. The two are automatic and Bulk. Refer to the Replication Server Administration guide for details on how to choose the type of materialization method to suite your needs. For this example, the bulk materialization method is used. The automatic materialization method may not be used when replicating from datsources other than SYBASE SQL Servers.

Defining a subscription adds the subscription to the Replication Server system tables, but it does not activate or materialize the subscription. Activating a subscription starts the distribution of updates from the primary to the replicate for the given subscription. Validating the subscription completes the bulk subscription materialization process and marks the subscription as valid in the system. Execute the following commands in Replication Server to define, activate and validate a subscription:

 

  • define subscription rstest_sub for rstest_defr with replicate at osgtwls.db51

 

  • Activate subscription rstest_sub for rstest_defr with replicate at osgtwls.db51

 

  • Validate subscription rstest_sub for rstest_defr with replicate at osgtwls.db51

 

Once a subscription is created and materialized, Replication Server begins distributing transactions for the primary data as they occur.

At this point, if no errors have been reported, the system is now ready to replicate the RSTEST table. To check that the subscription is valid in both the primary and secondary site, use the command:

 

  • check subscription rstest_sub for rstest_defr with replicate at osgtwls.db51

 

 



Back to Top
© Copyright 2010, Sybase Inc.