Oracle CDC V2 Connector > Mappings and mapping tasks with Oracle CDC V2 Connector > Mapping configuration

Mapping configuration

You must configure a mapping that contains a Source transformation for an Oracle CDC source and map it to a Target transformation.

To begin creating a mapping in Data Integration, click New > Mappings > Mapping and then click Create. In the New Mapping dialog box, click Mapping > Continue.

A mapping must contain at least one Source transformation mapped to one Target transformation. Alternatively, a mapping can contain multiple Oracle CDC sources and multiple targets. For example, you can define multiple one-to-one mappings, map one source to multiple targets, or map multiple sources to one target. To map multiple sources to the same target, you can create multiple one-to-one mappings that map each source separately to the same target. In this case, each source must have a different primary key value.

Important: If you include multiple CDC sources in a mapping, all of the Source transformations must use the same Oracle CDC connection, which connects to the same PowerExchange Listener and source location.

A mapping can also contain other transformation types, such as a Joiner transformation or Lookup transformation, as needed. If you include a Lookup transformation, make sure that you select either the Lookup Source is Static or Lookup Cache Persistent advanced property for the transformation. Otherwise, any associated mapping task for which the Recovery Strategy advanced session property is set to Resume from last checkpoint, as recommended, will fail.

When you define a Source transformation in a mapping, specify a name for the transformation, an Oracle CDC connection that can connect to the PowerExchange Listener on the PowerExchange Logger system, the source type, and the extraction map for the Oracle CDC source object. You can also set some additional source properties. The PWX CDC Metadata Adapter retrieves metadata for the source table from the PowerExchange-generated extraction map, including the field definitions.

When you define a Target transformation, specify a name for the transformation, a connection to the target, the target type, and the operation type of Data Driven. In the user's environment, the target can reside on premises or in a cloud. If you do not want to send all of the incoming fields to the target, you can define one or more exclusion field rules. As part of configuring the target, you must also map the incoming fields to the target fields. You can map fields automatically based on the field names or manually map them.

Instead of selecting specific connections and objects in the Source and Target transformations, you can define a parameter for the connection, the source or target object, or both. You then must enter a specific connection or object in the associated mapping tasks. If you want to use a parameter for the source or target connection, you must perform one of the following actions before the mapping can be successfully validated:

•Set the Source Type or Target Type field to Parameter and then enter a parameter for the source or target object in the Parameter field. On the Field Mapping tab of the Target transformation, you must set the map option to Automatic for the mapping to be validated.
•If you want to use a specific source or target object with a connection parameter, first select a specific connection, set the Source Type or Target Type field to Single Object, select a source or target object in the Object field, validate the mapping, and then enter the connection parameter.

Tip: If you want to validate the source-to-target field mappings, map the fields in the Target transformation before validating the mapping. After the mapping is confirmed as valid, enter connection parameters.

You can also completely or partially parameterize field mappings, and then define specific mappings in the mapping tasks.

Oracle CDC source properties in mappings

When you create a mapping, you configure a Source transformation for an Oracle CDC V2 source and set some source properties for the Source transformation.

Click the Source transformation box on the mapping canvas. Then configure the source properties in the Source Properties panel below the mapping canvas.

If you have multiple Source transformations in a mapping, configure source properties for each source. If you use a specific connection instead of a connection parameter, enter the same connection for all sources.

Note: If a mapping includes source tables or columns that have special characters in their names, the associated mapping task fails because it cannot import the source metadata. Special characters include s #, $, @, %, *, !, and ~. Therefore, ensure that none of the source tables and mapped columns have names with any special character. Rename the tables and columns if necessary.

The Source Properties panel contains the following tabs on which you can enter information:

•General tab. Specify a user-defined name and optional description for the Oracle CDC source.
•Source tab. Configure the source properties under Details and under Advanced. The Advanced properties are available only if you enter a specific CDC connection for the source. If you enter a parameter for the source connection, the advanced properties are not displayed. However, you can define these properties in the associated mapping task after you select a specific source connection for the task.

Note: The Filter and Sort options under Query Options are not supported for CDC connections. If you specify a parameter for the source connection, the Sort and Filter options are available but will be ignored if you configure them.

The following table describes the Oracle CDC source properties under Details:

