4.1 Understanding Mapping Groups
In Oracle GoldenGate Studio, the term mapping group is used to explain a collection of source and target table mappings and associated replication logic such as filtering and conflict detection rules. First, the tables must be drag and dropped or added using the keyboard shortcuts from the Resources Navigator to the Mapping Editor. Then, source and target table mapping associations can be automatically generated using Automap or created by drag-and-drop or using keyboard shortcuts from source to target and target to source. Additionally, wildcard characters (*) can be used in the mappings at the schema and table level. If you drag and drop the schema name it will automatically use a wildcard, indicating all tables. For more on wildcard logic see the Orcale GoldenGate Reference manual. For Oracle multitenant support you must edit the schema name manually to include the pluggable database name.
Only the metadata from the database connections are used when creating mapping groups. This means mapping groups are logical constructs which can be assigned to multiple replication paths. Mapping groups local to a solution can be associated with any replication path in that solution, while global mapping groups located in the global resource library, can be used for any replication path for any solution across all projects. When a mapping group is modified that change will be reflected in all replication paths referencing that mapping group. More than one mapping group can be assigned to a replication path.
For example, if you have 100 stores with the same application schema reporting back to a central data warehouse, you only have to define that mapping group once and you can apply it to all 100 replication paths. Any changes to that mapping group will only needs to be made once and will be reflected in all 100 replication paths.
4.1.1 Schema and Table Mapping
Schemas are not directly mapped to other schemas. Tables in the schemas are mapped other tables. To map all tables in a schema you can either highlight all of the tables in the global resource library and drag and drop them into the mapping editor or you can drag and drop the schema name, which will then use a wildcard (*) for all tables. If you're replicating DDL and want to capture new tables then always use a wildcard. Wildcard can also be used with schema names. However, when using a wildcard for schema names, some system schemas will automatically be excluded. To see which schema names will be implicitly excluded when using wildcards, refer Oracle GoldenGate installation document for that database. When using a wildcard for the schema name, the implicitly excluded schemas can be overridden by explicitly listing the schema names.
To map tables or wildcards you can click the Automap button and any unmapped target table will be mapped to a source table if the table names are the same regardless if they are in different schemas. Alternatively, in the mapping editor you can drag and drop source and target table names on each other to create the association.
Once tables are mapped you will see the Source table name in the Target column Mapped From. Only the Source table names are listed in this column but a tool tip will provide the source schema name. Double clicking the Target table name or Mapped From table name will take you to the column mapping editor.
In the schema and table mapping editor you can define properties and logic for the entire mapping, source tables, and target tables. The following figure and the table show which properties can be edited based on where you click in the mapping editor.
Figure 4-1 Schema and Table Mapping
| ID | UI Element | Properties |
|---|---|---|
|
1 |
Map Breadcrumbs |
Name and description, DDL replication, global column matching, DML conversions, and global replication error responses. |
|
2 |
Source schema name |
Database name, schema name, and exclude schema from source capture. The exclude schema is used when you have used a wildcard for all schemas but want to explicitly exclude certain schemas that would otherwise be included with the wildcard. |
|
3 |
Source table name |
Table name, exclude table from source capture, filter, Where clause, custom SQL execution, event actions, and additional custom properties. |
|
4 |
Target schema name |
Target schema name and exclude schema from target apply |
|
5 |
Target table name and Mapped From |
Target schema name and exclude table from target apply, exception handling, filter, DML conversion, Where clause, table specific replication error responses, custom SQL execution, coordinated apply thread (if applicable), coordinated apply thread range (if applicable), event actions, more settings, and additional custom properties. |
Note:
When you have more than one table mapped you will need to drill into column mapping and select the table name to edit all the properties except exclude property. This allows you to apply different logic to different tables.
4.1.2 Column Mapping
You can navigate to the column mapping from the table mapping editor. Double click the Target table name or the Mapped From column, or right-click anywhere on the target row that contains the table name and chose Edit Column Mapping from the context menu.
The Oracle GoldenGate runtime engine automatically maps matching column names so column mapping is not required. Column mapping works in the same way as table mapping and you can use Automap and drag-and-drop between Source and Target.
In the column mapping editor you can define properties and logic for Source and Target tables and columns. The following figure and the table show which properties can be edited based on where you click in the mapping editor. It also shows how to select individual table mappings when more than one table is mapped to a target table and how to navigate back to the schema and table mapping editor.
Note:
Selecting the table name in the column mapping editor provides the same options as clicking the table name in the schema and table mapping editor when there is only one source table mapped to the target table.
Figure 4-2 Column Mapping
| ID | UI Elements | Properties |
|---|---|---|
|
1 |
Map Breadcrumbs showing mapping group name |
Selecting here returns to the schema and table mapping editor. |
|
2 |
Map Breadcrumbs showing table mapping name |
If there is more than one source table mapped to this target table, selecting here provides a drop down list of those other mappings and you can navigate to them. |
|
3 |
Source table name |
Table name, exclude table from source capture, filter, Where clause, custom SQL execution, event actions, and additional custom properties |
|
4 |
Source column name |
Column name, exclude column from source capture, force column to be used as (part) of the primary key. |
|
5 |
Target table name |
Target table name, exclude table mapping from target apply, force specific columns to be used as the key, exception handling, conflict detection, conflict resolution, filter, DML conversion, Where clause, table specific replication error responses, custom SQL execution, coordinated apply thread (if applicable), coordinated apply thread range (if applicable), event actions, more settings, and additional custom properties. |
|
6 |
Target column name and Mapped From |
Column name, mapped from (which can contain source column names or custom logic), force column to be used as (part) of the primary key, enable this column for update conflict detection, and enable this column for delete conflict detection. |
Note:
Primary key column overrides, conflict detection, and conflict resolution are only present in the column mapping editor.
