Kbase 16956: Do we support Replication between Progress and ORACLE?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
Do we support Replication between Progress and ORACLE?
Overview of Possible replication schemes between a
PROGRESS and ORACLE database.
--------------------------------------------------------------------------------
Background:
===========
PROGRESS has introduced a replication toolkit in Version 8.1 that will
allow database administrators to setup a replication scheme between 2
or more PROGRESS databases. Whilst not supported for DataServers
in version 8 (8.1, 8.2 etc), many ORACLE DataServer users have asked
if it is possible to implement replication between a Progress and
an ORACLE database. Provided below are some guidelines that may help
in implementing this type of replication; however, this is not
supported by Progress Technical Support. If you use the following
suggestions you will be changing the original code that is provided
in the Toolkit. Any changes to the toolkit are not supported by
Progress Technical Support. If you have any trouble implementing
this scheme or any questions, you need to contact Progress
Consulting for fee-based assistance.
Types of Replication
====================
Essentially, there are two models that can be used to achieve
replication of information between databases:
Synchronous Replication
-----------------------
In a synchronous replication model all replication of data occurs
within the scope of the original transaction. This model is
suitable where real-time availability of replicated data is
required. The typical implementation of this model is a two-phase
commit scheme which insures completion of all transactions
prior to committal of data. Synchronous replication insures high
availability and consistency with side effects of degradation of
transactional throughput and loss of availability. The loss of
availability is due to the necessity of completing the two phase
commits. If target systems for the two-phase commit is unavailable
at the occurrence of a transaction then the completion of the
transaction is impossible.
Asynchronous Replication
------------------------
Asynchronous replication (often referred to as store and forward
replication) allows the replication to take place outside the
scope of the original transaction. The replication may take place
seconds, minutes, hours or even days from the time of the
transaction. The time interval between replications is based
solely on the requirements of each system. Although the replication
occurs outside the transaction, replication can occur by transaction.
For example, if an order with many order lines is placed in the system
and these changes are made within the scope of a single transaction,
the replication engine will insure that these changes are replicated
as a single transaction. If failure occurs during replication the
replication transaction is rolled back as the original transaction
would have been, therefore insuring database consistency and integrity.
Implementation of the Replication Models
========================================
Synchronous Replication
-----------------------
As stated above, synchronous replication is usually handled by a
two-phase commit protocol. The PROGRESS DataServer for ORACLE will
allow two-phase commit when no more than one ORACLE database is
involved in the transaction (any number of PROGRESS databases
may take part). If a transaction must be distributed between
more than one ORACLE database, then synchronous replication is
not possible. Replicated records can be written from a PROGRESS
application to a number of ORACLE databases, however these
records will be written within their own transaction scope,
essentially giving rise to asynchronous replication.
Asynchronous Replication
------------------------
The store-and-forward method of replication is possible between
PROGRESS and ORACLE. This would require that all modified records
in a PROGRESS database be written to a replication change table.
Periodically, (and this could be a period as small as one second),
a PROGRESS batch process wakes up and checks this table. If any
records are found, (indicating replication needs to occur), it
will copy all records in this replication change table to the
ORACLE database. With the 4GL and database extensions provided in
PROGRESS V8.1 to support replication, this batch process would
ensure that all records modified in the one transaction on the
PROGRESS database, be written to the ORACLE database as one
transaction, or all records are rolled back as a single unit.
In the V8.1 release, PROGRESS provides sample code to perform this
sort of replication. As this code is purely PROGRESS 4GL, changes
can be easily made to accommodate specific business requirements.
Sample Replication Code (PROGRESS V8.1)
---------------------------------------
The sample replication code provided with V8.1 of PROGRESS allows
for replication between two PROGRESS databases. With some minor
modifications, this code can be made to run in a one-way
replication scenario from a PROGRESS database to an ORACLE
database. Two-way (PROGRESS to ORACLE and ORACLE to PROGRESS)
can be achieved with some more complex modifications. The changes
that need to be made (for both one-way and two-way replication)
include:
DBTASKID Function
-----------------
DBTASKID is a new function in V8.1 that assigns a unique number
to a transaction. This is used for recording the transactions
in which record modifications were made. DBTASKID currently
uses the PROGRESS database to retrieve a sequence number for
this transaction. The current implementation does not work
with DataServers, hence, we are not able to track transactions in
ORACLE in this automatic fashion. Whilst this is perfectly
acceptable for one-way replication (PROGRESS to ORACLE), it
becomes a problem when two-way replication is required in PROGRESS
V8.1. There are plans to make this function available across
DataServers in subsequent releases of PROGRESS V8. If two-way
replication with ORACLE is required for V8.1, the application
source code can be modified to assign a sequence number to a
variable at the beginning of each transaction. This variable
can then be used instead of the DBTASKID function.
Removal of Collision Checks
---------------------------
Currently, before copying records from the source replication
change table to the target database, the sample code checks the
target database's replication change table to see if that same
record has been modified in the target database. Because of
the problem in assigning transaction numbers in an ORACLE
environment (see DBTASKID above), unless the source code is
modified, we will not be able to track transactions in the
ORACLE database. Unless we have this transaction number
(either through DBTASKID or sequence numbers), we cannot
supply all required information to the target database's
replication change table. Therefore, in a one-way replication
environment, we should remove all logic checking for collisions
(as we are assuming there will be none). If two-way replication
is being implemented, these collision checks can remain.
RAW-TRANSFER Function
---------------------
PROGRESS V8.1 provides the new RAW-TRANSFER statement. This
statement allows the 4GL programmer to copy the contents of an
entire Progress database record buffer into a field (or variable)
whose datatype is RAW (and vice versa). It also allows the
copying of one database buffer to another record buffer. The
PROGRESS V8.1 DataServer does not yet support this RAW-TRANSFER
statement. However, equivalent functionality can be achieved by
coding the following:
1. For all replication triggers in the required table, create a
temporary table to hold the record to be replicated:
DEFINE NEW GLOBAL SHARED TEMP-TABLE t##tablename LIKE tablename.
Where tablename is the name of the table to be replicated.
t##tablename is not a fixed naming convention - any name can be used.
2. Replace all instances of:
RAW-TRANSFER replication-chng.record TO tablename
with:
RAW-TRANSFER replication-chng.record TO t##tablename.
BUFFER-COPY t##tablename TO tablename.
VALIDATE tablename.
DELETE t##tablename.
This will copy a RAW field to a record in an ORACLE table.
This functionality is used in the sample code for both one-way
and two-way replication.
3. Replace all instances of:
RAW-TRANSFER tablename TO replication-chng.record
with:
BUFFER-COPY tablename TO t##tablename.
RAW-TRANSFER t##tablename TO replication-chng.record.
DELETE t##tablename.
This will copy a record in an ORACLE table to a RAW field. This
functionality is used in the sample code for two-way replication.
By making these modifications to the sample code provided in the
V8.1 Replication Toolkit, developers will be able to ensure changes
made to one database can be replicated to another when one (or more)
of these databases is an ORACLE one.
Progress Software Technical Support Note # 16956