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:
Important!
|
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:
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:
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)
);
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'.
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.
|
| 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.
|
| reporting |
Determines how SharePlex reports unsuccessful procedural results. You must specify a value for this parameter.
|
SharePlex creates a col_def_typ type for each replicated operation. This type stores column information.
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
,data type user_tab_columns.data type%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
| 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.
|
| 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. |
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 |
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.
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.
Use the following template to link a procedure to one or more objects and operation types.
| SchemaName.tableName | IUD | schema.procedure |
where:
| scott.sal | IUD | scott.sal_cr |
| scott.cust | IUD | scott.cust_cr |
| !DEFAULT | IUD | scott.sal_cr5 |
How it works:
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 |
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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. 利用規約 プライバシー Cookie Preference Center
