You must configure a mapping that contains a Target transformation for a Db2 for z/OS Bulk Load target and map it to a Source 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 Target transformation in a mapping, specify a name for the transformation, a Db2 for z/OS Bulk Load connection, the target type, and the table for the Db2 target object. You can also set some additional target properties. The PWX Bulk Metadata Adapter retrieves metadata for the target table from the Db2 database catalog.
A mapping must contain at least one Source transformation mapped to one Target transformation.
When you define a Source transformation, specify a name for the transformation, a connection to the source and the source 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.
Targets in mappings with Db2 for z/OS Bulk Load 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 Bulk Load 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 for z/OS Bulk Load.
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.
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.
The following table describes the advanced target properties that appear under Advanced:
Advanced Property
Description
File Name
Data set name or high-level qualifier Db2 for z/OS Bulk Load Connector uses to create temporary files for bulk data load operations of the DB2 LOAD utility.
If you need to create a data file for a single partition of a partitioned table, use the following syntax:
filename/partxxxx
The xxxx variable is the partition number.
Space Units
The type of space units that Db2 for z/OS Bulk Load Connector uses when dynamically allocating target data sets on z/OS, if you do not specify space information
Primary Space
The primary space on z/OS to use for LOAD operations. Default is 0.
Secondary Space
The secondary space on z/OS to use for LOAD operations. Default is 0.
Delete Temporary Files
How the Db2 for z/OS Bulk Load Connector should handle the temporary files it creates with the DB2 LOAD utility to load data into a DB2 table.
Select one of the following values:
- NO does not delete the temporary files.
- BEFORE deletes the temporary files before running the utility.
- AFTER SUCCESS ONLY deletes the temporary files after running the utility if it ends with a return code 0.
- AFTER deletes the temporary files after running the utility.
JCL Template
The name of the JCL template for the DB2 LOAD utility on the PowerExchange target system.
CTL Template
The name of the control file template for the DB2 LOAD utility on the PowerExchange on the target system.
Load Options
Indicates how the data that PowerExchange provides to the DB2 LOAD utility is loaded into a DB2 table.
Select one of the following values:
- RESUME. Generate a LOAD RESUME statement.
- REPLACE. Generate a LOAD REPLACE statement.
Mode Type
Indicates how PowerExchange runs the DB2 LOAD utility to load data into a DB2 table.
Select one of the following values:
- TASK. Runs the LOAD utility as a subtask under the PowerExchange Listener.
- JOB. Submits a separate job to run the DB2 LOAD utility.
- NOSUBMIT. Create the files and JCL to run the DB2 LOAD utility (unless Mode Time is set to DATAONLY) but do not submit the load job. You must submit the job manually.
Mode Time
Determines how PowerExchange handles the execution of the DB2 LOAD utility.
Select one of the following values:
- WAIT. Wait for the job to end before returning control to PowerCenter. This option can be specified only when the Mode Type is JOB or TASK.
- NO WAIT. Return to PowerCenter without waiting for the job to end. This option can be specified only when Mode Type is set to JOB or NOSUBMIT.
- TIMED. Wait the number of seconds that is specified in the Time attribute before returning control to PowerCenter. The TIMED option can be specified only when Mode Type is set to JOB.
- DATAONLY. Create the data file only. Do not create the files and JCL for running the DB2 LOAD utility. Usually, this option is used when Mode Type is set to NOSUBMIT.
Default is WAIT.
Time
The wait time, in seconds, that is in effect when you set Mode Type to JOB and Mode Time to TIMED.
Valid values are 1 to 99998.
Default is 0.
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.
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:
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.