This is the second in series of articles illustrating on how to use existing IRI CoSort (SortCL) jobs in graphical IRI Voracity ETL workflows, or more simply, flows. In the first article, we dropped an existing SortCL job that processes month-end sales transactions into Voracity. Specifically, we:

1. Created a flow diagram with a start block, connector, and transform mapping block representing the SortCL job script.
2. Modified the Transform Mapping Diagram for that script by
   a.  deleting the output file Out_CashRecords.dat from the flow.
   b.  adding an Action Key for the Sort.
3. Saved the changes made in the diagram to the SortCL script, and created a batch file to run the job in the flow by “Exporting the Flow Component.”

In this article, we will add another job script into a flow, or a “flowlet.” Below is that new SortCL script, MonthEndSales.scl:

/STATISTICS=monthendsales.stat
/INFILE=C:/IRI/CoSort95/workbench/workspaceFlow/MonthEnd/AllSales.dat
     /PROCESS=RECORD
     /ALIAS=AllSales
     /FIELD=(DEPT, TYPE=ASCII, POSITION=1, SEPARATOR=”|”)
     /FIELD=(YEARMONTH, TYPE=ASCII, POSITION=2, SEPARATOR=”|”)
     /FIELD=(TRANSAMT, TYPE=NUMERIC, POSITION=3, SEPARATOR=”|”, PRECISION=2)

/SORT
     /KEY=(DEPT, TYPE=ASCII)

/OUTFILE=”NIGHTLY.SALESBYDEPT;DSN=Oracle_qa2;”
     /PROCESS=ODBC
     /CREATE
     /FIELD=(DEPT, TYPE=ASCII, POSITION=1, SEPARATOR=”|”)
     /FIELD=(YEARMONTH, TYPE=ASCII, POSITION=2, SEPARATOR=”|”)
     /FIELD=(SALESAMT, TYPE=NUMERIC, POSITION=3, SEPARATOR=”|”, PRECISION=2)
     /SUM SALESAMT FROM TRANSAMT BREAK DEPT

Drag the script file’s icon from the Project Explorer to the right of the Transform Mapping Block named SortTransSelect.scl to create the Transform Mapping Block named MonthEndSales.scl.

Connect the Blocks

Notice that the input file for MonthEndSales.scl is AllSales.dat, which is also one of the targets in SortTransSelect.scl. Using the Connection component from the Palette, connect the icon for Out_AllSales.dat in the SortTransSelect.scl block, to In_AllSales.dat in the MonthEndSales.scl block, by clicking on the component, then each file icon. The order that you click the blocks determines the direction of the connection arrow.

The MonthEnd Flow Diagram now appears as below with two transform mapping blocks:

Double-click on the block for MonthEndSales.scl to create the MonthEndSales Mapping Diagram. This is a detailed diagram of the script MonthEndSales.scl.

Append (Don’t Truncate) the Table

Looking at the Output Data block, the Section Options block of the output NIGHTLY.SALESBYDEPT has Process ODBC. This means the output will be loaded into a database for the named output. The Section Options block also contains the option Output Write Type /Create. This directs the job to truncate the table before loading the new records. Instead, we want to keep the existing records and add the new records to the table. 

Click Output Write Type in the Section Options. In the Properties View on the Base tab, select /APPEND from the drop-down. Or double-click Output Write Type and select /APPEND from the Output WriteType dialog, and click Finish. In Section Options, Output Write Type now displays /APPEND.

Create a New Target with Aggregation

The output is performing a Sum from the input field TRANSAMT and placing the value in the column named SALESAMT in the table NIGHTLY.SALESBYDEPT. The aggregation for the table has a BREAK condition for each time a new department is encountered, so the monthly subtotals for each department can load into the table.

It was decided that a monthly grand total for sales will load to a separate table called MONTHENDSALES. Therefore, an aggregation needs to be created similar to the one in SALESBYDEPT, but without a BREAK condition.

Drag an Output ODBC from the Palette into the Output Data block. This brings up the Table Selection dialog.

Select your Connection profile from the drop-down, check the box for the table that will receive the data, and click Finish.

A block for the output NIGHTLY.MONTHENDSALES has now been created and links the field AllSales.YEARMONTH to the YEARMONTH column in the table NIGHTLY.MONTHENDSALES. The field SALESAMT needs to be deleted because we are going to define it with an aggregation. Right-click on the field SALESAMT, select Edit > Delete from Model.

From Field Options in the Palette, drag the Aggregation to the blue field block for NIGHTLY.MONTHENDSALES. Be sure the block is large enough to add this to the bottom of the field list. The Aggregation dialog pops up.

Click the Edit button next to the Field Name, and change the Field Name from AGGREGATION to SALESAMT. Change the Data Type to NUMERIC, change the Precision to 2 (for 2 decimal places), and click OK.

This takes you back to the Aggregation dialog. Under From, select the Field radio button > choose the field TRANSAMT > click OK.

Now, connect the field AllSales.TRANSAMT to the column SALESAMT by clicking on Mapping Connection in the Palette, then on the field AllSales.TRANSAMT in the Sort box, and finally on the column SALESAMT in the table NIGHTLY.MONTHENDSALES.

Save the Changes

Right-click in the white background of the diagram, select IRI Diagram Actions > Export Flow Component, then verify that the File name is MonthEndSales.scl, and click Finish.

The MonthEndSales Mapping Diagram now appears as follows:

With the edits complete, go back to the flow diagram and right-click the flowlet to create the .bat file by Exporting Flow Component. The .bat file will contain commands to execute the blocks in the flow in sequence, beginning with the Start block. It can be executed from within Workbench directly, through its task scheduler, using any third-party job scheduler, or from the command line.

In the next article, we will expand the flow by adding:

• Command Line blocks where we can add any commands that can be executed on the command line of the operating system
• A Decision block where the path that is followed by the flow is decided by whether a condition is true or false

For help with your flow, email voracity@iri.com.