IDMS Connector > Mappings and mapping tasks with IDMS Connector > Mapping configuration

Mapping configuration

You must configure a mapping that contains a Source transformation for an IDMS 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.

When you define a Source transformation in a mapping, specify a name for the transformation, an IDMS connection, the source type, and the data map tables for the IDMS source object. You can also set some additional source properties. The PWX Bulk Metadata Adapter retrieves metadata for the source table from the PowerExchange-generated data map, including the field definitions.

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 map them manually.

IDMS source properties in mappings

When you create a mapping, you configure a Source transformation for an IDMS 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 IDMS source.
•Source tab. Configure the source properties under Details and under Advanced. The advanced properties are available only if you enter a specific 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.

The following table describes the IDMS source properties under Details:

Property	Description
Connection	Required. Select a source connection of the type IDMS, 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. 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. Multi Group is selected by default. Alternatively, you can define a parameter for the source object in the mapping and enter a specific source object in each mapping task that is associated with the mapping. To define a parameter, select Parameter as the source type, and then specify the parameter in the Parameter property.
Parameter	Parameter to use for the source object. This field only appears when you select Parameter as the source type. To parameterize a source object, click New Parameter. Enter the parameter name and then select the type multi group data object. This type of parameter can accept a set of groups. From the New Input Parameter dialog box, you can add or delete groups for the parameter. When you create a multi-group data object parameter, the corresponding links are also created. You can use these links in the mapping to connect to downstream transformations.
Schema, Data Map, and Tables	On the Source tab, click the pencil icon to select a schema, a data map, or table. Note: The Preview Data feature is not supported.

Note: The Filter and Sort options under Query Options are not supported.

The following table describes the optional source properties under Advanced:

Advanced Property	Description
Map Name Override	Overrides the data map name of the source PowerExchange data map.
Map Schema Override	Overrides the schema name in the source PowerExchange data map.
Filter Overrides	One or more conditions that filter the source records that the PWX Bulk Reader retrieves from the PowerExchange Listener. Based on the filter conditions, a WHERE clause is appended to the default SQL SELECT query that the PWX Bulk Reader uses to read the records. IDMS Connector supports two forms of filter condition syntax. For single record source definitions such as single record nonrelational data maps, specify a single filter condition statement. You can specify a single filter condition or join numerous filter conditions by using the conditional operands that PowerExchange supports for NRDB SQL statements. For example: column1 is NULL and column2=’A’ For multi-record nonrelational source definitions, you can also use the following syntax: group_name1=filter_condition;group_name2=filter_condition;... Use the group_name form to specify filter conditions for one or more record types in a multi-record source definition. To apply a filter condition to all records in a multi-record source definition, use a single filter condition without group_name. You cannot combine single filter conditions with group_name conditions. 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 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 Bulk Reader uses to retrieve records from PowerExchange. The connector replaces the default SQL query with the SQL statement that you enter and passes the SQL statement to Cloud Data Integration for processing. 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 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 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 For a multiple-record source, use the following syntax: group_name1=sql_query_override1; group_name2=sql_query_override2;... For example, you can select only records with ID column values that contain DBA for a multi-record source with two records called USER1 and USER2 by specifying the following SQL query override: USER1=Select ID, NAME from USER1 where ID='DBA'; USER2=Select ID, NAME from USER2 where ID='DBA';
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.

Targets in mappings with IDMS sources

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

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

•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. Target properties vary based on the supported target that you select.
•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.

For more information, see the appropriate target connector guide for target for specific instructions.

Lookup Transformation

You can configure a cached Lookup transformation when you use an IDMS connection in a mapping to return data from an IDMS source based on a specified lookup condition. However, the IDMS Connector does not support uncached lookup.

For more information, see Cloud Data Integration Transformations.