StarQuest Technical Documents

Using SQDR for Limited Incremental Replication from Platforms Not Supported by SQDR Plus (Advanced)

Last Update: 3 March 2020
Product: StarQuest Data Replicator
Version: 3.30 or later
Article ID: SQV00DR018

Abstract

This document describes a method for using StarQuest Data Replicator (SQDR) to simulate real-time replication from database systems not currently supported by SQDR Plus to any database management system that SQDR supports.

This document differs from "Using SQDR for Limited Incremental Replication from Platforms Not Supported by SQDR Plus " in that the tracking table is created on the destination database instead of on the source database. This method allows the final update of the tracking table to occur in the same unit of work as the replication to ensure that the process either succeeds or fails as a whole unit. 

For best results, use SQDR Plus when performing incremental replication from supported systems (DB2 for i, DB2 for Linux, UNIX, and Windows, Microsoft SQL Server, Oracle, Informix, or MySQL/MariaDB/Aurora). To download a free trial of SQDR Plus, go to http://www.starquest.com.

Note that the procedure below demonstrates using SQL Server as the source database. SQL Server is now fully supported as a source by SQDR Plus, so this method of incremental replication is no longer recommended. However, the techniques illustrated here can be used with other database systems that are not yet supported by SQDR Plus. For instance, the Oracle Database Gateway for ODBC can be used in place of a SQL Server linked server.

Solution

This document is intended for users who are familiar with the techniques of creating and altering tables on the source and destination DBMS systems, and creating and using SQL Server Linked Servers. In addition, this solution is designed to work only with source tables in which new records are inserted, but no records are updated or deleted.

All of the SQL Server SQL statements included in the instructions below can be executed in the New Query window of SQL Server Management Studio.

In this document, the sample SQL statements to be executed on the destination host use DB2 syntax. If your destination database is not DB2, you may need to modify the SQL statements to support your DBMS.

General Considerations

 

Summary

Following are the general steps that you need to perform. Each step is described in more detail in the Detailed Procedures section.

1. Review ODBC data source configuration.
2. Add a timestamp column to the SQL Server source table. The timestamp value will serve as the row identification value and will be used in the SQDR subscription criteria to limit the replication result set so that only new records are replicated.
3. Create a tracking table on the destination DBMS to store the row identification value of the last record replicated to the destination table and the last record inserted into the source table at the time of replication.
4. Create a SQL Server linked server on the source SQL Server database using an ODBC System DSN. This linked server will be used in the SQDR subscription criteria to access the tracking table on the remote database.
5. Modify the SQDR service properties to allow the source processing to occur even if the replication fails.
6. Create the SQDR subscription with BEFORE and AFTER processing, and with a criteria that limits the result set to be replicated.

Detailed Procedures

1. Review ODBC data source configuration for destination.

   If the destination database is on an iSeries server and you are using StarSQL, verify that the ODBC data source is configured with the Advanced option BinaryCCSID set to a value of 0. Depending on the type of database and ODBC driver being used, a similar configuration setting may be necessary when using an ODBC driver other than StarSQL.

2. Add a timestamp column to the SQL Server source table and create an index.

   1. The SQL Server source table to be replicated must have a timestamp field. If one does not exist, add a non-nullable timestamp column to the table. The example below adds a non-nullable column called tscol to a source table called stable.

   alter table dbo.stable add tscol timestamp not null

   NOTE: Do not confuse the timestamp data type with the datetime data type. The timestamp data type is an auto-generated binary value used for "row-versioning." It does not actually preserve the date or time and thus is better suited than the datetime data type for this specific application because it is guaranteed to be a unique value regardless of system date/time changes on the SQL Server system.

   2. For best performance, create an index over the newly added timestamp column. For example:

   create index ix_tscol on dbo.stable (tscol)

2. Create a tracking table on the destination DBMS to store row identification values.

Create a tracking table in the destination database that is subject to journaling (i.e., changes to this table are tracked in a log file). For example, on DB2 for iSeries this table should be created in a collection. This table should be created with three columns: one as a varchar (128), called SUBNAME and two as binary (8) types, called CURMAX and LASTMAX. The example uses the table name SUBTRACK.

CREATE TABLE SUBTRACK
(SUBNAME VARCHAR (128) NOT NULL,
CURMAX CHAR (8) FOR BIT DATA WITH DEFAULT,
LASTMAX CHAR (8) FOR BIT DATA WITH DEFAULT,
PRIMARY KEY (SUBNAME))

The SUBNAME column will store the name of the SQDR subscription. The CURMAX column will store the maximum timestamp of the rows in the source table to be replicated to the destination host at the start of the replication. The LASTMAX column will hold the timestamp value of the last row successfully replicated to the destination host.

Create a unique index so that this table can be updated by the SQL Server linked server.

CREATE UNIQUE INDEX SUBTRACKIX ON SUBTRACK (SUBNAME)

The row must be first initialized with the subscription name and afterwards the timestamp values will be maintained by the SQDR subscription process. This example assumes that the subscription name will be STABLE (which is also the name of the source table in this example).

