SharePlex 11.4 - 管理者ガイド

User defined conflict resolution routines for PostgreSQL Database as a Service to Oracle

To create conflict resolution routines, you write PL/SQL procedures that direct the action of SharePlex when a conflict occurs. Business rules vary widely from company to company, so it is impossible to create a standard set of conflict resolution rules and syntax that apply in every situation. You will probably need to write your own routines. It is good practice to write more than one procedure, such as making site or system priority the primary routine and timestamp a secondary routine. SharePlex invokes one routine after another until one succeeds or there are no more procedures available.

SharePlex provides the following tools that can be used as a basis for your routines:

• A generic PL/SQL interface that you can use to write basic routines based on DML operation types. 詳細については、How to write a routine using the SharePlex generic interfaceを参照してください。
• Packaged routines that perform basic conflict resolution based on a key or column value, which can be used as a backup measure in case the custom routines fail. 詳細については、User defined conflict resolution routines for PostgreSQL Database as a Service to Oracleを参照してください。

How to write a routine using the SharePlex generic interface

SharePlex provides a generic conflict resolution PL/SQL package that can be used to pass information to and from the procedural routines that you write.

Before you get started, understand that any table to be accessed through PL/SQL for conflict resolution requires implicitly granted privileges from the owner of the object to SharePlex.

Note: If you ran the SharePlex conflict resolution demonstration in the SharePlex Installation and Setup Guide, you can view a sample generic conflict resolution routine by viewing the od_employee_gen routine that was installed in the database used for the demonstration.

Procedure interface

For the procedure interface information of PostgreSQL, see Procedure interface for PostgreSQL.

List the routines in conflict_resolution.sid

After you create the conflict resolution procedure(s), construct the conflict resolution file. This file tells SharePlex which procedures to use for which objects and operation types, and in which order.

Where to find the conflict resolution file

A blank conflict_resolution.oraA, where oraA is a sid of OracleDB, was included in the data sub-directory of the SharePlex variable-data directory when SharePlex was installed. Use the file on the target system.

If this file does not exist, you can create one in ASCII format in an ASCII text editor. It must be named conflict_resolution.oraA, where oraA is a sid of OracleDB.

Important! There can be only one conflict_resolution.sid file per active configuration.

How to make entries in the conflict resolution file

Use the following template to link a procedure to one or more objects and operation types.

where:

• IUD is the type of operation that creates the conflict that is resolved with the specified procedure.
• schema.procedure is the schema and name of the conflict resolution procedure that will handle the specified object and operation type.

Syntax rules

• There must be at least one space between the object specification, the operation type specification, and the procedure specification.
• The order in which you list the procedures in the conflict resolution file determines their priority of use (in descending order). If you list a table-specific procedure, SharePlex uses it before procedures that are specified with a wildcarded object name.

Example conflict resolution file

How it works:

• The scott.sal_cr routine is used for the scott.sal table before the scott.sal_cr5 procedure is used for that table.
• For scott.cust, a procedure scott.cust_cr is called for IUD before the default routines are used for all operations.

How to specify SharePlex prepared routines in the conflict resolution file

To use the SharePlex prepared routines for all tables in the replication configuration, use the !DEFAULT parameter instead of specifying an schema and object name.

A custom routine takes priority over a SharePlex prepared routine. A prepared routine is used only if the custom routine fails. This is true regardless of the order in which the !DEFAULT-associated routine appears in the file.

The !DEFAULT parameter is case-sensitive. It must be typed in all capital letters.

How to change the conflict resolution file while replication is active

You can change the conflict resolution file any time during replication to add and remove tables and procedures. After you change the conflict resolution file, stop and re-start the Post process.