grouping few records from a single file in syncsort



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

grouping few records from a single file in syncsort

Postby 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.
prasanthbalan
 
Posts: 7
Joined: Tue Jan 08, 2013 9:37 pm
Has thanked: 1 time
Been thanked: 0 time

Re: grouping few records from a single file in syncsort

Postby 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.

These users thanked the author BillyBoyo for the post:
prasanthbalan (Tue Mar 05, 2013 1:04 pm)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: grouping few records from a single file in syncsort

Postby 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. :)
prasanthbalan
 
Posts: 7
Joined: Tue Jan 08, 2013 9:37 pm
Has thanked: 1 time
Been thanked: 0 time

Re: grouping few records from a single file in syncsort

Postby 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

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


And see if the X appears on your SORTOUT.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: grouping few records from a single file in syncsort

Postby prasanthbalan » Tue Mar 05, 2013 1:31 pm

Hi Billy,

My syncsort version is SYNCSORT FOR Z/OS 1.3.2.1R.
prasanthbalan
 
Posts: 7
Joined: Tue Jan 08, 2013 9:37 pm
Has thanked: 1 time
Been thanked: 0 time

Re: grouping few records from a single file in syncsort

Postby BillyBoyo » Tue Mar 05, 2013 2:41 pm

OK, I suspect the JNF1CNTL I showed won't work then. Different solution.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: grouping few records from a single file in syncsort

Postby 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 ?
prasanthbalan
 
Posts: 7
Joined: Tue Jan 08, 2013 9:37 pm
Has thanked: 1 time
Been thanked: 0 time

Re: grouping few records from a single file in syncsort

Postby 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:

  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).
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post