SharePlex 9.2.6 - Administration Guide

Considerations when using transformation

Because transformation changes data and because SharePlex does not post the data, transformation changes the behavior of replication. It is a customization of SharePlex processing. Review the following considerations before implementing transformation to ensure that your transformation procedures succeed.

Privileges

Any table that will be accessed through PL/SQL for transformation requires implicitly granted privileges from the owner of the object to SharePlex.

Keys

A PRIMARY or UNIQUE key is required for all tables using transformation. SharePlex locates the target row for UPDATEs and DELETEs by using the key, which enables it to return values to your transformation routine from the target table for UPDATE operations. Do not allow keys to be changed on the target system, or SharePlex will not be able to locate the row to pass values to your routine.

Test your routines

Test your transformation 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. When data is transformed, SharePlex cannot compare before and after values to verify synchronization, which is a measure of whether the routines are performing correctly. The only way to confirm synchronization is to use the compare command with the key option. This option restricts the comparison to just the key values and is not a complete confirmation of synchronization. It only detects missing or extra rows. It does not (and cannot) indicate whether values in non-key columns are properly correlated to those in the source columns, because the target data was transformed.

For those reasons, the repair command cannot be used to resynchronize data. You must devise your own resynchronization procedures based on your company’s business rules and the database environment.

Dates

The default date format for SharePlex transformation is MMDDYYYY HH24MISS. Tables with default dates must use that format, or transformation will return errors. Before creating a table with a default date, use the following command to change the date format in SQL*Plus.

ALTER SESSION SET nls_date_format = 'MMDDYYYYHH24MISS'

Other considerations

• Transformation does not support changes to LOB and LONG columns.
• The processing overhead for passing data to your procedure, combined with that of executing the procedure itself, degrades overall performance on the target system compared to normal replication and posting.
• 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 peer-to-peer replication.

Deploy transformation

Deployment of transformation involves the following steps.

• Create configuration entries for the source and target tables to be transformed. There are no special configuration procedures for tables that use transformation. Configure them as you would any other table. For more information, see Create a configuration file.
• Create transformation routines. For more information, see Create transformation routines.
• Create a transformation file, which directs SharePlex to call the transformation routines. For more information, see Create the transformation file.

Create transformation routines

Write your transformation routines with dynamic PL/SQL procedural language. Use parameters and record and table structures defined in the public package named sp_cr. This package was installed in the SharePlex schema in the database. The package uses the following parameters.

Procedure interface

Follow this template to create your procedure.

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 %s.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), \n\t"
        "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.

OUT variables

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

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

Example entries in col_def_tabtyp table per operation type

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

INSERT operation

¹ 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

¹ (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.

² (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

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

Create the transformation file

To direct SharePlex to call transformation routines instead of posting SQL operations, use the transformation.SID file, where SID is the ORACLE_SID of the target database. Before executing a SQL operation, Post reads this file to determine if there is a transformation routine that it must call.

Where to find this file

A blank transformation.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 transformation.SID, where SID is the ORACLE_SID of the target instance. Note: the SID is case-sensitive.

Important! There can be only one transformation.SID file per active configuration.

How to make entries in the file

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

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 to be transformed by 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 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.
• You a comment line anywhere in the file. Start a comment line with the pound symbol (#).

Example transformation file

How to change the file during replication

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

Configure security features

SharePlex provides a number of security features that help protect replicated data on the local system and during transfer across a network. This chapter provides guidelines for the configuration and use of these features.

Contents

Secure data with SSL/TLS

SharePlex provides a feature to enable SSL/TLS connections for all network traffic. This will encrypt data on the network between SharePlex instances and between SharePlex and the command line interface.

If SSL/TLS is enabled, SharePlex will only accept SSL/TLS connections. For all SharePlex instances that replicate to each other, either all must have SSL/TLS enabled or all must have SSL/TLS disabled. The SSL/TLS configuration includes a network password. This password must be the same across all of your SharePlex instances.

NOTE: For TLS connections, SharePlex supports TLS 1.2 (or later).

To change the SSL/TLS configuration:

• Shutdown sp_cop on all nodes
• Run “sp_security --setup” on all nodes
• Start sp_cop on all nodes

sp_security

Use the sp_security utility to enable, disable or view the SSL/TLS settings for SharePlex network communication.

Enable SSL/TLS

IMPORTANT! SSL/TLS must be either enabled with a common network password or disabled on all SharePlex installations.

To enable SSL/TLS

Run “sp_security --setup”, select the SSL/TLS option, and then enter a network password.

% sp_security --setup

Security Setup Wizard
---------------------
This wizard will walk you through setting up the SharePlex network security.

Setup configuration for '/home/shareplex/var110/' and Port 2100 [N]: Y

Choose your network security model.  Please note the following:
         * Cop must be down when the security model is changed, or when the network password is changed
         * The same model must be used among all SharePlex nodes replicating to each other
         * For security model [1], the same network password must be set on all SharePlex nodes replicating to each other

  [1] Use basic SSL/TLS connections
  [2] Use non-SSL/TLS connections (default prior to SharePlex 9.1.3)

Security model: 1

Please enter a network password that will be used for authentication
among the SharePlex nodes.  All SharePlex nodes that replicate data to each
other must have the same network password.

Network password:
Please re-enter the network password

Network password:

Security settings:

Configuration for '/home/shareplex/var110/' and Port 2100:

  Security model            : SSL/TLS
  Network password          : stored for unattended startup
  SSL key file password     : stored for unattended startup
  SSL key file              : key.pem
  SSL cert file             : cert.pem

Setup complete!

Disable SSL/TLS

IMPORTANT! SSL/TLS must be either enabled with a common network password or disabled on all SharePlex installations.

To disable SSL/TLS

Run “sp_security --setup” and select non-SSL/TLS connections.

% sp_security --setup

Security Setup Wizard
---------------------
This wizard will walk you through setting up the SharePlex network security.

Setup configuration for '/home/shareplex/var110/' and Port 2100 [N]: Y

Choose your network security model.  Please note the following:
         * Cop must be down when the security model is changed, or when the network password is changed
         * The same model must be used among all SharePlex nodes replicating to each other
         * For security model [1], the same network password must be set on all SharePlex nodes replicating to each other

  [1] Use basic SSL/TLS connections
  [2] Use non-SSL/TLS connections (default prior to SharePlex 9.1.3)

Security model: 2

Security settings:

Configuration for '/home/shareplex/var110/' and Port 2100:

  Security model            : Un-encrypted

Setup complete!

View current SSL/TLS configuration

To view the current SSL/TLS configuration

Run “sp_security --show”.

% sp_security --show

Security settings:

Configuration for '/home/shareplex/var110/' and Port 210:

  Security model            : Un-encrypted