Property	Description
Connection	Required. Select a source connection of the type Oracle CDC, or click New Connection to create one. Alternatively, you can define a connection parameter in the mapping and enter a specific connection in each mapping task that is associated with the mapping. If you want to use a connection parameter with a specific source object, you must first select a specific connection so that you can access the source to select the source object in the Object field. After you select the source object, define the connection parameter. If you want to use parameters for both the connection and source object, you do not need to first select a specific connection. You can just define the parameters in either order. To define a connection parameter, click New Parameter next to the Connection property and enter the following information: - Enter a parameter name that begins with a letter and is up to 200 characters in length, including @, #, _, and alphanumeric characters. - In the Type property, verify that connection is selected. - Leave the Connection Type property blank. After you click OK, the parameter appears in the Connection property.
Source Type	Required. Select Single Object to be able to select an extraction map for an Oracle source table in the Object property. Alternatively, you can define a parameter for the source in the mapping and enter a specific source object in each mapping task that is associated with the mapping. To define a source object parameter, select Parameter as the source type, and then specify the parameter in the Parameter property.
Object	Click Select. In the Select Source Object dialog box, under Packages, click a listed value. This value is the first part of the generated extraction map name. The right pane lists extraction maps that have names matching the selected package value. Select an extraction map. You can use only extraction maps that PowerExchange generated by default for capture registrations. These extraction maps have names that begin with "d." The extraction-map metadata is stored in the Informatica Intelligent Cloud Services repository in the cloud. The Object property appears only if you select Single Object as the source type. Note: The Preview Data feature is unavailable for CDC source objects.
Parameter	Select a parameter for the source object, or click New Parameter to define a new parameter for the source object. The Parameter property appears only if you select Parameter as the source type.

The following table describes the optional source properties under Advanced:

Advanced Property	Description
Connection Overrides	A variable or parameter file that overrides attributes at the mapping level for one or more sources. Use a semicolon (;) as the separator. For example, to override attributes by using a parameter file, use the following syntax: RestartToken FileName=D:\abcagent\apps\Data_Integration_Server\data\restart\test.dat;APPLICATION=testapp Overrides can be parameterized and can refer to other parameters. For example: RestartToken File Name=$PMRootDir/restart/restartfile.txt If you have more than one CDC source in the mapping, you only need to define the override in one of the sources. The override is automatically applied to all sources. Note: If you provide an override value for more than one source, all values must match to avoid a runtime error. For more information, see Connection overrides reference.
Map Schema Override	A schema name that overrides the schema name in the PowerExchange data map.
Map Name Override	A map name that overrides the name defined in the PowerExchange data map.
Source Schema Override	If you created a single capture registration for a set of source tables that have the same table name but different schemas and defined an override schema name in a PowerExchange Logger group definition file, enter that override schema name. Otherwise, PowerExchange cannot extract the change data for the source table that has the override schema from the log files. For more information about PowerExchange Logger group definitions, see the PowerExchange CDC Guide for Linux, UNIX, and Windows.
Filter Overrides	One or more conditions that filter the source change records that the PWX CDC Reader retrieves from the PowerExchange Logger log files. Based on the filter conditions, a WHERE clause is appended to the default SQL SELECT query that the PWX CDC Reader uses to read the change records. You must use the nonrelational SQL syntax that PowerExchange supports for comparison and LIKE operators. For more information, see the "PowerExchange Nonrelational SQL" chapter in the PowerExchange Reference Manual. If the filter includes a date or timestamp column, ensure that the range of years in the column data is within the range of years for PowerExchange data filtering, as controlled by the DATERANGE statement in the DBMOVER configuration file. The default range is 1800 to 2200. For example, to select change records in which the TYPE column has a value of A or D, specify the following condition: TYPE='A' or TYPE='D' Maximum length of the entire SELECT statement that PowerExchange builds, including the WHERE clause with the filter conditions, is 8192 bytes. Note: If you specify the Filter Overrides attribute and also specify an SQL Query Override attribute value that contains a filtering WHERE clause, the resulting SELECT statement contains a WHERE clause that uses the AND operator to associate the Filter Overrides filter conditions with the SQL Query Override conditions. For example: SELECT * from schema.table WHERE Filter_Overrides_conditions AND SQL_Query_Override_conditions
SQL Query Override	An SQL statement that overrides the default SQL query that the PWX CDC Reader uses to retrieve change records from PowerExchange. You must use the nonrelational SQL syntax that PowerExchange supports. For more information, see the "PowerExchange Nonrelational SQL" chapter in the PowerExchange Reference Manual. If the override statement includes a date or timestamp column, ensure that the range of years in the column data is within the range of years for PowerExchange data checking and filtering, as controlled by the DATERANGE statement in the DBMOVER configuration file. The default range is 1800 to 2200. For example, to select change records for the USER source table when the TYPE column has the value of A or D, specify the following query: SELECT ID, NAME from schema.extract_map.USER where TYPE=‘A’ or TYPE=‘D’; Maximum length of the SELECT query override statement is 8192 bytes. Note: If you specify an SQL Query Override attribute value that contains a filtering WHERE clause and also specify the Filter Overrides attribute, the resulting SELECT statement contains a WHERE clause that uses the AND operator to associate the SQL Query Override conditions with the Filter Overrides filter conditions. For example: SELECT * from schema.table WHERE Filter_Overrides_conditions AND SQL_Query_Override_conditions
Tracing Level	The level of detail that appears in the session log for a Source transformation. Select one of the following options, which are listed in order of increasing detail: - Terse - Normal - Verbose Initialization - Verbose Data Default is Normal.