INSERT INTO SUBTRACK (SUBNAME) VALUES ('STABLE')

Create a SQL Server linked server on the Source database using an ODBC System DSN.

The linked server must be created on the source SQL Server database, which may differ from the SQL Server platform on which SQDR is installed. The linked server can use any ODBC System DSN configured to connect to the destination database. If your destination database is DB2, we recommend the use of StarSQL. A StarSQL data source connecting to an iSeries host system should be configured with the Advanced option BinaryCCSID set to a value of 0. Depending on the type of database and ODBC driver being used, a similar configuration setting may be necessary when using an ODBC driver other than StarSQL.

Launch SQL Server Management Studio.

Under the Server Objects folder, right click on Linked Servers and select New Linked Server.

Enter in a name for the Linked Server and choose "Other data source". Do not use any spaces or special characters in the Linked Server name. Record this name for use later in step 5.

Select "Microsoft OLE DB Provider for ODBC Drivers" (MSDASQL) as the Provider Name. For non-StarSQL users, consult the SQL Server documentation or the documentation for your driver if you are unsure of the linked server property values.

In the "Data source" field, enter in the name of the ODBC System data source you want to use. Note that you must enter the Data Source name exactly as it is shown in the ODBC Data Source Administrator.

Select the Security page and select "Be made using this security context". Enter a valid remote user/login (user ID) and password for the destination database host.

For a StarSQL data source, leave the remaining fields blank. If you are using another ODBC driver, refer to the documentation for your driver.

Modify the SQDR service properties.

In the Replication Manager, select the name of the computer that represents the service in the left pane, right-click, and select Properties.

Under the Global tab, uncheck the box "Use single transaction for source processing" and click OK.

Stop and restart the service by right-clicking the service in the left pane and selecting Stop Service. Restart the service by right-clicking the service and selecting Start Service.

Create the SQDR subscription.

In the StarQuest Data Replication Manager, right-click the SQL Server source you want to use and select Insert Subscription.

On the Source pane of the Subscription Wizard, select the source table to replicate and click Next. (Click Refresh to display the available source objects.)

On the Destination pane, check the radio button "Append replicated rows to existing data". If replicating to an existing table, be sure that the destination table is subject to journaling (i.e., changes to the table are tracked in a log file). If you allow SQDR to create the destination table when the subscription is saved, be sure to specify the schema (e.g., collection) that ensures that the table changes are journaled/tracked.

On the Processing pane:

Enter the following SQL in the SOURCE processing field to be executed BEFORE the replication, modifying the [linkedserver], [catalog] and [schema] to use the actual linked server name, catalog, and table schema, and changing [database] to the actual name of the source database.

UPDATE [linkedserver].[catalog].[schema].SUBTRACK SET CURMAX = (select convert (binary(8), max(tscol)) from [database].dbo.stable) WHERE SUBNAME = 'STABLE'

Enter the following SQL in the DESTINATION processing field to be executed AFTER the replication:

UPDATE [schema].SUBTRACK SET LASTMAX=CURMAX WHERE SUBNAME='STABLE'

On the Schedule pane, set the subscription to run on a regular interval. Be sure to allow enough time for the replication and the processing to complete before the subscription attempts to run again.

Name the subscription with the same name as used for the SUBNAME value that was inserted into the SUBTRACK table. In this example, the subscription name is STABLE.

After the subscription has been created, right-click the subscription and select Run Subscription. This will replicate the complete source table to the destination table and initialize the timestamp values in the SUBTRACK table.

After the subscription has successfully completed, double-click on the subscription to open the properties. On the Destination panel, select the Use Existing Table option.

On the Criteria pane, enter the following SQL clause changing [linkedserver] to the name of the linked server created in the previous step, [catalog] to the name of the database catalog, and [schema] to the name of the table schema on the destination host:

(tscol > convert (TIMESTAMP,
(SELECT LASTMAX FROM [linkedserver].[catalog].[schema].SUBTRACK WHERE SUBNAME='STABLE')))
and (tscol <= convert (TIMESTAMP,
(SELECT CURMAX FROM [linkedserver].[catalog].[schema].SUBTRACK WHERE SUBNAME='STABLE')))

Click the Verify button to verify that the SQL statement does not contain any syntax errors. It is normal at this stage for the result set returned using this criteria to be zero.

Click OK in the subscription to save the changes.

The next time the subscription runs, only the newly inserted records will be replicated to the destination table.

Applying This Solution to Other Tables

To apply this solution to another source table in the SQL Server database, complete only Steps 1, 2C, and 5. 

Source Table Management

In some circumstances you may find it necessary to delete all of the records in the source table once they have been successfully replicated to the destination database. This can easily be accomplished without impacting the destination database. Using the same table and column names as in the previous examples, the following SQL statement deletes all of the records from the source table that have already been replicated to the destination table. Execute this SQL statement, changing [linkedserver] to the name of the linked server created previously, [catalog] to the name of the database catalog, and [schema] to the name of the table schema on the destination host:

delete from [schema].stable where tscol <=
(SELECT LASTMAX FROM [linkedserver].[catalog].[schema].SUBTRACK)