Mapping to a Sequential File of a Specific Format

Situations arise where you need to extract data from data sources and load a sequential table with a specific layout. For example, you may need to load a packaged application of a very specific format with a mix of both packed and alphanumeric columns. The data you load must be in the order the packaged application expects and the formats must match exactly. That means, when you build your data flow, you must make sure that each column is in the expected order and format.

To do this, you build a data flow to load a sequential file with a previously defined synonym. This means that you create the synonym to match the format required by the application you are loading and you use that synonym as the target of the data flow.

In the following example, DMHR, a human resources table, is the data source, and extracted records are loaded into a data target sequential file named DMHRINP. A synonym describing the layout of this sequential table was created in the format of the sequential file we are loading. The sequential file includes two packed columns and two alphanumeric columns.

Refer to the sample data flow DMSEQ for the complete example.


Top of page

Example: Mapping to a Sequential File in a Data Flow

In the DMC:

  1. Right-click an application directory in the navigation pane and choose New, then Flow. The Data Flow tab opens in the right pane, with the SQL object displayed.
  2. Drag the data source object DMHR from the ibisamp application directory in the navigation pane into the workspace, to the left of the SQL object.
  3. Right-click the SQL object and select Column Selection.

    The Column Selection window opens.

  4. Select ID_NUM, LNAME, FNAME, and PAYSCALE in the Available Columns list and click the arrow to add them into the Selected Columns list. Click OK.
  5. Drag the data target object DMHRINP from the ibisamp application directory into the workspace, to the right of the SQL object.
  6. Right-click the target object DMHRINP, and select Properties.

    The Properties dialog box opens.

  7. If the flow may be rerun, you should clear the target using the Prior to load option. Since dmhrinp is a Flat File, select Delete File. Click OK.
  8. Right-click the target object and select Target Transformations.

    The Transformation window opens.

  9. Select ID in the Target Columns list and ID_NUM in the Selected Columns list, then click the equal sign (=). The relation is added to the Expressions list.
  10. Select SCALE in the Target Columns list and PAYSCALE in the Selected Columns list, then click the equal sign (=). The relation is added to the Expressions list.
  11. Click the Automap button to map LNAME and FNAME.

    When you have completed the transformations, the Transformations window should appear, as shown in the following image:

    Transformations window

  12. Test the transformations by clicking the Test transforms button. Click Close.
  13. Click OK to close the Transformations window.
  14. Save the data flow as DMXSEQ.
  15. On the Flow tab, in the Run group, click Run and select Run from the drop-down menu.

When the data flow run is complete, verify that the data was loaded properly in the sequential file by right-clicking the DMHRINP object, selecting Toggle, and clicking the Sample Data tab, as shown in the following image.

Sample Data


iWay Software