DBD: Data Replication System - Start the Data Replication Server

1: The Data Replication server connection

To replicate data, DB Distributor processes need access to a data source, typically an Oracle, MySQL or SqlServer database server. The latest versions of DB Distributor now allow data replication processes to natively connect to all such data sources, though earlier versions did not. With the latest version you may launch a Data Replication server process directly from within DB Distributor.

Note: The earlier versions did not support native connectivity to ODBC data sources, and did not properly supply the parameters required for queue-based processing. The previous way to resolve both issues was to establish a long-running proxy process to translate data between the two endpoints; that process was hosted via a Providex/WindX/telnet session on a separate Windows server.

With the latest version of DB Distributor, data replication to ODBC databases targets like Oracle, MySQL or SqlServer no longer require the use of a Providex/WindX plugin session running on Windows workstation. With the evolution of Pvxplus as the driving force behind the Providex software platform there are now at least two WindX Plugin flavors commonly available for use:

PxPlus WindX Plugin (From PVX Plus Technologies)

For the Data Replication server process to function properly and maintain an ODBC database connection it must be executed as a background process on the DB Distributor server.

The ideal and recommended approach (if currently supported by your DB Distributor server) is to use the built-in PxPlus support. It allows you to start and run Data Replication directly through the DB Distributor administration screens, without involving a secondary Windows machine as a proxy service.

If you use the Providex WindX Plugin, you must install and use a particular version of the plugin: Providex Version 7.00.0040 -- Build date Feb 6 2006@15:33:58. DemandBridge currently distributes Providex/WindX in two different versions: V7.00 and V7.10. You must be sure to install WindX v7.00 on the Windows workstation to use with your ODBC connection.

Other versions of the Providex plugin have buggy SQL implementations or other ODBC issues that cause data integrity issues during data replication. Those versions may run and at first appear healthy but will later exhibit subtle problems with the way certain SQL date transactions are handled, or they may hang, or may not run at all. See DemandBridge Enhancement document #04584 for further information.

The correct and recommended version of the Providex/WindX Plugin software can be downloaded from the URL below (at the time of this writing, June 06, 2014): http://www.pvxplus.com/downloads/pvx/WindX/zplugin/7.xx/

Select the version 7.00 below from the available list of WindX plugin installers which will be found at the above site:

WindX-plugin-7.00.0040-windows-32bit-x86.exe

2: Starting the Data Replication server process

To start the proxy connection, from our Windows workstation we first start a Providex/WindX/telnet session to connect to the DB Distributor server. After logging into the server, we execute a startup script (e.g. typically a shell script like /usr/lib/pvx/rundatarep) to initiate a Data Replication server process.

To summarize, follow these steps to establish and start a Data Replication server:

Create and test the ODBC database connection on the Windows machine.
Before continuing, test the connection to prove it works.
Start a Providex/WindX v7.00/telnet session to your DB Distributor server.
Within the telnet session, log into the server and execute the startup script.

Note: While the proxy connection runs, each replication transaction will echo to the telnet console within the WindX session and provides a handy way to monitor the Data Replication server as it runs.

The running WindX session will look something like this:

3: Starting multiple Data Replication server processes

It is possible (and common) to start more than one Data Replication server process for the same queue unless the DB/D table being replicated is “ICF” (see below).

There are advantages to running multiple server processes for a queue:

Improves performance
Each queue server automatically numbers itself
Each queue server creates a separate log file

To start a second server process, just start another Providex/WindX/telnet session from your Windows workstation to your DB Distributor server and run the startup script again.

Table “ICF” is a special case due to the way record activity within DB/D is handled. A majority of ICF replication activity happens because of lots being split and committed and deleted during order entry and invoice updates. The splitting and commit transaction are typically a deletion (REMOVE) and insertion (WRITE) in the Providex data file.

There are at least two data replication events for every ICF record being used during order entry. If there are multiple data replication processes acting on the ICF traffic the order in which the remove and insert get pushed to the MySQL side is not predictable and in the same order that it happened in DB/d. This leads to data integrity issues.

The workaround is simple: either move ICF off into its own queue or only have one data replication process running always.

4: Common startup issues

A common issue caused by the use of the incorrect Pxplus/WindX plugin is based on the way dates are parsed. The primary key data for the "WHERE" clause can sometimes be parsed incorrectly when a key segment is a "datetime" data type. An example is the DB Distributor file “ICF” which has the RECEIPT_DATE column as part of its primary key.

An example of an incorrect SQL statement that could be issued when using the Pxplus/WindX plugin is as shown below:

WHERE CUST_DIV = '01' AND CUST_CODE = '99999000' AND ITEM_CODE = 'SSPENRED' AND LOC_CODE = '9001' AND RECEIPT_DATE = '2010-10-0601' AND SEQ_NUM IS NULL

Notice the RECEIPT_DATE specification above. As a result of this type of execution the target database will not be able to address the correct row in the table for an UPDATE or DELETE, and so the database tables will slowly drift out of synchronization with the DB Distributor files.

The Providex/WindX Plugin 7.00 issues this same statement correctly as shown below:

WHERE CUST_DIV = '01' AND CUST_CODE = '99999000' AND ITEM_CODE = 'SSPENRED' AND LOC_CODE = '9001' AND RECEIPT_DATE = {d'2010-10-06'} AND SEQ_NUM = '01'

See the discussion of the Table Analysis utility in the following section. That utility can be used to help re-synchronize your database with DB Distributor files if they lose synchronization for any reason.