This topic contains instructions for using the transformation feature of SharePlex. Transformation enables SharePlex to manipulate data before, or instead of, posting it to a target.
This topic contains instructions for using the transformation feature of SharePlex. Transformation enables SharePlex to manipulate data before, or instead of, posting it to a target.
Transformation directs the Post process to call a PL/SQL procedure (de fined as a transformation routine) instead of applying a SQL operation to the target database. Transformation enables replicated data to be manipulated before, or instead of, posting to a target.
For example, if a source table and its target table are dissimilar in construction — like when a person’s first and last name are in one column in the source table but in separate columns in the target table — you can write a transformation routine to convert the data for those columns so that replication succeeds. You can use transformation routines to convert data types, units of measurement, or character sets. You can use them instead of database triggers to reduce I/O overhead, and for many other business requirements.
When you specify transformation for a table, Post takes no action on the replicated data. Instead, it passes data values to your transformation routine, enabling you to control both the form and destination of the data with the procedure. You can post to the target table, post to an alternate location, or both. Therefore, when writing your routine, is your responsibility to include in your procedure the necessary SQL operations for posting.
Oracle
Oracle
Transformation is a convenient way to use SharePlex to transfer data from one table to another without concern for maintaining identical structure or data. This makes it practical for reporting, broadcast, and data mart and warehousing applications.
Transformation is not suitable for peer-to-peer or high-availability replication environments. High availability requires identical databases that are kept synchronized by replication. For peer-to-peer replication SharePlex must be able to detect and resolve conflicts when there are concurrent changes to the same record. When data is transformed, SharePlex cannot compare before and after values to verify synchronization and detect conflicts.
Transformation supports only INSERT, UPDATE and DELETE operations. You can do the following when developing procedures:
If a transformation routine is specified for an individual table, and the table also is part of a group of tables for which another routine is specified, only the table-specific routine is used for that table when the associated DML operation occurs.
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.
Any table that will be accessed through PL/SQL for transformation requires implicitly granted privileges from the owner of the object to SharePlex.
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 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.
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'
Deployment of transformation involves the following steps:
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.
Follow this template to create your procedure.
| (table_info in outsplex.sp_cr.row_typ, col_values insplex.sp_cr.col_def_tabtyp) |
where:
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 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)
);
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
);
CREATE SEQUENCE EXC_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE 20 NO CYCLE ;
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. |
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 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.
|
| reporting |
Determines how SharePlex reports unsuccessful procedural results. You must specify a value for this parameter.
|
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.
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.
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)
);
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
);
CREATE SEQUENCE EXC_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 1 CACHE 20 NO CYCLE ;
| 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.
|
| 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. |
The following tables illustrate the possible outcomes of each type of 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.
| 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
| 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.
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.
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.
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:
| scott.sal | IUD | scott.sal_tr |
| like:scott.%\_corp\_emp | IUD | scott.emp_tr1 |
| like:scott% | IUD | scott.emp_tr2 |
| scott.cust | U | scott.sal_tr |
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.
© ALL RIGHTS RESERVED. 利用規約 プライバシー Cookie Preference Center