Mapping configuration

Sequential File source properties in mappings

• •General tab. Specify a user-defined name and optional description for the sequential file 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.
The following table describes the sequential file source properties under Details:

Property	Description
Connection	Required. Select a source connection of the type Sequential File, 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 tables from the Select Source Tables dialog. After you select the source tables, 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. To use a parameter for the source object, select Parameter. To select a data map for a sequential file source or to read data from a nonrelational data source and write multiple record types to a nonrelational target, select Multi Group.
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 to select a schema, a data map, or table. 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. Note: The Preview Data feature is not supported.

The following table describes the optional source properties under Advanced:

Advanced Property	Description
Filelist File	For sequential files, select this check box only if you entered a filelist file in the File Name Override field. You must use a filelist file. A filelist file specifies the names of the files from which source data is to be retrieved. The PowerExchange Listener retrieves data from all of the files named in the filelist file in the order in which the files are listed. To request file list processing for a sequential source, configure the following session attributes: - Specify the filelist file name in the File Name Override attribute. - Select the Filelist File option.
File Name Override	For sequential files, overrides the data set or file name in the PowerExchange data map. Enter the complete data set or file name. If you select the Filelist File check box, enter the name of a filelist file in this attribute. A filelist file is a list of files.
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. Sequential File 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
Flush After N Blocks	For multiple-record sources, specifies the maximum number of block flushes that can occur without any one block being flushed. For bulk multiple-record sources, by default, PWXPC flushes blocks of data only when the buffers are completely full or at end-of-file. If some record types do not have as much data as others, flushing might not occur often. In this case, the record types might not have data on the target for a long time, thereby blocking flushes on the writer side. To ensure that buffers for all record types are flushed at a regular interval, define this Flush After N Blocks session property. This property specifies the maximum number of block flushes that can occur across all record types without any one block being flushed. A value of zero disables this feature and causes flushing to occur only when blocks are full. Valid values for the property are -1 to 100000. The default value of -1 works in the following manner: For all multiple-record sources that do not use sequence fields, process the same as Flush After N Blocks = 0, which disables this feature and flushes only when blocks are full. For all multiple-record sources that use sequence fields, use Flush After N Blocks = 7 * (number of record types in the source).
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.
PWX Partition Strategy	For sequential files, specifies one of the following partitioning strategies: Single Connection. PowerExchange creates a single connection to the data source. Any overrides specified for the first partition are used for all partitions. With this option, if you specify any overrides for other partitions that differ from the overrides for the first partition, the session fails with an error message. Overrides Driven. If the specified overrides are the same for all partitions, PowerExchange creates a single connection to the data source. If the overrides are not identical for all partitions, PowerExchange creates multiple connections.
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.

Targets in mappings with Sequential File sources

• •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 Oracle or SQL Server target connection, or create a target connection that has a type of Oracle or SQL Server. 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. Note: You can only insert to a sequential target, you cannot perform an update or delete operation.
Target Type	Select Single Object if you want to specify a target table. 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. Select Multi Group to write multiple record types to a sequential 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. 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 records 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. Updates for a sequential file are not supported.

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.
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.
File Name Override	Overrides the data set or file name in the PowerExchange data map. Enter the complete data set or file name. For i5/OS, use the following format: library_name/file_name
Insert SQL override	Overrides the default Insert SQL 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.
PWX Partition Strategy	Specify one of the following partitioning strategies: - Single Connection. PowerExchange creates a single connection to the data source. Any overrides specified for the first partition are used for all partitions. With this option, if you specify any overrides for other partitions that differ from the overrides for the first partition, the session fails with an error message. - Overrides Driven. If the specified overrides are the same for all partitions, PowerExchange creates a single connection to the data source. If the overrides are not identical for all partitions, PowerExchange creates multiple connections
File Disposition	For the SEQ target type on z/OS, the z/OS data set disposition. Valid values: - OLD - SHR - NEW - MOD Default is MOD if the data set exists, and NEW if it does not.
Space Units	For the SEQ target type on z/OS, the type of units for expressing primary or secondary space for z/OS data sets. Valid values are: - CYLINDER - TRACK This value is ignored if Disp is not MOD or NEW. Default is TRACK.
Primary Space Allocation	For the SEQ target type on z/OS, the primary space allocation, in the units specified in the Space attribute. This value is ignored if Disp is not MOD or NEW. Default is 1.
Secondary Space Allocation	For the SEQ target type on z/OS, the secondary space allocation, in the units specified in the Space attribute. This value is ignored if Disp is not MOD or NEW. Default is 1.
Record length	For the SEQ target type on z/OS, the data set logical record length. This value is ignored if Disp is not MOD or NEW. Default is 256. If you select VB for the Record Format value, specify the maximum number of data bytes in a logical record for LRECL. PowerExchange adds 4 to this value for the record descriptor word (RDW).
Block Size	For the SEQ target type on z/OS, the z/OS data set block size. Default is 0, which means use the best possible block size. If you select VB for the Record Format value, the actual block size might be up to four bytes greater than the value you specify for Block Size.
Record format	For the SEQ target type on z/OS, the z/OS record format. Valid values are F, V, FU, FB, VU, VB, FBA, and VBA. This value is ignored if DISP is not MOD or NEW.
Unit specification	For the SEQ target type on z/OS, the z/OS unit type.
Volume Serial	For the SEQ target type on z/OS, the volume serial number. This value is ignored if Disp is not MOD or NEW.
SMS Management Class	For the SEQ target type on z/OS, the SMS management class name. This value is ignored if Disp is not MOD or NEW.
SMS Data Class	For the SEQ target type on z/OS, the z/OS SMS data class name.
SMS Storage Class	For the SEQ target type on z/OS, the SMS storage class name. This value is ignored if Disp is not MOD or NEW.
Model DSCB Name	For the SEQ target type on z/OS, the MVS File allocation parameter.

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

Multiple-record writes to nonrelational 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