VSAM Connector

Back Next

VSAM source properties in mappings

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

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 VSAM 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 Filter and Sort options under

Query Options

are not supported.

The following table describes the VSAM source properties under

Details

Property	Description
Connection	Required . Select a source connection of the type VSAM , 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 . To use a parameter for the source object, select Parameter . To read data from a VSAM data source and write multiple record types to a VSAM 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 VSAM source table, click New Parameter to create a parameter. Enter a 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. The Preview Data feature is not supported.

The following table describes the optional source properties under

Advanced

Advanced Property	Description
Filelist File	For VSAM 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 or VSAM ESDS 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 VSAM 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. VSAM 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. 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	A map name that overrides the data map name of the source PowerExchange data map.
Map Schema Override	A schema name that overrides the schema name in the source PowerExchange data map.
PWX Partition Strategy	For VSAM 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. 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';
Table Name Override	Overrides the table name of the imported table. If you import one table, enter the Table Name Override by using the following format: TABLENAME If you import multiple tables, use the following GroupName syntax: GroupName1=TABLENAME1;GroupName2=TABLENAME2 You override table names in Multi Group sources or targets, including Multi-Record Write, by using the same syntax you use to specify Filter Overrides and SQL Query Overrides. Use the following syntax: GroupName =TableNameOverride where the `GroupName` is the same name as the imported table name. Use a semi-colon to delimit each entry. For example: TABLEA=TABLE1;TABLEB=TABLE2 If both SQL Query Override and Table Name Override are specified, SQL Query Override takes precedence.
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 .