DFSORT Tables

(Originally posted 2016-01-10.)

It’s been a while since I posted a DFSORT trick – and it’s high time I did.

This post follows (distantly) on from More Maintainable DFSORT and is occasioned by some recent development work on our tools.

As so often happens, developing this code has been a bit of a journey of discovery. And I’ve learnt (the hard way) a couple more ways you can make the code more maintainable.

Let me straight away share a few of these with you – in case you use [1] DFSORT but don’t want to read much further.

  • Where possible specify fields on separate lines. So, rather than writing

    INREC FIELDS=(A,B,C)
    

    write

    INREC FIELDS=(A,
      B
      C)
    

    In fact the above mentioned post contained this advice, but in my recent development work it’s proved invaluable.

  • Consider a padding final field on eg OUTREC:

    OUTREC FIELDS=(A,
        B,
        X)
    

    where the ‘X’ is a single blank specifier. [2] That way when you move fields around or delete them you don’t need to worry about the trailing bracket – as it’s after the invariant X.

  • You can decode a STCK value into printable seconds with

    TIMESTAMP,DIV,+4096000,EDIT=(IIIT.TTT)
    

Using IFTHEN To Make A Table

This is the “meat” of this post.

Suppose you want to produce a report that is a grid or table, with the same type of value in each cell.

Consider the following input data set:

ALPHA       WHITE  7
ALPHA       BLUE   1
BRAVO       RED    3
ALPHA       WHITE  4
ALPHA       RED    8
BRAVO       RED   11
CHARLIE     BLUE  67
BRAVO       RED   34
BRAVO       WHITE 57
CHARLIE     BLUE   8
ALPHA       WHITE 34
CHARLIE     BLUE  81
DELTA       RED   24
ECHO        BLUE   9
FOXTROT     RED    7

Three columns, of which the third is numeric though character rather than binary.

Now look at this report, produced from the data:

 Division       RED Sold WHITE Sold  BLUE Sold Other Sold   RED Txns WHITE Txns  BLUE Txns Other Txns
 ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
 ALPHA                 8         45          1          0          1          3          1          0
 BRAVO                48         57          0          0          3          1          0          0
 CHARLIE               0          0        156          0          0          0          3          0
 DELTA                24          0          0          0          1          0          0          0
 ECHO                  0          0          9          0          0          0          1          0
 FOXTROT               7          0          0          0          1          0          0          0

The second field from each input data record is used to define the column, while the first field is used to define the row.

The input data is mapped with the following DFSORT Symbols (SYMNAMES DD) statements:

* MAPPING OF ORIGINAL RECORDS
POSITION,1
DIVISION,*,12,CH
COLOUR,*,6,CH
SOLD,*,2,CH

In picture form:

To achieve the desired results in the report using the following DFSORT statements.

First INREC to reformat the data:

 INREC IFTHEN=(WHEN=INIT,
   BUILD=(DIVISION,
     COLOUR,
* SOLD VALUE IN INPUT RECORD CONVERTED TO BI
     SOLD,UFF,TO=BI,LENGTH=2,
* SOLD TALLIES
     X'0000',
     X'0000',
     X'0000',
     X'0000',
* RECORD TALLIES
     X'0000',
     X'0000',
     X'0000',
     X'0000')),
 IFTHEN=(WHEN=(_COLOUR,EQ,C'RED   '),
    OVERLAY=(_RED_SOLD:_SOLD,_RED_RECS:X'0001')),
 IFTHEN=(WHEN=(_COLOUR,EQ,C'WHITE '),
    OVERLAY=(_WHITE_SOLD:_SOLD,_WHITE_RECS:X'0001')),
 IFTHEN=(WHEN=(_COLOUR,EQ,C'BLUE  '),
    OVERLAY=(_BLUE_SOLD:_SOLD,_BLUE_RECS:X'0001')),
 IFTHEN=(WHEN=NONE,
    OVERLAY=(_OTHER_SOLD:_SOLD,_OTHER_RECS:_SOLD))

Second a SORT statement:

 SORT FIELDS=(_DIVISION,A)

Third a SUM statement:

 SUM FIELDS=(_RED_SOLD,
  _WHITE_SOLD,
  _BLUE_SOLD,
  _OTHER_SOLD,
  _RED_RECS,
  _WHITE_RECS,
  _BLUE_RECS,
  _OTHER_RECS)

And fourth an OUTFIL statement:

 OUTFIL FNAMES=SORTOUT,REMOVECC,
 HEADER1=('Division    ',X,
   '  RED Sold',X,
   'WHITE Sold',X,
   ' BLUE Sold',X,
   'Other Sold',X,
   '  RED Txns',X,
   'WHITE Txns',X,
   ' BLUE Txns',X,
   'Other Txns',X,/,
   '------------',X,
   '----------',X,
   '----------',X,
   '----------',X,
   '----------',X,
   '----------',X,
   '----------',X,
   '----------',X,
   '----------'),
 OUTREC=(_DIVISION,X,
   _RED_SOLD,EDIT=(IIIIIIIIIT),X,
   _WHITE_SOLD,EDIT=(IIIIIIIIIT),X,
   _BLUE_SOLD,EDIT=(IIIIIIIIIT),X,
   _OTHER_SOLD,EDIT=(IIIIIIIIIT),X,
   _RED_RECS,EDIT=(IIIIIIIIIT),X,
   _WHITE_RECS,EDIT=(IIIIIIIIIT),X,
   _BLUE_RECS,EDIT=(IIIIIIIIIT),X,
   _OTHER_RECS,EDIT=(IIIIIIIIIT),X,
   X)

