To create conflict resolution routines, you write PL/PGSQL 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/PGSQL interface that you can use to write basic routines based on DML operation types. For more information, see How to write a routine using the SharePlex generic interface.
• The customized 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. For more information, see User defined conflict resolution routines for PostgreSQL Database as a Service to PostgreSQL Database as a Service.

Important! This documentation provides guidelines, examples and templates to assist you, but do not use them as your own routines. Test your conflict resolution routines before you put them into production to make sure they work as intended, and to make sure that one routine does not counteract another one. By default, SharePlex does not stop for out-of-sync conditions. If failed attempts at conflict resolution are not resolved, the databases can become more and more out of synchronization. Check the Event Log frequently to monitor for out-of-sync warnings by using the show log command in sp_ctrl. See the SharePlex Reference Guide for more information about show log and other SharePlex commands. Updates are occasionally made to the conflict resolution logic, so refer to the Release Notes and documentation for your version of SharePlex for any additional information that augments or supersedes these instructions.

How to write a routine using the SharePlex generic interface

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

Before you get started, understand the following guidelines:

• Conflict resolution cannot be used for DDL changes.
• Any table to be accessed through PL/PGSQL procedure for conflict resolution requires implicitly granted privileges from the schema of the object to SharePlex.

Procedure interface

Follow this template to create your procedure.

(table_info sp_cr.row_typ, col_values sp_cr.col_def_typ[], INOUT status INTEGER, INOUT action INTEGER, INOUT reporting INTEGER)

where:

• sp_cr is the name of the schema that contains the PL/PGSQL record and table structures.
• row_typ is the name of the PL/PGSQL record that passes in variables (see Schema definition).
• col_def_typ is the name of the PL/PGSQL table that stores column information (see col_def_typ type).

Schema definition

SharePlex defines PL/PGSQL record and table structures in a schema named sp_cr in the SharePlex database.

The schema uses the following parameters:

CREATE SCHEMA IF NOT EXISTS sp_cr;

CREATE TYPE sp_cr.row_typ AS

(src_host VARCHAR(32),

src_db VARCHAR(32),

src_time VARCHAR(20),

statement_type VARCHAR(6),

source_table VARCHAR(128),

target_table VARCHAR(128),

native_error INTEGER,

sql_state VARCHAR(10)

);

IN Variables

For each row operation that causes a conflict, SharePlex passes this metadata information to your procedure.

Variable	Description
src_host	The name of the source system (where the operation occurred). It is case-sensitive and is passed using the same case as on the source system, for example SysA. If there are named post queues in use on the target system, this variable consists of the name of the post queue, for example postq1. Note: Maximum length is 32 characters. A host name longer than 32 will be truncated to 32 characters.
src_db	The database name of source.
src_time	The timestamp of the change record when it is received by the Capture process.
statement_type	A letter, either I, U or D, indicating whether the operation is an INSERT, UPDATE or DELETE statement.
source_table	The owner and name of the source table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database.
target_table	The owner and name of the target table, expressed as owner.table. This value is case-sensitive and matches the way the table is named in the database.
native_error	This field is generate by ODBC API for conflicting DML.*
sql_state	This field is generate by ODBC API for conflicting DML.*

Note: *In case of Update and Delete operation, if row not found, then native error is set to 100 and SQL_state is set to '00000'.

OUT Variables

These variables direct the action of SharePlex based on whether the procedure succeeded or failed).

Variable	Description
status	Defines whether or not the procedure succeeded. You must specify a value for this parameter. A value of 0 implies successful execution. It acts differently, depending on whether the procedure is used for conflict resolution. Conflict resolution: A value of 0 directs SharePlex to proceed with the SQL statement. SharePlex does not write any log entries to the Event Log when conflict resolution succeeds. A value of 1 implies that the procedure was unsuccessful. In this case, the action SharePlex takes depends on what you specified as the action variable.
action	Defines the action that you want SharePlex to take. This is different, depending on whether the procedure is used for conflict resolution. Conflict resolution: Specifies the action to take as a result of an unsuccessful conflict resolution procedure. You must specify a value for this parameter. A value of 0 directs SharePlex NOT to post the SQL statement. The outcome of this action depends on what you specify for the reporting variable. In addition, it directs SharePlex to try the next conflict resolution procedure that you listed in the conflict resolution file, if one exists. The value of 1 is reserved for internal SharePlex use. Do not use it. A value of 2 directs SharePlex to try the next conflict resolution procedure that you listed in the conflict resolution file, if one exists.
reporting	Determines how SharePlex reports unsuccessful procedural results. You must specify a value for this parameter. A value of 0 directs SharePlex NOT to report an error or write the failed SQL statement to the database_errlog.sql log. Values 1 and 2 are reserved for internal SharePlex use. Do not use them. A value of 3 directs SharePlex to write the failed SQL statement to the database_errlog.sql log and report an error to the Event Log.

