Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Chek sum of a group of records & exclud recrds if sum is

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Chek sum of a group of records & exclud recrds if sum is
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    Post subject:
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    Post subject: Reply to: Chek sum of a group of records & exclud recrds
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    Post subject:
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    Post subject:
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    Post subject:
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:

http://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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us