Chat now with support
Chat with Support

SharePlex 11.0 - SharePlex Administration Guide

About this Guide Conventions used in this guide Revision History Overview of SharePlex Run SharePlex Run multiple instances of SharePlex Execute commands in sp_ctrl Set SharePlex parameters Configure data replication Configure replication to and from a container database Configure named queues Configure partitioned replication Configure replication to a change history target Configure a replication strategy Configure DDL replication Configure error handling Configure data transformation Configure security features Assign SharePlex users to security groups Start replication on your production systems Monitor SharePlex Prevent and solve replication problems Repair out-of-sync data Tune the Capture process Tune the Post process Recover replication after Oracle failover Make changes to an active replication environment Apply an Oracle application patch or upgrade Back up Oracle data on the source or target Troubleshooting Tips Appendix A: Peer-To-Peer Diagram Appendix B: SharePlex environment variables

Configure PostgreSQL to Oracle replication

The configuration files on the systems in a peer-to-peer configuration are identical with the exception of the datasource specification and the routing.

Conventions used in the syntax

In the configuration syntax in this topic, the placeholders represent the following items in the environment. This documentation assumes three systems, but there can be more.

  • hostA is the PostgreSQL system.

  • hostB is the Oracle system.
  • SchemaA.object is the fully qualified name of an object on hostA or a wildcarded specification.
  • ownerB.object is the fully qualified name of an object on hostB or a wildcarded specification.
Configuration on hostA

Datasource:r.dbname

schemaA.tablename ownerB.object hostB@o.oraB
schemaA.tablename ownerB.object

hostB@o.oraB

Configuration on hostB

Datasource:o.oraB

ownerB.object schemaA.tablename hostA@r.dbname
ownerB.object schemaA.tablename hostA@r.dbname
Example for HostA
Datasource:r.dbname
"demo"."data2k" "demo"."data2k" hostB@o.dbname
Example for HostB
Datasource:o.dbname
"demo"."data2k" "demo"."data2k" hostB@r.dbname

Set up conflict resolution routines

For information on setting up the conflict resolution routines for PostgreSQL to Oracle, see User defined conflict resolution routines for PostgreSQL to Oracle.

SharePlex prepared routines

For information on SharePlex prepared routines for PostgreSQL to Oracle replication, see SharePlex prepared routines.

Develop conflict resolution routines

This section provides information on various user-defined conflict resolution routines.

What is a conflict?

A conflict is defined as an out-of-sync condition — source and target tables are not identical. You can predict that out-of-sync (conflict) situations will occur when a DML statement constructed by SharePlex fails to execute on a row in the target table because of the following reasons:

  • Post applies a replicated INSERT but a row with the same key already exists in the target. Post applies the following logic:

    • If all of the current values in the target row are the same as the INSERT values, Post considers the rows to be in-sync and discards the operation.
    • If any of the values are different from those of the INSERT, Post considers this an out-of-sync condition.

    Note: You can configure Post so that it does not consider non-key values when posting an INSERT (applicable only when replicating data from Oracle to Oracle). See the SP_OPO_SUPPRESSED_OOS parameter in the SharePlex Reference Guide.

  • Post applies a replicated UPDATE but either cannot find a row in the target with the same key value as the one in the UPDATE or Post finds the correct row but the row values do not match the before values in the UPDATE. Post applies the following logic:

    • If the current values in the target row match the after values of the UPDATE, Post considers the rows to be in-sync and discards the operation.
    • If the values in the target row do not match the before or after values of the UPDATE, Post considers this an out-of-sync condition.

    Note: You can configure Post so that it returns an out-of-sync message if the current values in the target row match the after values of the UPDATE (applicable only when replicating data from Oracle to Oracle). See the SP_OPO_SUPPRESSED_OOS parameter in the SharePlex Reference Guide.

  • A DELETE is performed on the source data, but Post cannot locate the target row by using the key. When Post constructs its DELETE statement, it includes only the key value in its WHERE clause. If the row does not exist in the target, Post discards the operation.

