SharePlex 9.2.6 - Administration Guide

Ensure compatible source-target mapping

The following guidelines help you ensure that the source and target objects that you want to map in a replication configuration are compatible.

Object names

For most replication strategies, the name and/or owner of a source object can be different from that of its target object. SharePlex replicates to the correct object because you specify it by owner and name within the configuration file. For high-availability configurations, the owner and name of a source table should be the same as the owner and name of the target table.

Source and target rows

Corresponding source and target rows must contain the same values to accurately reflect the source, unless transformation is being used.

One database in the configuration can have more or fewer rows than another database in the configuration. You can control which rows are replicated through horizontally partitioned replication. For more information, see Configure horizontally partitioned replication.

Source and target columns

Corresponding source and target columns must:

• Contain compatible data types (same type, size, precision).
• Have identical names, unless you use column mapping in the configuration file. For more information, see Map source and target columns.
• Be the same letter case if one of the databases is case-sensitive, but the source or target of that database is not case-sensitive. You can use column mapping to work around this issue. For more information, see Map source and target columns.

A target table can have more columns than the source table.

• If the source and related target column names are identical, SharePlex will ignore the extra columns in the target table.
• If the source and target column names are not identical, SharePlex maps a one-to-one relationship in the order that the columns are defined in each table (for example, map the first column in the source to the first column in the target, map the second column to the second column, and so forth).
• To avoid Oracle errors if the extra (non-mapped) columns are NOT NULL, define default values for those columns. For more information, see Map source and target columns.

A target table can have fewer columns than the number of columns in the source table, but you must use vertically partitioned replication to replicate only that subset of the source columns that matches the rows of the target. For more information, see Configure vertically partitioned replication.

Create a configuration file

To configure SharePlex to replicate data, you create a configuration file. This is an ASCII text file in which you specify:

• The data that you want SharePlex to replicate, including any filtering or partitioning of rows or columns
• The target name and type
• The system (and database if applicable) to which the data must be delivered.

Only a SharePlex Administrator or operator has the authority to create a configuration file.

When your configuration file is completed, you activate the configuration with the activate config command to begin replication. For more information, see Start replication on your production systems.

You can create a configuration file by hand in sp_ctrl or, if your data structure supports it, you can automate the creation of a configuration file with a script.

Create the configuration file on the system from which data is to be replicated, typically the source system, but strategies such as active-active replication require configuration files on more than one system.

To create a configuration file in sp_ctrl

1. Run sp_ctrl from the bin sub-directory of the SharePlex product directory.

2. In sp_ctrl, issue the create config command.

   sp_ctrl> create config config_name

   This command opens a file in the default text editor that is set for the operating system. Note: You can change the default editor that sp_ctrl uses. For more information, see Set a default editor for sp_ctrl.

3. Complete the configuration file. For more information, see Structure of a configuration file.

   Important! All configurations must reside in the config sub-directory of the SharePlex variable-data directory. Configuration files outside this directory cannot be activated. SharePlex places configurations in this directory by default when you create them through the sp_ctrl interface with the create config command. If you create the configuration directly through a text editor, make certain to save it to the config sub-directory.

To build a configuration file with a script

Note: Valid for Oracle only

SharePlex provides the following scripts that can be used to automate the building of your configuration file if the source and target object names are identical. These scripts support Oracle Database source and targets only.

Structure of a configuration file

A basic configuration file looks like the following:

The basic components of a configuration file are as follows.

How to qualify object names

So that SharePlex can determine the correct objects to capture from, and post to, you must qualify the object names in the configuration file in the same way that the database stores them logically. The general way this is indicated in SharePlex syntax is:

owner.object

Where:

• owner is the schema or database that contains the object (or objects, if wildcarded), depending on how that container is defined by the database.
• object is the name of the object or a wildcard specification to specify multiple objects.

When defining source or target objects in the configuration file, follow these guidelines for specifying the owner component:

How to specify case-sensitive names

This topic shows you how to specify case-sensitive names in the configuration file, for example when specifying table names or if you need to specify column names explicitly in a column mapping.

Case-sensitive object names

If the owner or name of an object is case-sensitive in the database, you must enclose that name within quotes in the SharePlex configuration file.

Important: This applies whether the database itself requires a case-sensitive name to be within quotes, such as Oracle, or whether the database accepts names that are spelled out in their case-sensitive form without quotes, like SQL Server.

To enforce case-sensitive object names

Specify the name in its correct case and enclose it within double quotes.

Correct way

• This is how to specify an object where both the owner and object names are both case-sensitive:

  "Owner"."Object"

• This is how to specify an object where only one of the components is case-sensitive:

  owner."Object" or "Owner".object

  The name that is not case-sensitive can be specified in any case.

Examples of both ways:

Incorrect way

This is not correct, because both components are within one set of quotes:

"Sales.Employees"

Case-sensitive column names

Ordinarily, column names are not specified in the configuration file, unless source column names need to be mapped to different target column names by means of a column mapping (see Map source and target columns). However, if the names of any pair of source and target columns have difference cases, you may need to include them in a column mapping to enforce their case sensitivity. Whether or not a column mapping is required depends on the target type: Oracle or Open Target.

To enforce case-sensitive column names to Oracle targets

The Oracle Post process does not perform case conversion of column names automatically. If the case is different between source and target columns, you must use a column map to map the case of the source names to the case of the target names. To get Post to enforce the case, specify the name in its correct case and enclose it within double quotes.

This is an example of case-sensitive column name mapping in a column map:

To enforce case-sensitive column names to Open Target

The Open Target Post process performs case conversion of column names automatically. If replicating to target columns that have a different case from their source columns, no column mapping is needed.