DBD: Data Replication System - Utilities

You start the Bulk Table Setup utility as follows:

GUI: DB Distributor Optional Modules / Data Replication / Utilities / Bulk Table Setup Utility

CHUI: D/R menu, option U3.

Using the Bulk Table Setup utility

1. Select files by clicking on the "Select Files" button.

2. Specify which of the following two options you will use:

   
   

   a. Setup selected files for replication?

   If this option is selected, enter the following additional data:

   
   

   – Target Code

   – Queue Code

   – Sequence

   
   

   b. Generate SQL for selected files?

   If this option is selected, select one of the following database types:

   
   

   – Oracle (Default)

   – MySQL

   – SqlServer

3. If at least one of the above options has been selected, the "Proceed" button is enabled for use. Click "Proceed" to continue. The selected files will be set up for replication, the proper SQL table creation syntax will be generated, and an output file will be created.

   
   

   You will be asked to provide a directory in which to save the file. The file "drsql.txt" will be saved in the specified directory (it will be overwritten if it already exists).

The SQL "CREATE TABLE" statements generated by the Bulk Table Setup utility can sometimes be wrong in the case where a data column value is case sensitive. For example, the table “IL1_ImageLibrary” has an attribute “IMAGE_KEY” which is case sensitive, but the SQL code generated by Bulk Table Setup does not mark that column as case sensitive. You must manually modify the SQL code before using it to create the table in a database.

In general, for external databases (Oracle, SqlServer and MySQL, and other ODBC-based databases) the assumption is:

1. Table names and column names are case insensitive. For example, the strings AR1_Cust and AR1_CUST are assumed to both refer to the AR1 table and are considered equivalent when they appear within SQL statements.
2. The data values within columns are case sensitive. For example, "Vendor" is not the same as "VENDOR" when they appear within SQL WHERE clauses.

It is a design decision by DemandBridge to not have database-specific clauses where they can be avoided within the SQL CREATE TABLE statements generated by the Bulk SQL Generator. This is done to minimize the complexity of generating SQL statements for different databases types. It is a complex job to keep track of the different data type terminologies used by the various database vendors.

You start the Target Analysis utility as follows:

GUI: DB Distributor Optional Modules / Data Replication / Utilities / Target Verification

CHUI: D/R menu, option U1

The utility can be used with database targets such as MySQL, Oracle and Microsoft SQLServer.

Click the magnifier to browse the list of Data Replication targets, then choose one:

After choosing the target, click Proceed to analyze all tables related to that target; data will be displayed in a list box (click “Clear” to erase current data in the list box so you can rerun again on a clear screen). If you wish to export the data to an Excel spreadsheet, just click the export icon in the lower left of the window (see the following screenshot):

The utility compares DB Distributor file information with the replicated table information and detects the following issues:

• Records present in the DB Distributor file but not in the replicated table data
• Records present in the replicated table data, but not in the DB Distributor file

For instance, in the above screenshot the AP4_Vend table shows 129 records in the DB Distributor file, but 132 records in the MySQL database file. Similarly, the table API_OpenInvoice shows 278 records in DB Distributor, but 279 records in MySQL.

The utility can help recover from Data Replication errors caused by, e.g., connectivity issues, which might result in the replicated table data getting out of synchronization with the DB Distributor file data.

A guaranteed method of bringing replicated database records back into sync with the DB Distributor file data is to do the following:

1. Run the "Target Analysis” utility to find out-of-sync tables & note the table names
2. Truncate the out-of-sync table
3. Run the "Initialize Replication” utility (see Chapter 7, above) for the table
4. Repeat Step 2 and Step 3 if more than one table is out-of-sync

Note: The Target Analysis utility can use an ODBC database connection, if available. The most recent versions of Providex/Pxplus do not require this.

You can start the Queue Analyzer utility as follows:

GUI: DB Distributor Optional Modules / Data Replication / Utilities / Queue Analysis

CHUI: D/R menu, option U4

Click “Analyze” to start the review; click “Clear” to erase the current display and allow you to rerun Analyze again on a clear screen.

The popup indicates that the analysis is in process; the final screen will eventually display three tabs that summarize different aspects of the current status of the Data Replication queue.

The Queues tab shows a count of outstanding (currently unreplicated) records waiting in the Data Replication queue, grouped by each of several known Queue Codes. Remember that each Queue Code represents a number of tables grouped together. Remember also that Queue Codes are independent, and that each Queue Code is processed by a separate data replication process (so replication for one Queue Code can be stopped, while replication continues for other codes).

Note in the following example that Data Replication is currently running with five different Queue Codes, representing a mixture of connections to several databases, including MySQL, Oracle and Microsoft SqlServer (for instance, the example shows there are currently 14726 unreplicated records grouped with Queue Code “TMA”):

The Tables tab displays a list of known tables in the Data Replication queue with their associated Queue Codes and record counts, ordered by Table name. Note in the following example that table AR1500 is being replicated to two different databases, since it is assigned to two different Queue Codes “AR” and “DBE”, therefore two separate data replication processes are running.

Note: Two different tables AR1500 and FM1500 are grouped with Queue Code “DBE”, therefore a single data replication process will replicate both tables to the same database destination.

The Times tab displays the total number of unreplicated records in the Data Replication queue at discrete instants in time. Time codes shown in the queue are encoded as:

YYddMM hh.mm

(e.g., two-digit year, two-digit month, and two-digit date followed by a space, then a two-digit hour and two-digit minute).

In the following example, note that there are 959 records in the queue on November 19, 2013, at 2:50PM [131119 14.50], but only 11 records left in the queue forty minutes later at 3:30 PM. [131119 15.30], and only 1 record left in the queue 10 minutes later at 3:40PM [131119 15.40]. This shows a normal progression in queue size over time as a batch of incoming records from Providex is processed and replicated to the various database connections.

You clear the replication flags by executing the utility as follows:

GUI: DB Distributor Optional Modules / Data Replication / Utilities / Clear Rep Flags in Data Dict.

CHUI: D/R menu, option U2