User defined conflict resolution routines for Oracle to Oracle

User defined conflict resolution routines for Oracle 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:

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 PL/SQL package that can be used to pass information to and from the procedural routines that you write.

Note: Custom routines are supported for Oracle to Oracle source-target combinations only.

Before you get started, understand the following guidelines:

  • The same PL/SQL package is used for both generic conflict resolution and transformation (its name is sp_cr). Use either generic conflict resolution or transformation for a table, but not both. Transformed tables cannot be compared by SharePlex and conflict resolution cannot succeed. If both are used, SharePlex only calls the transformation routine. If appropriate, you can use generic conflict resolution and transformation for different tables in the same configuration. For more information, see Configure Data Transformation.
  • Conflict resolution cannot be used for DDL changes.
  • Any table to be accessed through PL/SQL for conflict resolution requires implicitly granted privileges from the owner of the object to SharePlex.
  • Conflict resolution does not support changes to LONG or LOB columns.

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

Follow this template to create your procedure.

(table_info in outsplex.sp_cr.row_typ, col_values insplex.sp_cr.col_def_tabtyp)

where:

  • splex is the SharePlex schema.
  • sp_cr is the name of the package that contains the PL/SQL record and table structures.
  • row_typ is the name of the PL/SQL record that passes in/out variables (see Package definition).
  • col_def_type is the name of the PL/SQL table that stores column information (see col_def_type table).
Package definition

SharePlex defines PL/SQL record and table structures in a public package named sp_cr in the SharePlex database schema. The package uses the following parameters.

CREATE or REPLACE PACKAGE sp_cr AS 
TYPE row_typ IS RECORD 
src_host VARCHAR2(32), 
src_ora_sid VARCHAR2(32), 
src_ora_time VARCHAR2(20), 
source_rowid VARCHAR2(20), 
target_rowid VARCHAR2(20), 
statement_type VARCHAR2(6), 
source_table VARCHAR2(78), 
target_table VARCHAR2(78), 
oracle_err NUMBER, 
status NUMBER, 
action NUMBER, 
reporting NUMBER 
);
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_ora_sid The ORACLE_SID of the source database. It is case-sensitive and is passed in the same case as in the oratab file or V$PARAMETER table.
src_ora_time The timestamp of the change record in the source redo log.
source_rowid The row ID of the source row. It is passed as a literal within single quotes, for example ‘123456’.
target_rowid The row ID of the corresponding row in the target database. SharePlex obtains the row ID by querying the target database. It is passed as a literal within single quotes, for example ‘123456’. If the row cannot be found using the PRIMARY key, the value is NULL.
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. It is passed within double quotes, for example "scott"."emp."
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. It is passed within double quotes, for example "scott"."emp."
oracle_err

This is different, depending on whether the procedure is being used for conflict resolution or transformation.

Transformation: SharePlex passes a value of 0 for this variable. This variable is only used for conflict resolution.

Conflict resolution: The Oracle error number that caused the conflict.

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 or transformation.

    Transformation: Post does not write any SQL. SharePlex does not write any error messages to the Event Log when transformation succeeds. It continues its processing by reading the next replicated operation in the post queue.

  • 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.
  • (Transformation only) A value of 7 implies unsuccessful execution and instructs the Post process to stop.
action

Defines the action that you want SharePlex to take. This is different, depending on whether the procedure is used for transformation or conflict resolution.

Transformation: You must specify a value of 0 for this parameter, which directs SharePlex NOT to post the SQL statement. Your transformation routine is responsible for posting the results of the transformation either to the target table or another table. The outcome of this action depends on what you specify for the reporting variable

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 SID_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 SID_errlog.sql log and report an error to the Event Log.

col_def_type table

SharePlex creates a col_def_tabtyp PL/SQL table for each replicated operation. This table stores column information. It is different depending on whether the procedure is used for transformation or conflict resolution.

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

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_tabtyp table.

