Capture from multiple local datasources
You can use one instance of SharePlex to capture from multiple datasources on a system. All of the configurations can be active at the same time.
Note: SharePlex does not support multiple active configuration files for the same datasource, but it does support multiple active configuration files if each replicates a different datasource.
To capture from multiple datasources:
- Create a configuration file for the first datasource. In each routing map, include a named export queue. For more information, see Configure Named Export Queues.
-
Create a configuration file for the second datasource. In each routing map, specify a named export queue, but make certain it is different from any of the queues named in the first configuration file. It is important that data from one datasource does not process through the export queues of the other datasource.
- Create additional configurations with dedicated named export queues, if needed.
- When you activate the configuration files, use a separate sp_ctrl session for each one. For more information, see How to Activate Multiple Configuration Files.
Use Wildcards to specify multiple objects
You can use wildcard characters to specify multiple objects of a schema in one entry of the configuration file. SharePlex replicates any objects that satisfy the wildcard, except those that you explicitly exclude.
Note: Only object names can be wildcarded. Owner names cannot be wildcarded.
Requirements and limitations of wildcard support
Supported wildcard syntax
SharePlex supports the following SQL wildcards
- Percent (%) wildcard to specify a string. (See the Use Wildcards to Specify Multiple Objects.)
- Underscore (_) wildcard to specify a single-character.
- For table names that contain a percent sign or an underscore character (for example emp_salary), SharePlex recognizes the backslash (\) as an escape character to denote the character as a literal, not a wildcard.
Specify wildcarded names in the configuration file
Use this template for help when specifying a wildcarded name in the configuration file.
Configuration with wildcarded object names
| datasource_specification |
| expand src_owner.wildcard_name [not (list)] |
tgt_owner.wildcard_name |
routing_map |
Description of syntax elements
| expand |
Indicates that the specification contains wildcard characters that must be expanded. When SharePlex detects the expand keyword, it queries the database for all objects that match the criteria in the wildcard specification. Without this required keyword, the wildcard characters are assumed to be part of an explicit object name, and no wildcard expansion is performed.
Note: Leave a space between expand and the start of the source object specification. |
| src_owner.wildcard_name |
- src_owner is the owner of the source objects. Owner names cannot be wildcarded. If wildcards are used in the owner name, SharePlex assumes that they are part of the owner (schema) name.
- wildcard_name is the wildcarded name of the source objects.
Rules:
Oracle: The names of the target objects must be identical to those of the source objects, but the objects may belong to different owners. |
| not (list) |
An exclusion list that defines objects to omit from the wildcard expansion. Use this option to exclude objects that you do not want to be replicated.
Note: This not keyword does not have the same meaning as the SQL wildcard NOT operator.
- The not keyword and parentheses are required elements.
- list is a comma-separated list of tables owned by the same owner, either wildcarded or explicit. Example: not (spo%, gen%, product).
Leave a space before and after the not keyword. A space is allowed after each comma in the list.
Note: If an object that satisfies a wildcard is listed elsewhere in the configuration file, that entry overrides the processing or routing specified in the wildcarded entry. In this case, a not clause is not needed. See the Examples. |
| tgt_owner.wildcard_name |
- tgt_owner is the owner of the target objects.
- wildcard_name is the wildcarded name of the target objects.
The target specification must be in the form of owner.%. Partially expanded target wildcarded names are not supported, such as owner.tab%. |
| routing_map |
Any valid routing map. For more information, see Routing Specifications in a Configuration File |
Validate a wildcard specification
To confirm that a wildcard specification will produce the specific list of tables that you want to replicate, issue the verify config command in sp_ctrl before you activate the configuration. This command produces a list of the objects that SharePlex will capture and replicate, as well as any problems that occurred. For more information about this command, see the SharePlex Reference Guide.
Examples of valid wildcard specifications
Example 1: The following wildcard specification directs SharePlex to activate all tables owned by scott, where the table name is like prod% except if the table name is like %temp%. All tables that match this description are replicated to tables of the same names on the target in the hal schema. Note that SharePlex automatically upshifts the names, so that it actually activates all tables where the table name is like 'PROD%' but not like '%TEMP%'.
Datasource:o.sidA
expand scott.prod% not (%temp%) hal.% sysa@o.sidB
Example 2: The following example shows how you can specify special handling for one of the tables in a wildcarded specification, in this case the photo table. All tables but photo are routed through the default post queue. The separate entry for the photo table overrides the wildcarded entry and processes the photo table through a named post queue. For more information, see Configure Named Post Queues.
Datasource:o.sidA
cust.% cust.% hostB@o.oraB
cust.photo cust.photo hostB:lobQ@o.oraB
The following are additional examples of valid wildcard specifications
Datasource:o.sidA
expand scott.%test% scott.% sysa@o.sidB
Datasource:o.sidA
expand scott.%t__t% fred.% sysa@o.sidB
Datasource:o.sidA
expand scott.% not (spo%, gen%, prodct) scott.% sysa@o.sidB
Datasource:o.sidA
expand scott.prod% not (%temp%) hal.% sysa@o.sidB
Examples of invalid wildcard specifications
The following example contains a wildcarded schema, which is not permitted.
Datasource:o.sidA
expand rob%.%test% scott.% sysa@o.sidB
The following example contains a partially wildcarded target object name, which is not permitted.
Datasource:o.sidA
expand scott.%test% scott.%obj% sysa@o.sidB
Use Wildcards to Specify Multiple Tables for PostgreSQL
You can use wildcard characters to specify multiple tables of a schema in one entry of the configuration file. SharePlex replicates any tables that satisfy the wildcard, except those that you explicitly exclude.
Note: Only table names can be wildcarded. Schema names cannot be wildcarded.
Requirements and limitations of wildcard support
The schemas that contain wildcarded table names must exist on the source and target before the configuration is activated.
Supported wildcard syntax
SharePlex supports the following PostgreSQL wildcards:
- Percent (%) wildcard to specify a string. (See the Examples)
- Underscore (_) wildcard to specify a single-character.
- For table names that contain a percent sign or an underscore character (for example emp_salary), SharePlex recognizes the backslash (\) as an escape character to denote the character as a literal, not a wildcard.
Specify wildcarded names in the configuration file
Use this template for help when specifying a wildcarded name in the configuration file.
Configuration with wildcarded table names
| datasource_specification |
| expand src_schema.wildcard_name [not (list)] |
tgt_schema.wildcard_name |
routing_map |
Description of syntax elements
| expand |
Indicates that the specification contains wildcard characters that must be expanded. When SharePlex detects the expand keyword, it queries the database for all tables that match the criteria in the wildcard specification. Without this required keyword, the wildcard characters are assumed to be part of an explicit table name, and no wildcard expansion is performed.
Note: Leave a space between expand and the start of the source table specification. |
| src_schema.wildcard_name |
- src_schema is the schema of the source tables. Schema names cannot be wildcarded. If wildcards are used in the schema name, SharePlex assumes that they are part of the schema name.
- wildcard_name is the wildcarded name of the source tables.
PostgreSQL: The names of the target tables must be identical to those of the source tables, but the tables may belong to different schemas. |
| not (list) |
An exclusion list that defines tables to omit from the wildcard expansion. Use this option to exclude tables that you do not want to be replicated. Note: This not keyword does not have the same meaning as the SQL wildcard NOT operator.
- The not keyword and parentheses are required elements.
- list is a comma-separated list of tables owned by the same schema, either wildcarded or explicit. Example: not (spo%, gen%, product).
Leave a space before and after the not keyword. A space is allowed after each comma in the list.
Note: If an table that satisfies a wildcard is listed elsewhere in the configuration file, that entry overrides the processing or routing specified in the wildcarded entry. In this case, a not clause is not needed. See the Examples. |
| tgt_schema.wildcard_name |
- tgt_schema is the schema of the target tables.
- wildcard_name is the wildcarded name of the target tables.
The target specification must be in the form of schema.%. Partially expanded target wildcarded names are not supported, such as schema.tab%. |
| routing_map |
Any valid routing map. |
Validate a Wildcard Specification
To confirm that a wildcard specification will produce the specific list of tables that you want to replicate, issue the verify config command in sp_ctrl before you activate the configuration. This command produces a list of the tables that SharePlex will capture and replicate, as well as any problems that occurred. For more information about this command, see SharePlex Reference Guide.
Examples
Examples of valid wildcard specifications
Example 1: The following wildcard specification directs SharePlex to activate all tables owned by scott, where the table name is like prod% except if the table name is like %temp%. All tables that match this description are replicated to tables of the same names on the target in the hal schema.
Datasource:r.dbname
expand scott.prod% not (%temp%) hal.% hostB@r.dbname
Example 2: The following example shows how you can specify special handling for one of the tables in a wildcarded specification, in this case the photo table. All tables but photo are routed through the default post queue. The separate entry for the photo table overrides the wildcarded entry and processes the photo table through a named post queue.
Datasource:r.dbname
cust.% cust.% hostB@r.dbname
cust.photo cust.photo hostB:queuename@r.dbname
The following are additional examples of valid wildcard specifications for PostgreSQL to PostgreSQL replication:
Datasource:r.dbname
expand scott.%test% scott.% hostB@r.dbname
Datasource:r.dbname
expand scott.%t__t% fred.% hostB@r.dbname
Datasource:r.dbname
expand scott.% not (spo%, gen%, prodct) scott.% hostB@r.dbname
Datasource:r.dbname
expand scott.prod% not (%temp%) hal.% hostB@r.dbname
The following is an example of valid wildcard specifications for PostgreSQL to Oracle replication:
Datasource:r.dbname
expand "scott"."%test%" "scott"."%" hostB@o.target_dbname
The following is an example of valid partial wildcard specifications for PostgreSQL to SQL Server replication:
Datasource:r.dbname
expand scott.%test% scott.%test% hostB@r.target_dbname
Examples of invalid wildcard specifications
The following example contains a wildcarded schema, which is not permitted.
Datasource:r.dbname
expand rob%.%test% scott.% hostB@r.dbname
The following example contains a partially wildcarded target table name, which is not permitted.
Datasource:r.dbname
expand scott.%test% scott.%obj% hostB@r.dbname
Define a Unique Key for Oracle Database
Define a Unique Key
If a table was not created with a primary or unique key, you can specify columns to use as a key when you specify the object in the configuration file. SharePlex uses the specified columns as a unique key in its WHERE clause to locate target rows for posting.
NoteS:
- Without a primary or unique key, SharePlex uses all of the columns of a table (or all of the columns in a column partition) as a key, which slows replication performance.
- When a key definition is specified for a table that has a PRIMARY or UNIQUE key, the key definition overrides the defined key. This can be useful if you do not want any of the existing keys to be used by SharePlex.
|
Define a unique key - Oracle to Oracle
The columns that you specify as a key must meet the following criteria:
- They cannot be LONG or LOB columns.
- They must be able to uniquely identify a row. Otherwise, replication could return out-of-sync errors or post to incorrect target rows.
- They must be part of the column partition if the table is configured for vertically partitioned replication. When using the exclude column notation in vertical partitioning, the excluded columns cannot be used in the key definition. For more information, see Configure Partitioned Replication.
- Include the columns in a supplemental log group. Otherwise, SharePlex must query the database for their values.
- Create an index on the target table and add the index to the SharePlex hints file, located in the variable-data directory, which directs the Post process to use the index.
Syntax for key definition
To create a key definition, type a space after the source object and use the following syntax, including the parentheses.
src_owner.table !key (column_list)
where:
- !key is a required keyword.
- column_list is a list of columns to include in the key. Separate column names with commas. A space after the comma is optional.
|
datasource_specification |
|
|
| src_owner.table !key (col_name, col2_name, ...) |
tgt_owner.table |
host@o.SID |
Example
Datasource:o.ora1
scott.tab !key(name,ID) scott.tab2 sysB@oraB
Define a unique key - PostgreSQL to PostgreSQL
The columns that you specify as a key must meet the following criteria:
-
A unique key cannot be text, char with more than 2000 characters, varchar without size or more than 4000 characters.
- They must be able to uniquely identify a row. Otherwise, replication could return out-of-sync errors or post to incorrect target rows.
-
They must be part of the column partition if the table is configured for vertically partitioned replication. When using the exclude column notation in vertical partitioning, the excluded columns cannot be used in the key definition. For more information, see Configure Partitioned Replication.
- Create an index on the target table, it directs the Post process to use the index.
Syntax for key definition
To create a key definition, type a space after the source object and use the following syntax, including the parentheses.
src_schema.table !key (column_list)
where:
- !key is a required keyword.
- column_list is a list of columns to include in the key. Separate column names with commas. A space after the comma is optional.
| (missing or bad snippet) |
|
|
| src_schema.table !key (col_name, col2_name, ...) |
tgt_schema.table |
host@r.dbname |
Example
Datasource:r.dbname
scott.tab !key(name,ID) scott.tab2 sysB@dbname
Define a unique key - PostgreSQL to Oracle
The columns that you specify as a key must meet the following criteria:
Syntax for key definition
To create a key definition, type a space after the source object and use the following syntax, including the parentheses.
src_schema.table !key (column_list)
where:
- !key is a required keyword.
- column_list is a list of columns to include in the key. Separate column names with commas. A space after the comma is optional.
|
datasource_specification |
|
|
| src_schema.table !key (col_name, col2_name, ...) |
tgt_owner.table |
host@o.SID |
Example
Datasource:r.dbname
"scott"."tab" !key(name,ID) "scott"."tab2" sysB@o.oraB