col_def_typ type

SharePlex creates a col_def_typ type for each replicated operation. This type stores column information.

• Conflict resolution: For each row operation that causes a conflict, SharePlex writes column information to col_def_typ.

All fields are passed by SharePlex to your routine, although not all will have values if SharePlex cannot locate the row.

Following is the data type that is used to populate the col_def_typ table.

type col_def_typ is record

(column_name user_tab_columns.column_name%type

CREATE TYPE sp_cr.col_def_typ AS

(column_name VARCHAR,

datatype VARCHAR,

is_key BOOLEAN,

is_changed BOOLEAN,

old_value VARCHAR ,

new_value VARCHAR

);

type col_def_tabtyp is table of col_def_typ

Description of col_def_typ

Column	Description
column_name	Tells your procedure the name of the column that was replicated from the source table, for example emp_last_name. This value is not case-sensitive.
data type	Tells your procedure the data type of the data in the replicated column, for example VARCHAR. This value is always in capital letters.
is_key	Tells your procedure whether or not the column is a key column. If it is a key column, SharePlex passes a value of TRUE. If the column is not part of a key, SharePlex passes a value of FALSE.
is_changed	Tells your procedure whether or not the column value has changed. If it is changed, SharePlex passes a value of TRUE. If the column is not changed, SharePlex passes a value of FALSE. For INSERTs, is_changed is TRUE for non-NULL values, because none of the columns existed in the database. If a NULL value is inserted, is_changed is FALSE. For UPDATEs, is_changed is TRUE for non-key columns. For key columns, is_changed normally is FALSE, but SharePlex will pass a value for a changed key column. Conflict resolution only: If a key value also was changed on the target system, SharePlex will not be able to locate the correct row, and conflict resolution could fail. For DELETEs, is_changed is always FALSE, because SharePlex replicates only the key values for a DELETE statement.
old_value	Tells your procedure the old value of the replicated column, before it was changed on the source system. This column is NULL for INSERTs, because the row did not exist in the target database before the INSERT. Conflict resolution only: This is the pre-image against which SharePlex compared the source and target columns as part of its synchronization check for UPDATEs and DELETEs. If the old value passed by SharePlex does not match the current_value value obtained from the target row, then there is a conflict.
new_value	Tells your procedure the new value of the replicated column, as changed on the source system.

Example entries in col_def_typ table per operation type

The following tables illustrate the possible outcomes of each type of operation.

INSERT operation

column_name	is_changed	old_value	new_value	is_key
C1	TRUE	NULL	bind	FALSE
C2	TRUE	NULL	bind	TRUE
C3	FALSE	NULL	NULL	TRUE | FALSE

UPDATE operation

column_name	is_changed	old_value	new_value	is_key
C1	TRUE	bind	bind	FALSE
C2	FALSE	bind	NULL	TRUE
C3	TRUE	bind	bind	TRUE

DELETE operation

column_name	is_changed	old_value	new_value	is_key
C1	FALSE	bind	NULL	TRUE

List the routines in conflict_resolution.dbname

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.testdb, where testdb is a DB name, 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.DB, where DB is the database name.

Important! There can be only one conflict_resolution.DB 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.

SchemaName.tableName

IUD

schema.procedure

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

scott.sal	IUD	scott.sal_cr
scott.cust	IUD	scott.cust_cr
!DEFAULT	IUD	scott.sal_cr5

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.

!DEFAULT	IUD	proc0
schema.table1	IUD	proc1
james.table1	IUD	proc2
james.table1	IUD	proc3

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.