type col_def_typ is record 
(column_name user_tab_columns.column_name%type 
,data type user_tab_columns.data type%type
,is_key boolean 
,is_changed boolean 
,old_value varchar2(32764) 
,new_value varchar2(32764) 
,current_value varchar2(32764) 
); 
type col_def_tabtyp is table of col_def_typ
Description of col_def_tabtyp
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 VARCHAR2. 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.
current_value Tells your procedure the current value of the column in the target table. If SharePlex cannot locate the target row, the value is NULL.
Example entries in col_def_tabtyp 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 current_value1 is_key
C1 TRUE NULL bind NULL FALSE
C2 TRUE NULL bind NULL TRUE
C3 FALSE NULL NULL NULL TRUE | FALSE

1 When an INSERT fails, it is because a row with the same PRIMARY key already exists in the target database. SharePlex does not return the current value for INSERTs.

UPDATE operation
column_name is_changed old_value new_value current_value1, 2 is_key
C1 TRUE bind bind NULL | target_value FALSE
C2 FALSE bind NULL NULL | target_value TRUE
C3 TRUE bind bind NULL | target_value TRUE

1 (Conflict resolution) When an UPDATE fails, it is because SharePlex cannot find the row by using the PRIMARY key and the pre-image. If the row cannot be found, SharePlex searches for the row by using only the PRIMARY key. If SharePlex finds the row, it returns the current value for the key column as well as the changed columns. If SharePlex cannot find the row by using just the PRIMARY key, then SharePlex returns a NULL.

2 (Transformation) For an UPDATE, SharePlex cannot locate a row using the PRIMARY key and the pre-images, because the pre-images are different due to transformation. As an alternative, it searches for the row using just the PRIMARY key. If it finds it, SharePlex returns the current value for the key column as well as the changed columns. If it cannot locate the row using just the PRIMARY key, then current_value is NULL

DELETE operation
column_name is_changed old_value new_value current_value1 is_key
C1 FALSE bind NULL NULL TRUE

1 When a DELETE fails, it is because SharePlex could not find the row by using the PRIMARY key. Therefore, SharePlex returns a NULL.

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.SID file, where SID is the ORACLE_SID of the target instance, 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.SID, where SID is the ORACLE_SID of the target instance.

Note: the SID is case-sensitive.

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.

owner.object {i | u | d | iud} owner.procedure

