IBM Mainframe Forum

by **prasanthbalan** » Tue Mar 05, 2013 12:08 pm

Hi,

I have a input file of type FB and lrecl = 80.

The layout is as shown

Key 9(10) 1-10
Date s9(5) comp-3 11-13
Seq no: s9(5) comp-3 14-16

rec-type 9(3) 36-38
amount s9(11)V99 COMP-3 41-47

i need to sort all the input records based on the key,date and rec-type.
Then i need to sum the records having rec type = 100 and rec-type = 200 into 1 record with rec-type = 150 , the amount needs to be summed up and all other data needs to be from any of the two records.
then i have to increment the seq num in such a way that for every key the number starts from 1 and incremented by 1. Once the key changes, the sequence has to restart from 1 for the next key.

Also for a key, ther can be no recs of type = 100 and 200 or can contain only 1 type or both may be present. (The no of occurances is also not constant).
There may be other records in between rec-type = 100 and 200. (But if needed i can sortout only these records and do the required operations on them. But in that case i will need to perform the sequencing only after joining these records with the original ones).

Please help.

I have syncsort and not dfsort.

by **BillyBoyo** » Tue Mar 05, 2013 12:57 pm

Can you show us which version of SyncSort you have? There is a neat way with JOINKEYS, but it becomes un-neat if you don't have JNFnCNTL files available.

by **prasanthbalan** » Tue Mar 05, 2013 1:03 pm

Hi BillyBoyo,

I do not know the version of my syncsort. How to find it ?

However i have used joinkeys and it works.

by **BillyBoyo** » Tue Mar 05, 2013 1:17 pm

If you look at the "top" of the sysout for a SortStep it should tell you things like the version, who licensed to etc.

If you can knock up a JOINKEYS with one record on each, say one character key, then have

Select all

//JNF1CNTL DD *
INREC OVERLAY=(3:C'X')

And see if the X appears on your SORTOUT.

by **prasanthbalan** » Tue Mar 05, 2013 1:31 pm

Hi Billy,

My syncsort version is SYNCSORT FOR Z/OS 1.3.2.1R.

by **BillyBoyo** » Tue Mar 05, 2013 2:41 pm

OK, I suspect the JNF1CNTL I showed won't work then. Different solution.

by **prasanthbalan** » Tue Mar 05, 2013 3:35 pm

hi all,

let me split the work and then do.

initially let us assume that i have the input file with only the records of rec-type 100 and 200.

So i for all the records with equal key and date, i want one record in the output file (with the amount summed). Is this possible using grouping ?

by **BillyBoyo** » Tue Mar 05, 2013 4:58 pm

OK, you didn't show any test data, so I used my own. This should get you started:

Select all

INREC IFTHEN=(WHEN=(10,3,SS,EQ,C'100,200'),
OVERLAY=(10:C'150',
the new record-type
81:C'000',
both/any (if present) will have same low-order key
20,7)),
and here's the value to SUM
IFTHEN=(WHEN=NONE,
for all others
OVERLAY=(81:SEQNUM,3,ZD,
low-order key is unique per record
C'0000000'))
dummy value of zero for amount for non-150s

SORT FIELDS=(1,9,CH,A,
account number
10,3,CH,A,
record-type
81,3,CH,A)
000 (to make non-unique) or sequence number (to make unique)

SUM FIELDS=(84,7,ZD)
non-zero for 150s only

OUTREC IFOUTLEN=80,
your fixed-length record length
IFTHEN=(WHEN=INIT,
OVERLAY=(92:SEQNUM,5,ZD,RESTART=(1,9))),
to identify the start of a GROUP as SyncSort does not have KEYBEGIN
IFTHEN=(WHEN=GROUP,BEGIN=(92,5,CH,EQ,C'00001'),
PUSH=(16:SEQ=3)),
sequence number within group
IFTHEN=(WHEN=(10,3,CH,EQ,C'150'),
OVERLAY=(20:84,7))
SUMmed value

You'll need to change positions, lengths, types as necessary.

The idea is, before the SORT/SUM, change the input types as necessary, and have a new sort-key which is non-unique (I used zero) for the 150s, unique for all others. Extend the record to hold the amount for summing for 150s, zero for all others. Then SORT on all your keys (I ignored one) plus the new unique/non-unique key. SUM on the extended amount.
Use OUTREC to establish sequence number and to put the SUMmed amount in the correct place for the single 150 record (if present).

IBM Mainframe Forum

grouping few records from a single file in syncsort

grouping few records from a single file in syncsort

Re: grouping few records from a single file in syncsort

Re: grouping few records from a single file in syncsort

Re: grouping few records from a single file in syncsort

Re: grouping few records from a single file in syncsort

Re: grouping few records from a single file in syncsort

Re: grouping few records from a single file in syncsort

Re: grouping few records from a single file in syncsort