•Fields tab. Select the fields that you want to use in the data flow. You can edit field metadata such as precision and scale if necessary.

Note: Ignore the Partitions tab. Key-range partitioning is not supported for CDC connections.

Targets in mappings with Oracle CDC sources

To configure a target in a mapping, click the Target transformation box on the mapping canvas. Then configure the target properties in the Target Properties panel below the mapping canvas.

Note: To determine which target types are supported for the CDC Connector, see Supported targets.

In the Target Properties panel, enter information on the following tabs:

•General tab. Specify a user-defined name and optional description for the target.
•Incoming Fields tab. Optionally review the included fields and define field exclusion rules and renaming criteria.
•Target tab. Configure the target properties under Details and under Advanced. The advanced properties are available only if you enter a specific connection for the target.

The following table describes the target properties under Details:

Property	Description
Connection	Select an existing target connection, or create a target connection. Alternatively, you can use a connection parameter in the mapping and then enter a specific connection in each mapping task that is associated with the mapping. If you want to use a connection parameter with a specific target object, you must first select a specific connection so that you can access the target to select the target object in the Object field. After you select the target object, define the connection parameter. If you want to use parameters for both the connection and target object, you do not need to first select a specific connection. You can just define the parameters in either order. To define a connection parameter, click New Parameter and enter the following information: - Enter a parameter name that begins with a letter and is up to 200 characters in length, including @, #, _, and alphanumeric characters. - In the Type property, verify that connection is selected. - Leave the Connection Type property blank.
Target Type	Select Single Object if you want to specify a target table. Alternatively, select Parameter and then specify an input parameter for the target object in the Parameter property. When you use a parameter, you specify the target table for the parameterized object on the Targets page of each mapping task associated with the mapping.
Object	Click Select. In the Target Object dialog box, select Existing and then select an existing target table. Alternatively, select Create New at Runtime and enter the name of a target table to generate at runtime. However, Informatica recommends that you do not use this option. The generated table might not be completely materialized with data the first time the mapping task runs. Also, with this setting, you cannot set the Operation property to Data Driven, which is required to properly handle subsequent insert, update, and delete records. After the table is generated, you must return to the Object field, select Existing and the generated target table, and then set the Operation property to Data Driven. Note: A generated target table contains the PowerExchange-generated DTL__ metadata fields from the source. If you do not want these columns on the target, you can configure a field rule that excludes the columns that have the "DTL__" prefix.
Parameter	Select or create a parameter for the target object. Appears only when the target type is Parameter.
Operation	Select Data Driven to properly handle insert, update, and delete records from the source. Note: By default, inserts, updates, and deletes are applied as inserts, updates, and deletes, respectively.
Data Driven Condition	Enables you to define expressions that flag rows for an insert, update, delete, or reject operation. Appears only when the operation type is Data Driven but is not required for this operation type.
Update Columns	Click Add to select the columns that you want to use as a logical primary key for performing update, upsert, and delete operations on the target. Appears only when the operation type is Data Driven. This field is not required if the target table already has a primary key.
Truncate target	Select this option if you want to clear the target table each time the mapping task runs. However, if you select this option and set the Operation property to Data Driven, update operations on the target will fail.

The following table describes the advanced target properties that appear under Advanced:

Advanced Property	Description
Forward Rejected Rows	Select this option to have the Target transformation pass rejected records to a reject file that is generated in the following directory: \agent_install_directory\apps\Data_Integration_Server\data\error Clear this option to ignore the rejected records. Note: This field is not displayed if a parameter is specified for the target connection.
Pre SQL	SQL statements that run on the target before the extracted data is written to a target. Maximum length is 5000 characters.
Post SQL	SQL statements that run on the target after the extracted data is written to a target. Maximum length is 5000 characters.
Update Override	An override SQL update statement to update the data in a target table. The override SQL update statement you specify overrides the default update statements that the Secure Agent uses to update targets based on key columns. You define an override update statement to update target tables based on non-key columns. In the override statement, you must enclose all reserved words in quotation marks.

•Target Fields tab. Optionally add fields, delete fields, or edit field metadata such as native data type, precision, and scale.
•Field Mapping tab. Map the incoming fields to the target fields. You can manually map an incoming field to a target field or automatically map fields based on the field names. If you entered a connection parameter, you must select Automatic as the field map option to be able to validate the mapping. Alternatively, you can completely or partially parameterize field mappings, and then define the specific field mappings in the mapping task. For more information, see Transformations > Target Transformation > Target Transformation Field Mappings in the Data Integration Help.