where:

  • owner.object is the owner and name of a target object, or a wildcarded entry. (See Syntax rules)
  • i| u | d is the type of operation that creates the conflict that is resolved with the specified procedure. You can specify any or all operation types, for example id or iud. Upper or lower case are both valid.
  • owner.procedure is the owner 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.
  • You can use the LIKE operator and a SQL wildcard (%) to specify multiple objects by using a search string. (See the Example.)
  • You can use an underscore (_) to denote a single-character wildcard. For table names that contain an underscore character (for example emp_sal), SharePlex recognizes the backslash (\) as an escape character to denote the underscore as a literal and not a wildcard, for example: like:scott.%\_corp\_emp. If you are not using the LIKE operator, the backslash escape character is not required if an object name contains an underscore.
  • 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.
  • You a comment line anywhere in the file. Start a comment line with the pound symbol (#).
Example conflict resolution file
scott.sal IUD scott.sal_cr
like:scott.%\_corp\_emp IUD scott.emp_cr1
like:scott.%\_corp\_emp IUD scott.emp_cr2
like:scott% IUD scott.emp_cr3
scott.cust U scott.sal_cr

How it works:

  • The scott.sal_cr routine is used for the scott.sal table before the scott.emp_cr1 procedure is used for that table.
  • The scott.emp_cr1 procedure is used before the scott.emp_cr2 procedure for all tables meeting the search criteria, and so forth.
  • For scott.cust, a procedure is called for UPDATEs before the other 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 owner 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.

In the following example, the !UpdateUsingKeyOnly procedure is used for UPDATEs and DELETEs for all tables, including james.table1, although the user-defined procedures listed for james.table1 take precedence.

!DEFAULT U !UpdateUsingKeyOnly
!DEFAULT D !UpdateUsingKeyOnly
james.table1 U james.procedure_upd
james.table1 I james.procedure_ins
james.table1 D james.procedure_del
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.

Log information about resolved conflicts

You can configure the Post process to log information about successful conflict resolution operations if you are using the SharePlex prepared routines. This feature is disabled by default.

Note: This feature can be used only with Oracle to Oracle source-target combinations.

To enable the logging of conflict resolution:

  1. Run sp_ctrl on the target system.

  2. Issue the following command:

    sp_ctrl> set param SP_OPO_LOG_CONFLICT {1 | 2}

    • A setting of 1 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table.

      Note: A setting of 1 will not update the columns EXISTING_TIMESTAMP and TARGET_ROWID (when existing data is not replaced) in the SHAREPLEX_CONF_LOG table.

    • A setting of 2 enables the logging of conflict resolution to the SHAREPLEX_CONF_LOG table with Post query for additional meta data.

      Using LeastRecentRecord or MostRecentRecord prepared routines Post will query the target database for the timestamp column of the existing record. The query result is logged into the EXISTING_TIMESTAMP column of the SHAREPLEX_CONF_LOG table.

      For any prepared routines, on rows that aren't replaced by the incoming record, Post will query the TARGET_ROWID of the existing row that could have been replaced. Otherwise the ROWID of the existing row will not be logged.

      Note: A setting of 2 may affect the performance of Post as a result of making the query.

  3. Restart Post.

Post logs the information to a table named SHAREPLEX_CONF_LOG. The following describes this table.

Column Column Definition Information that is logged
CONFLICT_NO NUMBER NOT NULL The unique identifier of the resolved conflict. This value is generated from the shareplex_conf_log_seq sequence.
CONFLICT_TIME TIMESTAMP DEFAULT SYSTIMESTAMP The timestamp of the conflict resolution
CONFLICT_TABLE VARCHAR2(100) The name of the target table that was involved in the conflict
CONFLICT_TYPE VARCHAR2(1) The type of conflict, either I for insert, U for update, or D for delete
CONFLICT_RESOLVED VARCHAR2(1) NOT NULL

Indicator of whether the conflict was resolved or not.

Y = yes, the conflict was resolved

N = no, the conflict was not resolved. Unresolved conflicts are logged to the ID_errlog.sql file, where ID is the source database identifier.

TIMESTAMP_COLUMN VARCHAR2(50) The name of the column that contains the timestamp that Post compared to determine which record was most recent.
INCOMING_TIMESTAMP DATE The timestamp that the row was inserted, updated, or deleted on the source system
EXISTING_TIMESTAMP DATE The current timestamp of the row in the target database. This applies only if the SP_OPO_LOG_CONFLICT parameter is set to 2, which directs Post to query the target database to get this value.
PRIMARY_KEYS VARCHAR2(4000) The names of the primary key columns
MESSAGE VARCHAR2(400)

A message that states which row won in the conflict. The row that wins depends on which conflict resolution routine was used. For example, the following message is returned when the !MostRecentRecord routine is used and the most recent record is the source record:

Incoming timestamp > existing timestamp. Incoming wins, overwrite existing.

If the target record was the most recent one or has the same timestamp as the source record, then the message would be:

Incoming timestamp <= existing timestamp. Existing wins, discard incoming.

SQL_STATEMENT LONG The final SQL statement that got executed as a result of the conflict resolution
CONFLICT_CHECKED VARCHAR2(1) Indicates whether or not someone reviewed the conflict. The default is N for No. The person who reviews the conflict can change this value to Y.

User defined conflict resolution routines for PostgreSQL to PostgreSQL

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!

  • 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.

Note: Custom routines are supported from Oracle to PostgreSQL and PostgreSQL to PostgreSQL source-target combinations only.

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

,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

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.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating