Chat now with support
Chat with Support

SharePlex 8.6.6 - Reference Guide

About this guide Conventions used in this guide SharePlex commands SharePlex parameters SharePlex utilities Appendix B: SharePlex environment variables

Configure vertically partitioned replication

Use vertically partitioned replication to replicate a subset of the columns of a table. For example, you can replicate data changes made to C1, C2, C3, and C4, but not changes made to C5 and C6, as shown in the diagram.

Supported targets

All

Guidelines for using vertically partitioned replication

Follow these guidelines when creating a configuration file that includes vertically partitioned replication.

  • Vertically partitioned replication is appropriate for reporting and other data sharing strategies, but it is not appropriate for high availability environments. Once you configure a table for vertically partitioned replication, SharePlex does not recognize the other columns, so data in those columns is not replicated.

  • You can combine horizontally partitioned and vertically partitioned replication for maximum control over which information is distributed, and to where.

    For example:

    A company has a headquarters and regional divisions. The headquarters maintains the corporate database, and each region maintains a regional database. The headquarters uses vertically partitioned replication to share some of the column data of a table to those locations, while retaining other sensitive data at headquarters. Row changes made to the shared columns are further partitioned horizontally, for replication to the appropriate regional database.
  • A table cannot be configured to replicate some columns to one target system and all columns to another (combination of vertically partitioned replication and full-table replication). You can, however, configure full-table replication to an identical table on one target, and then configure vertically partitioned replication from that target to a second target that contains the table that requires only the partition columns.
  • A target table can, but does not have to, contain all of the same columns as its source table. The target can contain just the columns being replicated from the source table. The names of corresponding source and target columns do not need to be the same. Corresponding columns must contain the same datatypes (same type, size, precision).
  • ALTER TABLE to add a column to a table configured for vertically partitioned replication is not supported.

To configure vertically partitioned replication, you specify either a column partition or an excluded column partition in the configuration file:

  • A column partition replicates data changes that are made to the specified columns.
  • An excluded column partition replicates all data changes except those made to the specified columns.

Use one configuration file for all of the data that you want to replicate from a given datasource, including tables that will have full-table replication and those that will use partitioned replication. For general instructions on creating a configuration file, see Create configuration files. To configure entries for vertically partitioned replication, use the following syntax.

datasource_specification    
src_owner.table (src_col,src_col,...) tgt_owner.table [(tgt_col,tgt_col,...)] routing_map
src_owner.table !(src_col,src_col,...) tgt_owner.table routing_map
Configuration component Description
src_owner.table and tgt_owner.table The specifications for the source and target tables, respectively.

(src_col, src_col,...)

and

!(src_col,src_col,...)

The source columns to be replicated. Can be one of the following:

  • (src_col, src_col,...) specifies a column partition that lists columns to replicate.
  • !(src_col,src_col,...) specifies an excluded column partition that lists columns not to replicate. The remaining columns comprise the actual column partition.

Note: When using an excluded column partition, the corresponding source and target column names must be identical, and the excluded columns cannot be used in a key definition. For more information, see Define a unique key.

Follow these rules to specify either type of column partition:

  • There can be one partition per source table.
  • A column list must be enclosed within parentheses.
  • Separate each column name with a comma. A space after the comma is optional.
  • The maximum length of a partition is 174820 bytes (the maximum line length allowed in a configuration file). Therefore, the actual number of columns that you can list depends on the length of each name.
  • The columns can be contiguous or non-contiguous in the source table. For example, you can replicate the first, third and seventh columns of a table.
  • Key columns are not required to be included in the partition.
  • If using horizontally partitioned and vertically partitioned replication together for this table, all of the columns in the partition scheme must be part of the column condition.
(tgt_col,tgt_col,...)

The target columns. Use this option to map source columns to target columns that have different owners or names. If the source and target columns have identical owners or names, the target columns can be omitted.

To map source columns to target columns, follow these rules:

  • The syntax rules for the source column partition also apply to the target column list.
  • The target columns must have identical definitions as their source columns, except for their names.

  • List the target columns in the same logical order as their corresponding source columns. This is required regardless of the actual order of the target columns in the table, so that SharePlex builds the correct correlations in the object cache. For example, a change to the second column in the source list is replicated to the second column in the target list.
routing_map

The routing map for the column partition. The routing map can be one of the following:

  • If using horizontally partitioned replication for the source table, specify a partition scheme, as in: !partition_scheme. See Configure horizontally partitioned replication for more information.
  • If not using horizontally partitioned replication for the source table, specify a routing map as follows:

    • Use a simple routing map like sysB@o.myora if replicating the column partition to one target. A route with a named export or post queue is supported. See Configure named export queues and Configure named post queues for more information about named queues.
    • Use a compound routing map like sysB@o.myora+sysC@o.myora2 if replicating the column partition to multiple target systems. Important! A compound routing map must be used, rather than listing multiple targets in separate entries, because only one column condition per source table can be listed in the configuration file. To use a compound routing map, the owners and names of all of the target tables must be identical. For more information about using a compound routing map, see Routing specifications in a configuration file.

Configuration examples

The following is a vertically partitioned replication configuration replicating to multiple targets by using a compound routing map. To use a compound routing map for this source table, all targets must be named scott.sal.

Datasourceo.oraA    
scott.emp (c1,c2) scott.sal

sysB@o.oraB+sysC@o.oraC

The following is a vertically partitioned replication configuration replicating to a single target where the target columns have different names from those of the source.

Datasourceo.oraA    
scott.emp (c1,c2) scott.sal (c5,c6)

sysB@o.oraB

The following configuration file is not valid because it repeats the same column partition of scott.emp (c1, c2) twice in the configuration file.

Datasourceo.oraA    
scott.emp (c1,c2) scott.cust (c1,c2)

sysB@o.oraB

scott.emp (c1,c2) scott.sales (c1,c2) sysC@o.oraC
Related Documents