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, BYTEA, JSON, JSONB, 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.
|
datasource_specification |
|
|
| 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
Filter DML operations for Oracle Database
You can configure SharePlex to filter out the following DML from replication when wildcarding is being used.
- Oracle DML type (INSERT, UPDATE, DELETE)
- DML related to Oracle sequences and Oracle SQL*Loader direct-path loads.
Filter out a DML type
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.
Configure a DML filter
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.
!dml(DML_type[,DML_type][,...])
Where DML_type is one of the following:
| i |
INSERT |
| d |
DELETE |
| u |
UPDATE |
Examples
Example 1
The following example filters DELETE operations from being replicated to the target table.
|
Datasource:o.ora |
|
|
|
scott.emp !dml(d) |
scott.emp |
prodsys@o.sysdb |
Example 2
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.
|
Datasource:o.ora |
|
|
|
scott.stock !dml(d,i) (ID, name) |
scott.stock (SKU, prod) |
sys2@o.sysdb |
View current DML filters
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" prodsys@o.proddb
Filter out >>>>> DELETES
Unique Key : (EMPLOYEE_ID)
Restrictions
Filter DML related to specific Oracle objects from replication
You can prevent SharePlex from replicating sequences and SQL*Loader direct-path loads. By default the replication of these objects is enabled.
| Sequences |
SP_OCT_REPLICATE_SEQUENCES |
0 |
| SQL*Loader direct-path loads |
SP_OCT_REPLICATE_DLOAD |
0 |
Filter DML Operations for PostgreSQL Database
You can configure SharePlex to filter out the PostgreSQL DML type (INSERT, UPDATE, DELETE) from replication when wildcarding is being used.
Filter out a DML type
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.
Configure a DML filter
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.
!dml(DML_type[,DML_type][,...])
Where DML_type is one of the following:
| i |
INSERT |
| d |
DELETE |
| u |
UPDATE |
Examples
Example 1
The following example filters DELETE operations from being replicated to the target table.
|
Datasource:r.dbname |
|
|
|
scott.emp !dml(d) |
scott.emp |
prodsys@r.dbname |
Example 2
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.
|
Datasource:r.dbname |
|
|
|
scott.stock !dml(d,i) !key (EMPLOYEE_ID) |
scott.stock |
sys2@r.dbname |
View current DML filters
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 detail
7: "SCOTT"."EMP" "SCOTT"."EMP" prodsys@o.proddb
Filter out >>>>> DELETES INSERTS
Unique Key : (EMPLOYEE_ID)
Restriction
If there are multiple specifications of a source table in the configuration file, the DML filter specification specified in the last line for the table is considered.
Map source columns to target columns
Map Source and Target Columns
When source and target column names are different, you can specify an explicit column mapping in the configuration file, to ensure that Post applies row data to the correct target columns.
Guidelines for using column mapping
-
To use column mapping, you must map every column in a source table to a column in the target table, even if only some source and target names are different. When some columns are mapped but not others, the entry is considered to be a column partition for vertically partitioned replication, and data changes for non-listed columns are not replicated.
- If the spelling case is different between the source and target column names, enclose them within quotes.
- You can use horizontally partitioned replication and column mapping for the same source table, but you cannot combine column mapping with vertically partitioned replication.
- A target table can have more columns than the source table, but there must at least be a target column for every source column.
- ALTER TABLE to add a column to a table that is configured with column mapping is not supported.
Configure column mapping
The following syntax creates a column map. For more information, see Configure SharePlex to Replicate Data.
| datasource_specification |
|
|
| src_owner.table (src_col,src_col,...) |
tgt_owner.table (tgt_col,tgt_col,...) |
routing_map |
| datasource_specification |
The datasource specification. For more information, see Database Specifications in a Configuration File. |
| src_owner.table and tgt_owner.table |
The specifications for the source and target tables, respectively. For more information, see Create a Configuration File. |
|
(src_col,src_col,...) |
A list of the source columns.
Follow these rules to specify a column list:
- 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 column list is 174820 bytes (the maximum line length allowed in a configuration file).
|
| (tgt_col,tgt_col,...) |
A list of the target columns.
- 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 map 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.
- The syntax rules for the source list also apply to the target list.
|
| routing_map |
The routing map. For more information, see Routing Specifications in a Configuration File. |
Configuration example
This example contains no case-sensitive columns.
|
Datasource o.oraA |
|
|
|
sales.prod (ID,name,vendor) |
mfg.prod (UPC,product,supplier) |
sysB@o.oraB |
This example contains case-sensitive columns.
|
Datasource o.oraA |
|
|
|
sales.prod (ID,"name",vendor) |
mfg.prod (UPC,"product",supplier) |
sysB@o.oraB |
