|Q. In the previous tutorial, you’ve told me how to obtain the Sum(Aggregation) of records using SUM FIELDS. Some of the fields in the Output Report are not meaningful. How do I display only the relevant fields, and remove the unnecessary ones?|
|Consider the following Input Dataset - |
For finding the Total Sales Figure of all Insurance Agents(01000+02000+...+12000), we must use the SUM FIELDS Control Statement of DFSORT Tool. If we apply the SUM FIELDS control statement, on DFSORT as :
This tells us that the Total Sales of all Insurance Agents in COUNTRY=INDIA is 78,000. All other fields are irrelevant/not-meaningful. Now, let’s say I wanted to display only the COUNTRY and TOTAL SALES Field in the Output Report.
The OUTREC FIELDS Control Statement is used to display only select fields in the Output Report, and eliminate the unnecessary/irrelevant ones.
Q. Hey, I wanted the records in the above Output report to be displayed in Hexadecimal format – the EBCDIC values in Hex, just like how its really stored in a Computer? How do I do it in JCL?
Well, you just need to use the HEX Switch in the OUTREC FIELDS Control Statement. So, in the OUTREC FIELDS Control Statement for the above output report, add the HEX Switch to all fields, you want to be displayed in HEX -
Q. Hey I have had enough of Sum/Aggregation! Gimme a break.. Show me something else. Like how do I perform some Arithmetic Operations on the numeric field of the Input Records?
Let me just flash before you the Input Dataset, that I’ve chosen for this example. This is the data as of the year 2008.
In the year 2009, due to a performance bonus given to Insurance Agents, they all put in more efforts and in the next quarter it was observed, that their Sales grew by 10 percent. Write a Batch JOB/JCL that increases the Sales of Insurance Agents by 10 percent. So, you need to generate a report for Sales for the financial Year ending 2009, showing a 10% rise in the SALES Figure for all the agents.
To perform arithmetic(like increasing/addition) on numeric fields, we can use several operators like ADD,SUB,MUL,DIV,MOD inside the OUTREC FIELDS Control Statement. To treat a field as numeric, and to be able to perform numerical operations on it, we code ZD(Zoned Decimal) specifier.
To raise the Sales figure by 10 percent, we need to multiply the Sales x 1.1. But, since decimal(float) values are not allowed, we will multiply SALES x 11, and then describe the format of the resultant Edited field. The format of the Edited field can be specified using EDIT Subparameter.
If we have input sales = 1000, we multiply by 11, 1000 x 11 = 11000.
We specify the format for the output field using EDIT Parameter. We will code the EDIT Parameter for the above example as,
Upon submitting the above Job Stream, the final Output Report for the Sales 2009, which shows a 10% increase in Sales of Each Insurance Agent, looks something like this -
Q. How do you reformat records with OUTREC OVERLAY Control Statement?
When we use OUTREC FIELDS to re-format the output records, you need to specify all the fields/items, that you desire to appear in the Output Record. You must tell the DFSORT tool through the OUTREC FIELDS Control Statement, every item that you would like the record. Thus, you have to build the re-formatted record, one item at a time - field by field. In between, you can add some special characters like comma(,) or currency symbol($). This can be very tedious and cumbersome, if the record has a large length and contains many fields/columns.
If you intend to change/modify only a handful of columns, an easier and more convenient option is to use the OVERLAY Control Statement. Note that OVERLAY statement can be used to do the same tasks that you can accomplish with OUTREC Statement.
Let me show you a simple example which illustrates how to raise the Sales figure of the Insurance Agents like in the above example, but using the OUTREC OVERLAY Statement. This example proves, how easy it is to use the OVERLAY statement as opposed to the meticulous and cumbersome OUTREC FIELDS Control Statement.
Q. How the OUTREC IFTHEN Control Statement work? Can you elaborate by showing a simple JCL/Job stream?
|OUTREC FIELDS and OUTREC Overlay allows you to apply the same reformatting to all the Output Records. However, if you would like to apply different re-formatting to different output records, then one should use the IFTHEN option. By using the IFTHEN option, one can apply different OUTREC FIELDS or OUTREC OVERLAY(formats) statements to different output records. |
Suppose, we would like the Output Report to be generated as follows -
1) If SALES of Agent < 06000, then Increase = 10%
2) If SALES of Agent >= 06000, then Increase = 20%
So, for all records having sales < 06000, we multiply(MUL) by 1.1.
For all records having sales >= 06000, we multiply(MUL) by 1.2.
Thus, we can multiply records by +11 and +12 respectively, in JCL, and display the output field using the EDIT Format, EDIT=(IT,TTT.T)
The Output Report for the above Batch Job/JCL looks as follows : AG10004