IMS Connector Guide > Mappings and mapping tasks with IMS Connector > Mapping configuration

Mapping configuration

You must configure a mapping that contains a source transformation for an IMS 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 IMS 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 sources in a mapping, all of the Source transformations must use the same IMS 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 IMS connection that can connect to the PowerExchange Listener on the PowerExchange Logger system, the source type, and the extraction map for the IMS source object. You can also set some additional source properties. The PWX Metadata Adapter retrieves metadata for the source table from the PowerExchange 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. 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.

IMS source properties in mappings

When you create a mapping, you configure a Source transformation for a IMS 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 IMS 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.

Note: The Filter and Sort options under Query Options are not supported for IMS 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 IMS source properties under Details:

Property	Description
Connection	Required. Select a source connection of the type IMS, 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 Parameter to use a parameter for the source object or select Multi Group. To define a parameter, select Parameter and then specify the parameter in the Parameter property. To read data from a nonrelational data source and write multiple record types to a nonrelational target, select Multi Group.
Schema, Data Map, and Tables	Click the pencil icon to specify the source schema, a data map if available, and one or more tables to use for the mapping. For multiple record write, select Use Sequence Fields (Select All Tables) on the Select Source Tables dialog to preserve the data sequence as it passes to the target. When you select this option, all non-complex tables are automatically selected. Complex tables are not selected.
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.

The following table describes the optional source properties under Advanced:

Property	Description
Map Schema Override	A schema name that overrides the default schema for the IMS source.
Map Name Override	A data map name that overrides the default data map for the IMS source.
IMS Unload File Name	An IMS source UNLOAD file name.
IMS AM Override	An access method used by the IMS source. The value you select overrides the default value. Select DL/1 or ODBA.
IMS SSID Override	An IMS SSID that overrides the default SSID.
IMS PSBNAME Override	An IMS PSB name that overrides the default name.
IMS PCBNAME Override	An IMS PCB name that overrides the default name.
IMS PCBNUMBER Override	An IMS PCB number that overrides the default name.
Flush After N Blocks	The maximum number of block flushes that can occur across all groups without a given block being flushed. Define this property to ensure that all blocks are flushed at regular intervals.
Filter Overrides	One or more conditions that filter the source records that the PWX 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 Reader uses to read the 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 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 Reader uses to retrieve 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 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 IMS connections.

Targets in mappings with IMS 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 by IMS 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.

IMS targets in a mapping

To write data to an IMS database, configure an IMS object as the Target transformation in a mapping.

Specify the name and description of the IMS target. Configure the target and advanced properties for the target object.

The following table describes the target properties under Details:

