Db2 for z/OS Image Copy Connector > Mappings and mapping tasks with Db2 for z/OS Image Copy Connector > Mapping configuration

Mapping configuration

You must configure a mapping that contains a Source transformation for a Db2 for z/OS Image Copy 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, a Db2 for z/OS Image Copy connection, the source type, and the table for the Db2 source object. You can also set some additional source properties. The PWX Bulk Metadata Adapter retrieves metadata for the source table from the Db2 database catalog.

A mapping must contain at least one Source transformation mapped to one Target transformation. Alternatively, a mapping can contain multiple Db2 for z/OS Image Copy 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 one 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.

When you define a Target transformation, specify a name for the transformation, a connection to the target and the target 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 map them manually.

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.

Db2 for z/OS Image Copy source properties in mappings

When you create a mapping, you configure a Source transformation for a Db2 for z/OS Image Copy 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.

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 Db2 for z/OS Image Copy 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 Db2 for z/OS Image Copy source properties under Details:

Property	Description
Connection	Required. Select a source connection of the type Db2 for zOS, 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 one of the following options: - Single Object lets you select a Db2 for z/OS Image Copy source object in the Object property. - Parameter selects an existing parameter for the source object, or allows you to define a new parameter for the mapping. The parameter defines a specific source object in each mapping task associated with the mapping. - Query lets you enter a custom SQL query for the source object.
Object	If you selected Single Object as the source type, you can select a Db2 for z/OS Image Copy source object. Click Select. Then in the Select Source Object dialog box, under Packages, click a listed value. This value is the schema name. The right pane lists the tables in the selected schema. The table metadata is stored in the Informatica Intelligent Cloud Services repository in the cloud. Note: The Preview Data feature is not supported for Db2 for z/OS Image Copy source and target objects.
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 data object. From the New Input Parameter dialog box, you can add a description, default value, and other information for the parameter.
Query	If you selected Query as the source type, you can enter a custom SQL query for the source. Select Define Query and enter the SQL query in the Edit Custom Query dialog box. Use SQL that is valid for the Db2 for z/OS Image Copy database. You can create custom queries to override the default SQL, to perform a complicated join of tables, to reduce the number of fields that enter the data flow, or to create columns for metadata. You can validate an SQL query when you add or enter it. Note: The Preview Data button in the Edit Custom Query dialog box is not supported. If you select Preview Data, Data Integration issues an error.
Query Options	You can configure the source transformation to filter or sort data before the data enters the data flow. Use the source query options to filter or sort source data. For more information, see Transformations.

The following table describes the optional source properties under Advanced:

Property	Description
Schema Name Override	Overrides the source schema name.
Table Name Override	Overrides the source table name.
DB2 Subsystem ID Override	Overrides the DB2 instance name in the PowerExchange data map.
Image Copy Dataset	Provides the image copy data set name. If not specified and the table is in a non-partitioned table space, the most current image copy data set with TYPE=FULL and SHRLEVEL=REFERENECE is used. If the table is in a partitioned table space, you must specify the Image Copy Dataset attribute.
Disable Consistency Checking	If cleared for a DB2 image copy source, PowerExchange reads the catalog to verify that the DSN of the specified image copy data set is defined with SHRLEVEL=REFERENCE and TYPE=FULL and is an image copy of the specified table. If the DSN is not defined with these properties, the session fails. If selected, PowerExchange reads the Image Copy Dataset regardless of the values of SHRLEVEL and TYPE and without verifying that the object ID in the image copy matches the object ID in the DB2 catalog.
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. Db2 Data Map 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 Override	The SQL statement to override the default query generated from the specified source type to read data from the source. You can partially parameterize SQL override with values specified in a parameter file. Ensure that the list of selected columns, data types, and the order of the columns that appear in the query matches the columns, data types, and order in which they appear in the source object. Note: SQL override is not applicable when you enable partitioning. If you specify an SQL override and configure partitioning, the mapping fails.
Tracing Level	Sets the amount of details that appear in the log file. You can choose terse, normal, verbose initialization, or verbose data.

•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 Db2 for z/OS Image Copy 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 the Db2 for z/OS Image Copy 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 Db2 target connection, or create a target connection that has a type of Db2. 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.
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.
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.
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 rows from the source. By default, inserts, updates, and deletes are applied as inserts, updates, and deletes, respectively.
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.

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

Advanced Property	Description
Truncate target table	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.
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 Db2 update statement to update the data in a Db2 target table. The override 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.
Forward Rejected Rows	Select this option to have the Target transformation pass rejected rows 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 rows. Note: This field is not displayed if a parameter is specified for the target connection.

•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.

Lookup Transformation

You can configure a cached Lookup transformation when you use a Db2 for z/OS Image Copy connection in a mapping to return data from a Db2 for z/OS Image Copy source based on a specified lookup condition. However, the Db2 for z/OS Image Copy Connector does not support uncached lookup.

For more information, see Cloud Data Integration Transformations.