The data reformatted with INREC is mapped with these Symbols (in the same file as the input data symbols):

* RESULTS OF INREC
POSITION,1
_DIVISION,*,12,CH
_COLOUR,*,6,CH
_SOLD,*,2,BI
_RED_SOLD,*,2,BI
_WHITE_SOLD,*,2,BI
_BLUE_SOLD,*,2,BI
_OTHER_SOLD,*,2,BI
_RED_RECS,*,2,BI
_WHITE_RECS,*,2,BI
_BLUE_RECS,*,2,BI
_OTHER_RECS,*,2,BI

The data remains formatted this way until the OUTFIL statement produces the final report to the REPORT DD.

Mostly this is complicated stuff so let me take you through it, statement by statement.

INREC

The INREC statement reformats the record to look like this:

Here two sets of four new fields have been added to the input record. They are mapped with (previously-shown) symbols:

_RED_SOLD,*,2,BI
_WHITE_SOLD,*,2,BI
_BLUE_SOLD,*,2,BI
_OTHER_SOLD,*,2,BI
_RED_RECS,*,2,BI
_WHITE_RECS,*,2,BI
_BLUE_RECS,*,2,BI
_OTHER_RECS,*,2,BI

Also the SOLD field has been converted to a 2-byte Binary field:

_SOLD,*,2,BI

So this is all achieved with a set of IFTHEN “stages”, looking a lot like a pipeline:

  • IFTHEN WHEN=INIT is always performed – and first. It primes the counter fields (with 3 bytes of Binary zeroes apiece) and uses SOLD,UFF,TO=BI,LENGTH=2 to convert the SOLD field to Binary.
  • IFTHEN WHEN=(_COLOUR,EQ,C’RED ‘) is used only for records where the COLOUR field is ‘RED ’ to copy the _SOLD Binary value into the _RED_SOLD field and to write Binary 1 (X’0001’) to the _RED_SOLD field.
  • Likewise the next two IFTHEN clauses, which do the same for ‘BLUE ’ and ‘WHITE ’.
  • IFTHEN WHEN=NONE is performed only for records where none of the previous IFTHEN WHEN conditions were met. With the exception of the WHEN=INIT clause. It copies the SOLD value into _OTHER_SOLD and Binary 1 into _OTHER_RECS.

After INREC the number of records is the same but the SOLD field is copied into the right _SOLD field and 1 into the _RECS field.

The left hand side of the data at this point looks like:

ALPHA                 0          7          0          0          0          1
ALPHA                 0          0          1          0          0          0
BRAVO                 3          0          0          0          1          0
ALPHA                 0          4          0          0          0          1
ALPHA                 8          0          0          0          1          0
BRAVO                11          0          0          0          1          0
CHARLIE               0          0         67          0          0          0
BRAVO                34          0          0          0          1          0
BRAVO                 0         57          0          0          0          1
CHARLIE               0          0          8          0          0          0
ALPHA                 0         34          0          0          0          1
CHARLIE               0          0         81          0          0          0
DELTA                24          0          0          0          1          0
ECHO                  0          0          9          0          0          0
FOXTROT               7          0          0          0          1          0

For legibility I’ve left the last few columns out and in fact what you’re seeing is formatted so you can read the Binary numbers.

At this point there’s been no summation.

SORT

I sort on the _DIVISION field, which is really the same as the DIVISION field.

SUM

I sum the 4 _SOLD and the 4 _RECS fields. To show you the result of this in a viewable form I’d pretty much be showing you the final result (and I’ve already done that).

OUTFIL OUTREC

While there are some very sophisticated uses of OUTFIL this one is a simple case of report writing:

  • HEADER1 just prints a one-time header line (or two). The ‘/’ just specifies a new line.
  • OUTREC reformats the records passed, in this case making them printable. For example _RED_SOLD,EDIT=(IIIIIIIIIT) converts _RED_SOLD to a printable number with leading zeroes suppressed.

Conclusions

The above worked example is readily adaptable. But it is a little bit fragile, as is so often the case with advanced DFSORT and ICETOOL applications. Once I got used to the basic technique – using a series of IFTHEN WHEN clauses to copy one input field into a series of different output fields depending on another field’s value – it became readily extensible and adaptable.

And some of the techniques in this post (and in More Maintainable DFSORT) made this much easier.

Some things to note:

  • You have to know how many columns you want and what (in this case) COLOUR field values to expect. In my real world example I took full advantage of the _OTHER fields to ensure I captured them all.[3]
  • This example shows you can have 2 fields “gridded” like this. In this case one is just a count but I’ve done this with two and indeed three distinct input record fields.

  • This whole technique depends heavily on DFSORT Symbols.

I appreciate this has been a long and fiddly post. Perhaps we can hope for something more succinct soon. 🙂


  1. By “use” I mean “write DFSORT / ICETOOL statements” rather than “run jobs”.  ↩

  2. With OUTFIL if you specify a header (eg HEADER1) you might want to pad the OUTREC with lots of blanks, eg 50X. But you wouldn’t want to do that with high-volume Production data.  ↩

  3. In Production I have another query which tells me what values to expect.  ↩

Published by Martin Packer

.

One thought on “DFSORT Tables

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: