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 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.
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.
Wildcards are not allowed anywhere in a configuration entry that includes the following:
The tables that use these features must be specified in the configuration file separately.
SharePlex supports the following SQL wildcards
Use this template for help when specifying a wildcarded name in the configuration file.
|expand src_owner.wildcard_name [not (list)]||
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.
SQL Server: The names of the target objects must be identical to those of the source objects, but the objects can be in different databases.
Oracle: The names of the target objects must be identical to those of the source objects, but the objects may belong to different owners.
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.
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.
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|
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.
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.% firstname.lastname@example.orgB
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.% email@example.comB
Datasource:o.sidA expand scott.%t__t% fred.% firstname.lastname@example.orgB
Datasource:o.sidA expand scott.% not (spo%, gen%, prodct) scott.% email@example.comB
Datasource:o.sidA expand scott.prod% not (%temp%) hal.% firstname.lastname@example.orgB
The following example contains a wildcarded schema, which is not permitted.
Datasource:o.sidA expand rob%.%test% scott.% email@example.comB
The following example contains a partially wildcarded target object name, which is not permitted.
Datasource:o.sidA expand scott.%test% scott.%obj% firstname.lastname@example.orgB
Note: Valid for Oracle tables only. Replication of SQL Server tables requires them to be defined with a primary 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.
The columns that you specify as a key must meet the following criteria:
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)
|src_owner.table !key (col_name, col2_name, ...)||
Datasource:o.ora1 scott.tab !key(name,ID) scott.tab2 sysB@oraB
You can configure SharePlex to filter out the following DML from replication when wildcarding is being used.
You can configure SharePlex to filter any type of DML operation so that the operation is not replicated to the target table. DML filtering is compatible with most other SharePlex configuration syntax.
To configure a DML filter, add the following syntax to the source table specification. Leave a space between the table specification and the filter specification. You can specify multiple operation types to filter. Any additional syntax for other features, such as a column list or key definition, must follow the DML filter specification.
Where DML_type is one of the following:
|DML_type input||Operation type|
The following example filters DELETE operations from being replicated to the target table.
The following example filters DELETEs and INSERTs so that only UPDATEs are replicated to the target table. This example also shows how a DML filter is compatible with a column mapping specification.
scott.stock !dml(d,i) (ID, name)
scott.stock (SKU, prod)
Use the verify config command to view the DML that is being filtered for each table in the configuration file. This command can be used for an active or inactive configuration file.
sp_ctrl> verify config myconfig
7: "SCOTT"."EMP" "SCOTT"."EMP" email@example.com
Filter out >>>>> DELETES
Unique Key : (EMPLOYEE_ID)
If there are multiple specifications of a source table in the configuration file, the DML filter specification must be identical for all of them. Multiple specifications of the same source table occur in the following instances:
You can prevent SharePlex from replicating sequences and SQL*Loader direct-path loads. By default the replication of these objects is enabled.
|Filter out this object||Set this parameter||Value|
|SQL*Loader direct-path loads||SP_OCT_REPLICATE_DLOAD||0|