SPLIT file using SORT based on totals



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Re: SPLIT file using SORT based on totals

Postby kkamarnath » Thu Apr 18, 2013 11:59 am

Hi BillyBoyo,

Thanks for your direction.

Please help me in understanding the below statement in your suggestion.

"WHEN=GROUP on the second field for the indicator which has been PUSHed and not TOTAL, to PUSH the value to the selection marker."

Thanks,
Amar
kkamarnath
 
Posts: 10
Joined: Wed Apr 17, 2013 6:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SPLIT file using SORT based on totals

Postby kkamarnath » Thu Apr 18, 2013 3:16 pm

Hi guys,

I am able to achieve my results using the following three SORT cards.

//SYSIN DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=GROUP,END=(5,5,CH,EQ,C'TOTAL'),
PUSH=(134:ID=4))
OUTFIL FNAMES=OUT1,
INCLUDE=(134,4,ZD,EQ,1),BUILD=(1,133)
OUTFIL FNAMES=OUT2,
INCLUDE=(134,4,ZD,GT,1),BUILD=(1,137)
/*

//SYSIN DD *
SORT FIELDS=(134,4,CH,D)
OUTREC IFTHEN=(WHEN=GROUP,END=(5,5,CH,EQ,C'TOTAL'),
PUSH=(138:ID=4))
OUTFIL FNAMES=OUT1,
INCLUDE=(138,4,ZD,EQ,1),BUILD=(1,133)
OUTFIL FNAMES=OUT2,
INCLUDE=(138,4,ZD,GT,1),BUILD=(1,141)
/*

//SYSIN DD *
SORT FIELDS=(138,4,CH,D)
OUTFIL FNAMES=OUT1,BUILD=(1,133)
/*

I really appreciate your help.

Thanks,
Amar
kkamarnath
 
Posts: 10
Joined: Wed Apr 17, 2013 6:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SPLIT file using SORT based on totals

Postby BillyBoyo » Thu Apr 18, 2013 9:39 pm

Run this,

  SORT FIELDS=COPY
  INREC IFTHEN=(WHEN=GROUP,END=(5,5,CH,EQ,C'TOTAL'),
                PUSH=(70:ID=4))


And put the SORTOUT to SYSOUT.

Look at your SORTOUT for the step.

You should see four groups of data.

If you use OUTFIL INCLUDE for ID (134,4,CH,EQ,C´0001´) if you want to keep the ID four long (you only need one long, from what you said).

A second OUTFIL INCLUDE for ID (134,4,SS,EQ,C´0001,0002´) (if you don´t know what the SS is doing, look it up in the manual).

A third OUTFIL with SAVE.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SPLIT file using SORT based on totals

Postby skolusu » Fri Apr 19, 2013 2:33 am

Amar,

you are complicating a simple request. You don't need 3 passes of data as well as a sort operation. use the following DFSORT JCL which will give you the desired results in just 1 pass

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD DISP=SHR,DSN=Your Input FB 133 byte file                                                     
//OUT01    DD SYSOUT=*                                               
//OUT02    DD SYSOUT=*                                               
//OUT03    DD SYSOUT=*                                               
//OUT04    DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  INREC IFTHEN=(WHEN=GROUP,END=(5,5,CH,EQ,C'TOTAL'),PUSH=(134:ID=2))
  OUTFIL FNAMES=OUT01,BUILD=(1,133),INCLUDE=(134,2,ZD,EQ,01)         
  OUTFIL FNAMES=OUT02,BUILD=(1,133),INCLUDE=(134,2,ZD,EQ,02)         
  OUTFIL FNAMES=OUT03,BUILD=(1,133),INCLUDE=(134,2,ZD,EQ,03)         
  OUTFIL FNAMES=OUT04,BUILD=(1,133),INCLUDE=(134,2,ZD,EQ,04)         
//*


If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

http://www.ibm.com/support/docview.wss? ... g3T7000080
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: SPLIT file using SORT based on totals

Postby enrico-sorichetti » Fri Apr 19, 2013 2:42 am

Kolusu that' s almost the same solution I proposed, but the TS changed the requirement on the fly...
but the TS rejected it because He has duplicate groups.


rec 1   !
rec 1   !   ==> outfile 1                       
tot-1   !

rec 2   !
rec 2   !
tot-2   !
        !   ==> outfile 2
rec 2a  !
rec 2a  !
tot-2   !

rec 3   !
rec 3   !   ==> outfile 3
tot-3   !

cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2994
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 164 times

Re: SPLIT file using SORT based on totals

Postby skolusu » Fri Apr 19, 2013 4:30 am

enrico-sorichetti wrote:Kolusu that' s almost the same solution I proposed, but the TS changed the requirement on the fly...
but the TS rejected it because He has duplicate groups.


Enrico,

I assumed OP wasn't able to map your solution to 133 bytes from 80 bytes. My bad. If the data is indeed like you shown OP wouldn't get the desired results unless he had EQUALS as default. Even if he had the solution assumes there are only 2 duplicate groups. What happens if he had more than 2 groups? How does he distinguish duplicate groups? REC2 and REC2a group? Is it by the same total for each group or the detailed records have the same indicator?
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
skolusu
 
Posts: 586
Joined: Wed Apr 02, 2008 10:38 pm
Has thanked: 0 time
Been thanked: 39 times

Re: SPLIT file using SORT based on totals

Postby kkamarnath » Fri Apr 19, 2013 10:24 am

Hi Kolusu,

In my requirement there are only three groups. Out of which group-1 and group-3 occurs only single time. and Group-2 occurs multiple times depending upon number of records that are being generated. Hence I used three sort cards to achieve my results.

Thanks,
Amar
kkamarnath
 
Posts: 10
Joined: Wed Apr 17, 2013 6:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SPLIT file using SORT based on totals

Postby BillyBoyo » Fri Apr 19, 2013 11:47 am

It will be possible with JOINKEYS, without sorting the data.

Can you confirm genuine sample input data which matches your requirement, and the sample output for that?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SPLIT file using SORT based on totals

Postby kkamarnath » Fri Apr 19, 2013 2:25 pm

Hi Billy,

Please find the sample input file below.

AAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCC
TOTAL-1 : 3       
DDDDDDDDDDDDDDDDDDD
EEEEEEEEEEEEEEEEEEE
FFFFFFFFFFFFFFFFFFF
GGGGGGGGGGGGGGGGGGG
TOTAL-2 : 4       
HHHHHHHHHHHHHHHHHHH
KKKKKKKKKKKKKKKKKKK
GGGGGGGGGGGGGGGGGGG
TOTAL-2 : 3       
PPPPPPPPPPPPPPPPPPP
QQQQQQQQQQQQQQQQQQQ
TOTAL-3 : 2       


Ouput:

File1:

AAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCC
TOTAL-1 : 3         


File2:

DDDDDDDDDDDDDDDDDDD
EEEEEEEEEEEEEEEEEEE
FFFFFFFFFFFFFFFFFFF
GGGGGGGGGGGGGGGGGGG
TOTAL-2 : 4       
HHHHHHHHHHHHHHHHHHH
KKKKKKKKKKKKKKKKKKK
GGGGGGGGGGGGGGGGGGG
TOTAL-2 : 3       


File3:

PPPPPPPPPPPPPPPPPPP
QQQQQQQQQQQQQQQQQQQ
TOTAL-3 : 2       

Please note that number of records for Total-2 may vary each time the input file is generated hence total occurrence of Total-2 is not constant. In the above example I gave the number of occurrences as 2, some times it may be 6 or 1 or 9 etc,. Total-1 and Total-3 always occur single time.

Thanks,
Amar

Code'd
kkamarnath
 
Posts: 10
Joined: Wed Apr 17, 2013 6:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SPLIT file using SORT based on totals

Postby BillyBoyo » Fri Apr 19, 2013 3:54 pm

OK, untested.

For your first file, in JNF1CNTL, append a "1". This is just to ensure that a key with a constant value is available for the whole file. It is not "relevant" because no matches will occur. You tell the JOINKEYS that this is SORTED.

For your second file, which you will allow JOINKEYS to sort for you, INCLUDE the TOTAL-1 and TOTAL-2 records. Use SUM with a set-up value of '1' to tell the range of the ID for the TOTAL-2 records later (which is why we include TOTAL-1 in the SUM). The "key" is set-up with a value of '0', to arrive before all the '1's of the data.

In the JOIN, ONLY have unmatched records (no matches will be possible, but cleaner to be specific).

In the main-task have an additional GROUP for the unmatched record from file 2, which is our SUMmed total. PUSH this so that it is available on all the records.

The first OUTFIL remains as was shown earlier.

The second OUTFIL has an AND added, so that the full range of IDs can be included for the variable number of TOTAL-2 groups.

The third OUTFIL specifies SAVE, so that anything not written to the first or second OUTFIL will end up here.

  JOINKEYS F1=INA,FIELDS=(134,1,A),SORTED,NOSEQCK           
  JOINKEYS F2=INB,FIELDS=(1,1,A)
  JOIN UNPAIRED,F1,F2,ONLY
  REFORMAT FIELDS=(F1:1,133,F2:2,2,?)                       
  OPTION COPY
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(136,1,CH,EQ,C'2'),
                   PUSH=(136:134,2)),
         IFTHEN=(WHEN=GROUP,END=(5,5,CH,EQ,C'TOTAL'),PUSH=(134:ID=2))
  OUTFIL FNAMES=OUT01,BUILD=(1,133),
                INCLUDE=(134,2,ZD,EQ,01)         
  OUTFIL FNAMES=OUT02,BUILD=(1,133),
             INCLUDE=(134,2,ZD,GT,01,
                            AND,134,2,ZD,LE,136,2,ZD)         
  OUTFIL FNAMES=OUT03,BUILD=(1,133),SAVE
//JNF1CNTL DD *
  INREC OVERLAY=(134:C'1')
//JNF2CNTL DD *
  INCLUDE COND=(5,7,SS,EQ,C'TOTAL-1,TOTAL-2)
  INREC BUILD=(C'0',C'01')
  SUM FIELDS=(2,2,ZD)
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

PreviousNext

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post