IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Chek sum of a group of records & exclud recrds if sum is
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
sajjan jindal
Warnings : 1
New User


Joined: 09 Sep 2007
Posts: 60
Location: india
PostPosted: Thu Jul 30, 2009 9:54 am
 Reply with quote
problem statement:

assume the following layout of the file.


Code:
Plan ID   Commision
1111       20
1111       -10
2222       10
2222       -5
2222       -10

The requirement is such that -> i want to exclude the records, if the total for the Commision amounts grouped on the Plan ID is coming negative.
In the above example the expected output is :

1111 20
1111 -10

Records for 2222 will be excluded since the sum of commisions for PlanID 2222 is coming to be -5 (negative value)

As far as i know if the requirement would have been to get single records, we could have used the "sum fields".
Please help me with this scenario.
Quote:
Code:
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1
New User


Joined: 09 Sep 2007
Posts: 60
Location: india
PostPosted: Thu Jul 30, 2009 4:26 pm
 Reply with quote
Thinking over this problem, i could come up with the following logic:

1. Sum the records and extract the records with negative values in a file.

2. Now using the file created in first step, exclude the records from the original file (the input for the 1st step) if they exist in the first file.
the challenge i forsee in this step is that there are duplicate records in the original file from which i have to extract records.

Please help me with this.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Thu Jul 30, 2009 9:06 pm
 Reply with quote
The following DFSORT/ICETOOL JCL will give you the desired results. I assumed your input file is FB recfm and 80 bytes in length.

Code:

//STEP0100 EXEC PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN       DD *                                                       
1111       20                                                         
1111       -10                                                       
2222       10                                                         
2222       -5                                                         
2222       -10                                                       
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)           
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                       
  SORT FROM(IN) USING(CTL1)                                           
  SORT FROM(T1) USING(CTL2)                                           
//CTL1CNTL DD *                                                       
  SORT FIELDS=(1,4,CH,A)                                             
  OUTFIL FNAMES=T1,REMOVECC,BUILD=(1,80,9X),                         
  SECTIONS=(1,4,TRAILER3=(1,4,81:'$',TOT=(6,10,SFF,PD,LENGTH=8)))     
//CTL2CNTL DD *                                                       
  SORT FIELDS=(1,4,CH,A,81,1,CH,D)                                   
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(81,1,CH,EQ,C'$'),PUSH=(82:82,8))   
  OUTFIL FNAMES=OUT,BUILD=(1,80),                                     
  OMIT=(81,1,CH,EQ,C'$',OR,82,8,PD,LT,0)                             
/*
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1
New User


Joined: 09 Sep 2007
Posts: 60
Location: india
PostPosted: Fri Jul 31, 2009 10:06 am
 Reply with quote
Hi Kolusu,

Thanks for your response, the solution is working perfectly. Since i am not aware of the fields you have used, i would request you to please tailor the above solution for the below:

Can you please modify this for a file having a FB of 503

and the field to be summed up is at position 216 (Lenght 10) -- ZD (I beileve Binary - usage is comp is ZD).

And also the output should be in the same sequence as the input.

Regards,
Sajjan.
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1
New User


Joined: 09 Sep 2007
Posts: 60
Location: india
PostPosted: Fri Jul 31, 2009 5:03 pm
 Reply with quote
Thanks , Kolusu, plz chk the following....

Code:
//STEP0100 EXEC PGM=ICETOOL                                           
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN       DD DSN=F6813AA.PRG.NEFHIST.GAINFO.TEST,disp=shr
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(150,30),RLSE)
//OUT      DD DSN=F6813AA.PRG.NEFHIST.GAINFO.out,
      DISP=(,CATLG),                               
      RECFM=FB,LRECL=503,BLKSIZE=0,               
      UNIT=SMS,SPACE=(CYL,(150,30),RLSE)           
//TOOLIN   DD *                                                       
  SORT FROM(IN) USING(CTL1)                                           
  SORT FROM(T1) USING(CTL2)                                           
//CTL1CNTL DD *                                                       
  SORT FIELDS=(1,8,CH,A)                                             
  OUTFIL FNAMES=T1,REMOVECC,BUILD=(1,503,9X),                         
  SECTIONS=(1,8,TRAILER3=(1,8,504:'$',TOT=(216,10,zD,PD,LENGTH=8)))     
//CTL2CNTL DD *                                                       
  SORT FIELDS=(1,8,CH,A,504,1,CH,D)                                   
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(504,1,CH,EQ,C'$'),PUSH=(505:505,8))   
  OUTFIL FNAMES=OUT,BUILD=(1,503),                                     
  OMIT=(504,1,CH,EQ,C'$',OR,505,8,PD,LT,0)                             
/*
[/code]
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri Jul 31, 2009 10:03 pm
 Reply with quote
Quote:
and the field to be summed up is at position 216 (Lenght 10) -- ZD (I beileve Binary - usage is comp is ZD).


sajjan jindal,

Comp items are binary and are NOT ZD format. For a table showing COBOL formats vs DFSORT formats, see:

publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA20/C.3?

Your code looks ok with exception of the data format. So correct that and your job should run fine.
Back to top
View user's profile Send private message
sajjan jindal
Warnings : 1
New User


Joined: 09 Sep 2007
Posts: 60
Location: india
PostPosted: Sat Aug 01, 2009 6:36 pm
 Reply with quote
Thanks a Tone Kolusu.
I always had a confusion for the data types.

Regards,
Sajjan.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Join multiple records using splice DFSORT/ICETOOL 5
Search our Forums:

Back to Top
IBMMainframes.com is not an official and/or affiliated with IBM® in anyway
Board Rules | FAQ | Downloads | Wiki | SiteMap | Contact Us