Customers often ask “Can SharePlex do transformation?”. Often, what they’re really asking is, “Can SharePlex selectively replicate rows or columns?” or “Can SharePlex move data from one column in the source, to a different column in the target?”
For Oracle to Oracle replication, the answer to all three of these questions is, “Yes!” For Oracle to all other targets, the answer to the second two is also “Yes!” and, the answer to the first is “Not Yet”. Here’s more information.
When we say “transformation” as we discuss SharePlex, we mean the ability to change the content of a particular column in a table, as we move it from the source to the target. For example, we might want to mask all but the last 4 digits of a Social Security Number, or, we might want to add two columns together and put the result in a third column, or perhaps even in a different table.
With Oracle to Oracle replication, we accomplish this transformation in the POST process on the target system. Instead of simply posting data to the target database using SQL*PLUS, we create a stored procedure with an IN parameter that contains the data that would have been posted, and its meta-data. The code in the stored procedure then does whatever transformations are desired, and then writes the data to the database.
You can find more information about transformation in the Configure data transformation section of the “SharePlex Administrator Guide”; and in a future blog we’ll show examples of some transformation procedures and discuss implementation specifics.
Partitioning and Selective Replication
Somewhat like database partitioning, SharePlex partitioning is breaking the replicated data into “subsets” either horizontally by row, or vertically by column. Each of the subsets is treated as a separate replication stream, and can be directed to the same, or different targets. Unlike transformation, partitioning happens on the source system, and is available for an Oracle source to all targets.
With horizontal partitioning, we can partition data in two ways. The first, called “hash-based”, is based on a hash value. The hash value is used to determine the number of partitions to be used. For example, we could take a 10 million row table, divide it into 10 partitions of equal size.
For the second, “column based” partitioning, we define one or more partitions based on a value in some column. For example, we might want to place all active records into one table, and all in-active records into a different table, controlled by a “status” column.
Both horizontal partitioning schemes are controlled with the “add partition” command in sp_ctrl.
With vertical partitioning, we can partition the data into subsets by columns or groups of columns. We can also use this technique to replicate data from one column in the source to a different column on the target; or to exclude columns from replication altogether. For example, we could replicate a source column named “Column1” to a target column named “Column2”. Or, we could decide not to replicate a column containing sensitive data. Or, we could re-order columns.
Vertical partitioning is defined in the table specification section of the configuration file. The columns to be replicated or not are specified in the source table definition, and the column names for the target are specified in the target table definition.
Horizontal and Vertical partitioning can be combined to achieve almost any desired target configuration. For example, columns could be rearranged to make a different index structure easier; or sensitive columns such as Social Security number could be kept only on the source database.
More information about transformation in the Configure partitioned replication section of the “SharePlex Administrator Guide”; and in future blogs.
To learn more about SharePlex, visit our Web Site