SUM in DFSORT



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

SUM in DFSORT

Postby fornanthakumar » Mon Jan 09, 2012 5:15 pm

Hi,

My requirement is sumup the values based on the key fields. when i do that i am getting only one record which gives me the summed up value for each key field but i want like,

Importantly : I should not do SORT, just copy.

I/P:

1234510
1234520
1234640
1234640
1234613
1234110

O/P:

1234510
1234520
1234530
1234640
1234640
1234613
1234693
1234110
1234110

My SYSIN Card is,

SORT FILEDS = (1,5,CH,A)
SUM FIELDS = (6,2,ZD)

Note : Here i could mention fields only with SORT. i am not sure about using Fields with OPTION COPY statements. My objectives are two
1. Do not Sort the records.
2. I want all the individual records and the sum value should be added after each key records finished.

Please help me out.
Regards,
Nanthu.Y.
fornanthakumar
 
Posts: 69
Joined: Fri Oct 22, 2010 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SUM in DFSORT

Postby BillyBoyo » Mon Jan 09, 2012 5:18 pm

First, drop the e-mail address from your signature. You were warned about this in the set-up stuff. Your e-mail address will be found by some "web bot" and you will get all sorts of rubbish arriving.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SUM in DFSORT

Postby Frank Yaeger » Tue Jan 10, 2012 2:07 am

Here's a DFSORT job that will do what you asked for (presumably you're not concerned about overflowing 2 digits for the total):

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
1234510
1234520
1234640
1234640
1234613
1234110
//SORTOUT DD SYSOUT=*
//SYSIN DD *
  OPTION COPY
  OUTFIL REMOVECC,
    SECTIONS=(1,5,
      TRAILER3=(1,5,TOT=(6,2,ZD,TO=ZD,LENGTH=2)))
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Re: SUM in DFSORT

Postby fornanthakumar » Tue Jan 10, 2012 2:52 pm

Hi Frank Yaeger ,

Thank you very much indeed for your great assistance.

To add more into it, i want the output to be like below,
1234510
1234520
1234530
1234640
1234640
1234613
1234693
1234110

If you see the condition will be COND = (1,5,ZD,GT,12342).. but still i want the last record (omit by the cond) to be in output file.

can i sections in IFTHEN clause.. please advise..
Regards,
Nanthu.Y.
fornanthakumar
 
Posts: 69
Joined: Fri Oct 22, 2010 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SUM in DFSORT

Postby BillyBoyo » Tue Jan 10, 2012 3:20 pm

Can you look at what you wrote, and then re-write so that it is clearer.

The output from Frank's code gives the output you requested originally.

1234510
1234520
1234530
1234640
1234640
1234613
1234693
1234110
1234110


Where does the COND come into it? Are you saying that when there is only one record, you don't want a new summed record generated?

I'd be concerned that there is nothing on the file to indicate which records have been generated. You are generating trailers, but can't tell that it is a trailer (except by change of key). If you run the sort again, it'll just add up the total, including the already-generated total record. As Frank has said, you don't have much space with just two digits for the summing. Are you happy with that?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SUM in DFSORT

Postby fornanthakumar » Tue Jan 10, 2012 3:25 pm

Hi Billy,

I am very much happy with Frank's Code.. Now the situation here is, i should generate the trailers like above only for particular type of keys from the files and rest of the records i do not want the trailers.

Just say .. i want to generate trailer for the key which is greater than 12342. hence i want the output like below,
1234510
1234520
1234530
1234640
1234640
1234613
1234693
1234110

Are you clear with my question ?
Regards,
Nanthu.Y.
fornanthakumar
 
Posts: 69
Joined: Fri Oct 22, 2010 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SUM in DFSORT

Postby BillyBoyo » Tue Jan 10, 2012 4:08 pm

Let's see.

You have a range of numbers you want to generate a trailer for (greater than 12342).

You are otherwise happy with all other points raised.

You can't get the IFTHEN into the SECTIONS.

There is a solution with JOINKEYS. If you want to try it yourself, let us know. I'm short of time at the moment, but I'm sure you'll have one by tommorow morning otherwise.

Please make sure that you have the requirement exactly described now.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SUM in DFSORT

Postby fornanthakumar » Tue Jan 10, 2012 4:25 pm

Hi billy,

Thanks. My requirement is what i described above.. i have given you the how the output records should look like,

I will in try using JOINKEY now... but please help me out if you have any such idea...

thanks in advance.
Regards,
Nanthu.Y.
fornanthakumar
 
Posts: 69
Joined: Fri Oct 22, 2010 1:58 pm
Has thanked: 0 time
Been thanked: 0 time

Re: SUM in DFSORT

Postby BillyBoyo » Tue Jan 10, 2012 5:24 pm

See if this one can give you some ideas. dfsort-icetool-icegener/topic6793.html#p29910
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

Re: SUM in DFSORT

Postby Frank Yaeger » Tue Jan 10, 2012 11:35 pm

You can use a DFSORT/ICETOOL job like the following for your 'changed' requirement:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD *
1234510
1234520
1234640
1234640
1234613
1234110
1234008
1234007
1234006
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD SYSOUT=*
//TOOLIN DD *
COPY FROM(IN) TO(T1) USING(CTL1)
COPY FROM(T1) TO(OUT) USING(CTL2)
//CTL1CNTL DD *
  OUTFIL REMOVECC,FNAMES=T1,
    SECTIONS=(1,5,
      TRAILER3=(1,5,TOT=(6,2,ZD,TO=ZD,LENGTH=2),C'T'))
//CTL2CNTL DD *
  OMIT COND=(8,1,CH,EQ,C'T',AND,1,5,ZD,LE,12342)
  INREC OVERLAY=(8:X)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
User avatar
Frank Yaeger
Global moderator
 
Posts: 1079
Joined: Sat Jun 09, 2007 8:44 pm
Has thanked: 0 time
Been thanked: 15 times

Next

Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post