DBD: Data Replication System - Set up Data Replication Targets

1: How to define a Target for Data Replication

When you define a Data Replication target, you are defining the basic parameters that describe how one or more DB Distributor tables will be duplicated to one or more remote databases. Each target includes specific data on how to connect to its associated database.

For instance, you may have a target named “MySQL data” that describes how DB Distributor data is connected to a MySQL database; or a target named “Microsoft Access Information” describing how DB Distributor data is connected to a Microsoft Access database.

Note: Before creating a Data Replication target, you must determine and verify the database connection parameters (e.g. username, password and database schema name) needed to connect to the database. It is common to find simple syntax problems or typographic errors in the database connection parameters that are specified when setting up a Target. Review your database connection parameters very carefully.

You can set up a Data Replication target by running the Target Setup utility, as follows:

GUI: DB Distributor Optional Modules > Data Replication > Setup > Target

CHUI: D/R menu, option S1

Target Code

This field shows a short descriptive name for a Target. The query button will show existing Target Codes that are already defined:

Description

This field contains a short description to help you identify what the Target is.

Model

This field is probably the most important one in the Target and is prone to having simple syntax problems and typographic errors. Review these database connection parameters very carefully when you define this field.

The field defines the specialized syntax describing how to connect to a particular database. Each type of target database requires specific connection syntax, and that syntax differs greatly between Oracle, MySQL and Microsoft SqlServer. For example, Oracle connections require that a username and password be passed within the values of "USER=" and "PSWD=", but MySQL connections do not.

For these Oracle database connections, the parameters “USER=” and “PSWD=” are based on what your system administrator has defined for that database connection.

The following examples show the syntax needed for specific database types:

Oracle Syntax:
[oci]some_oracle_sid;table_name;USER=someuser;PSWD=somepasswd

Where:
"oci" specifies that you connect to an Oracle database using the Oracle Call Interface
"some_oracle_sid" is the Oracle system ID (SID)
“someuser" is the user name
"somepasswd" is the password

An actual example:

MySQL example:
[wdx][odb]dbreports;&DD;

Where:
“wdx” indicates the server will connect over a WindX proxy connection
"odb" specifies that the connect uses the ODBC interface
"dbreports" is the database name
Microsoft SqlServer example:
[wdx][odb] dbreports;&DD;USER=someuser;PSWD=somepasswd

Where:
“wdx” indicates the server will connect over a WindX proxy connection
"odb" specifies that the connection uses the ODBC interface
"dbreports " is the database name
"someuser" is the user name
"somepasswd” is the password

I/O Program

This field contains the name of the DB Distributor I/O procedure to use for the database connection. It is possible to use different embedded I/O Procedures with some tables to accommodate special needs. The Data Replication module will handle embedding of the procedure into the data dictionary for that table. Currently, DB Distributor supports using only the embedded I/O procedure DR2EDA to support connections to PVX (Providex) files.

A conceptual diagram of the Data Replication architecture used by DR2EDA follows:

Characteristics of the I/O procedure DR2EDA include:

Updates are written to the Replication Queue.
The Replication Queue is a Providex table on the same machine.
Very little chance for error during normal DB Distributor processing.
If the connection to the other database is unavailable, updates will queue up. When the database connection is available again, they are applied in the same order as they were received.
Uses fewer resources per operator than writing directly to the database.
Uses fewer connections to the other database, reducing the potential number of licenses needed for that database.
Does not require each operator’s computer to have a database connection.
Will require running one or more background Data Replication server processes on the DB Distributor server.
Someone must periodically review the server to ensure that it is running and that data continues to replicate.

Database Type

This pulldown allows selecting the type of database that you are replicating to:

If using Oracle, select “Oracle”
If using MySQL, select “MySQL”
If using SqlServer or any other type database, select “Other”

Filters Enabled?

Select Yes to select. This checkbox allows using special filters for all data that will be sent to the database. It allows you to filter the data according to whether or not it contains specific customer codes. It could, therefore, allow you to replicate data to DB Enterprise only for the specific customers that use DB Enterprise to place orders.

Note: If you intend to use Filters, then BEFORE you enable this checkbox you must define the filter using the Apply Filters utility. Refer to the chapter Set Up Data Replication Filters for more details.