Property	Description
Connection	Select an existing IMS target connection, or create a target connection that has a type of IMS. 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 to specify a target table or select Multi Group to write multiple record types to an IMS target. If you selected Multi Group and Use Sequence Fields (Select All Tables) for the source, make sure Use Sequence Fields (Select All Tables) is also selected for the target. This setting is required for multiple-record write.
Object	Click Select. In the Target Object dialog box, select Existing and then select an existing target table.
Operation	Select the target operation. You can perform the following operations on an IMS target: - Insert - Update - Upsert - Delete - Data Driven

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 SQL Override	Overrides the default Update SQL that is sent to PowerExchange.
Map Schema Override	A schema name that overrides the schema name in the PowerExchange data map.
Map Name Override	Overrides the target PowerExchange data map name.
Insert SQL override	Overrides the default Insert SQL sent to PowerExchange.
Delete SQL override	Overrides the default Delete SQL that is sent to PowerExchange.
Pre SQL run once per Connection	Runs the SQL that you specify in the Pre SQL attribute only once for a connection. Select this attribute in either of the following cases: - In the Pre SQL attribute for a session that uses writer partitioning, you specify a SQL statement such as CREATEFILE that can run only once for the session. If you do not select Pre SQL run once per Connection, the session tries to run the statement once for each partition. - In the Pre SQL attribute for a session that performs a multiple-record write, you specify a CREATEFILE statement that creates a new generation of a GDG or creates an empty file. If you do not select Pre SQL run once per Connection, the session creates a generation or tries to create a new empty file for each record that the session writes.
Truncate Target	Truncates, or deletes, table contents before loading new data.
IMS AM Override	Overrides the IMS access method in the imported data map for the target with the other allowable access method. The session then uses the override access method at run time. - If you imported a target data map that specifies the DL/1 BATCH access method, enter O to override it with the IMS ODBA access method. For ODBA access, you must also specify the IMS PSBNAME Override and IMS PCBNAME Override attributes. - If you imported a target data map that specifies the IMS ODBA access method, enter D to override it with the DL/1 BATCH access method, which provides DL/I or BMP access. You must also specify the IMS PCBNUMBER Override attribute. Important: Before you run the session with an access method override, ensure that you complete the PowerExchange configuration tasks for the new access method. For example, if the override is DL/1 BATCH, you must configure LISTENER and NETPORT statements in the DBMOVER member and configure the netport JCL. If the override is IMS ODBA, you must perform other configuration tasks. For more information, see "IMS Data Maps" in the PowerExchange Navigator User Guide.
IMS SSID Override	If you imported an IMS ODBA data map for the target and did not override the access method, use this value to override the IMS subsystem ID (SSID). If you specified ODBA access as an override in the IMS AM Override attribute, you must enter this value. An SSID is required for ODBA access. If you use the IMS DL/1 BATCH access method and a BMP netport job, you can use this override with the %IMSID substitution variable in the netport JCL. This override replaces the substitution variable to specify the IMS SSID to use for the session. By using the substitution variable and override together, you can use the same netport JCL to access multiple IMS environments, such as development, testing, and production environments. Note: An IMS SSID is not required for DL/I batch access to IMS data or for access to an IMS unload file.
IMS PSBName Override	If you imported an IMS ODBA data map for the target and did not override the access method, this value overrides the PSB name from the data map. If you specified ODBA access as an override in the IMS AM Override attribute, you must enter this value. A PSB name is required for ODBA access. If you use DL/I batch or BMP access and specify this override, you must also specify the PSB=%PSBNAME substitution variable in the netport JCL. The override value then replaces the substitution variable in the JCL. If you specify the PSB=%1 substitution variable instead of PSB=%PSBNAME in the netport JCL, the session uses the PSB name in the NETPORT statement, if specified. In this case, you need a separate NETPORT statement for each PSB. To avoid exceeding the limit of ten NETPORT statements, use this override with %PSBNAME substitution variable instead.
IMS PCBNAME Override	If you imported an IMS ODBA data map for the target and did not override the access method, this value overrides the PCB name from the data map. If you specified ODBA access as an override in the IMS AM Override attribute, you must enter this value. A PCB name is required for ODBA access. A PCB name is not used for DL/I or BMP access.
IMS PCBNUMBER Override	If you imported a DL/1 BATCH data map for the target and did not override the access method, this value overrides the PCB number from the data map. If you specified DL/I or BMP access as an override in the IMS AM Override attribute, you must enter this value. A PCB number is required for DL/I or BMP access. A PCB number is not used for IMS ODBA access.
IMS Unload File Override	Required for a multiple-record write to an IMS unload file.

.

Lookup Transformation

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

For more information, see Cloud Data Integration Transformations.

Multiple-record writes to nonrelational targets

You can use a multiple-record data map to read data from a nonrelational data source and write the multiple record types in a single pass to a nonrelational target. This process is called a multiple-record write.

When Cloud Data Integration performs a multiple-record write, it preserves source sequencing information. To enable multiple-record writes with sequencing, select the Use Sequence Fields (Select All Tables) option in the Select Source Tables dialog box for both the source and target definition. When you select these options, Cloud Data Integration generates primary key (DTL_PK) and foreign key fields (DTL_FK) for each group in the source and target definitions, which enables Cloud Data Integration to reassemble the data in the correct sequence at the target.

You can perform multiple-record writes with the following types of sources and targets:

•Source and target IMS unload and VSAM data sets on z/OS
•Any combination of the following types of nonrelational sources and targets:

- Files on Linux, UNIX, or Windows
- Files on IBM i