Universal Programming, Inc.

MCNET / INFREP - INFINITY Database Replication to remote SQL Server

Mcnet/Infrep is a package that performs data replication between a Modcomp computer with Logical Data Corporation�s Infinity Database Package and a computer with a database server such as Microsoft Corporation�s SQL Server that uses SQL (Structured Query Language) as the database access language. The Infinity operations that are supported are Insert, Update, and Delete.

The document presents an overview of the information that is required in order to accomplish the replication and an overview of some of the conditional processing that can be cone.

An example based upon the Mnemonic-Based Mcnet/Infrep Configuration Data is available. Also, a sample database design for configuration is available.

 

Features

The basic replication features that are provided include:

Data can be replicated to multiple database servers.

An Infinity field can be replicated to one or more SQL tables.

Values can be scaled.

Simple constraints such as limit checking can be performed.

Simple translations such as selected engineering unit conversions and simple table lookup can be performed.

Table selection can be based upon an Infinity field value or an Infinity record number

SQL statements for Insert, Update, and Delete operations are generated by the replication task.

The following features are available for more complex replication:

SQL statements for Insert, Update, and Delete operations can be generated by user-supplied SQL statements. This allows functions such as additional arithmetic, field concatenation, field parsing (splitting), and execution of stored procedures to be performed.

User-written functions are supported on systems such as Microsoft Corporation�s Windows NT that support dynamic linking.

The replication task is controlled by configuration information that describes where to get data, how to manipulate the data, and where to put the data. This configuration information can be supplied in data tables in the relational database or in text files containing mnemonic-based configuration data.

 

Configuration Information Categories

The primary categories of configuration information are:

Source (Modcomp) host definition,

Source (Infinity) database definition,

Source (Infinity) database field definition,

Target database server definition,

Target database definitions (if user does not supply SQL statements), and,

Target database table definitions (if user does not supply SQL statements).

These categories correspond to the primary data tables that would be in a database that describes the parameters for the replication tasks.

The following sections list a summary of the information that is required in each table.

 

Source (Modcomp) Host Definition

The host computer must be identified to the replication task and requires the following:

Host name,

Host IP address,

Connection socket (port), and,

Description (optional).

 

Source (Infinity) Database Definition

The source database definition provides information about the database and user-supplied SQL statements and includes:

Host identifier,

Database name,

Optional conditions for specifying target tables,

Permission to delete rows,

Named user-specified SQL statements (optional),

List of named user-specified SQL statements for Delete, Insert, and Update operations (required if user has supplied any SQL statements), and,

Description (optional).

 

Source (Infinity) Database Field Definition

The source database field definition provides the information that is required in order to convert the data into a form that is suitable for the SQL database. This information includes:

Database identifier,

Field name,

Field sequence number (for generating offset),

Data type,

Primary key indicator (optional if user supplies SQL statements),

Element length (how many bytes in one entry),

Element count (how many entries for the name),

Offset to value (if not automatically generated),

Constraint information (optional),

Scale factor (optional),

User-processing code (optional), and,

Description (optional).

 

Target Database Server Definition

The target database server definition includes:

Server name,

Server type (default is MSSQL for Microsoft SQL Server), and,

Description (optional).

 

Target Database Information

The target database information includes:

Server identifier,

Database name,

User name,

User password, and,

Description (optional).

Target Data Table Definition (Not Required If User Supplies SQL Statements)

Server and Database identifiers,

Table name, and,

Description (optional).

 

Target Data Table Column Definition (Not Required If User Supplies SQL Statements)

Table identifier,

Column identifier,

Column name,

Data type,

Data size (bytes),

Primary key indicator,and,

Description (optional).

 

User-Supplied SQL Statements

User-supplied SQL statements are the same as normal SQL statements, but where a value would normally be used, a placeholder is used. Also, each user-supplied SQL statement must have a name associated with. (The names must be unique within a single source database.) When the statement is executed, then the placeholder is replaced by the current (converted) value associated with the placeholder. The placeholders are usually source database field names, but can also be a built in name that the replication task recognizes. Any string that is enclosed within curly braces ("{}") is considered a placeholder. When the placeholder represents a field name, then it can also be subscripted and sub strings can be extracted. The subscript notation is enclosed in parenthesis as (index, start, count), where index is element (entry) number, start is the first byte number to access (the default is one), and count is the number of bytes to get (the default is the remainder of the field). Also, built in placeholder names start with an underscore.

Example placeholders are:

Placeholder

Description

{FON}

Use entire contents of field name FON

{I2DATA(3)}

Use entire contents of third element in field name I2DATA

{A3DATA(4, 2, 6)}

Use 6 bytes starting with the 2nd byte from the fourth element in the field named A3DATA

{_SYSDATE}

Use current date and time

 

Some simple user-supplied statements might be:

Name

Statement

  

Master_insert

INSERT

INTO RAW_CAS_MASTERELECTRODE

(FACTORY_ORDER_NUMBER, BASE_OF_SOCKET, PROCESSID_CODE, CUSTOMER_DATA, SIZE_CODE, SALVAGE_SIZE_CODE)
 

VALUES

({FACTORYORDERNUMBER}, {BASEOFSOCKET}, {PROCESSIDCODE}, {CUSTOMERDATA}, {INTGR2DATA(1)}, {INTGR2DATA(2)});

Master_delete

DELETE

FROM RAW_CAS_MASTERELECTRODE

WHERE FACTORY_ORDER_NUMBER =

{FACTORYORDERNUMBER};

Mmf_insert

INSERT

INTO RAW_CAS_MASTERMMF

(FACTORY_ORDER_NUMBER, PROCESSID, YEAR, RUN_NUMBER, EXTRUSION_TIME, FORMING_STATUS, LOCATION_IN_RUN)
 

VALUES

({FACTORYORDERNUMBER}, {MMFYEARRUNNUMBER{1,1,2}, {MMFYEARRUNNUMBER{1,3,2}, {MMFYEARRUNNUMBER{1,5}, {INTGR2DATA(1)}, {INTGR2DATA(2), {INTGR2DATA(3)});

Mmf_delete

DELETE

FROM RAW_CAS_MASTERMMF

WHERE FACTORY_ORDER_NUMBER =

{FACTORYORDERNUMBER};

 

In order for the replication task to execute the preceding statements, the user would need to supply a list of statement names that need to be executed for the database. An example of this list might be:

Operation

Named SQL Statements To Execute

Insert new row

Master_insert, Mmf_insert

Update existing row

Master_delete, Master_insert, Mmf_delete, Mmf_insert

 

 

Conditional Statement Execution

Conditional SQL statements can be executed by the value of a field or an Infinity database record number.

The following table shows the information that would be required in order used to split a database into multiple data tables. (All conditions are not shown for this table.)

 

Field Name

Value

Named Statements For Insertion

Named Statements For Update

PROCESS

MF

Forming_insert

Forming_delete, Forming_insert

PROCESS

B1

Bake1_insert

Bake1_delete, Bake1_insert

 

If an Infinity database has different records layouts depending upon the record number, then multiple source database field definitions can be used to define the record contents and required processing. The additional database field definitions can be associated with a dummy source database when the primary database is defined. (The sample configuration file shows an example of this condition.)

For more information,
Contact Universal Programming today:
mailto:info@uniprog.com