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

retreiving max count records using DFSORT
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 07, 2010 12:16 pm
 Reply with quote
Hi,

I've a file with the records like below.

111 xx ....
111 xx ...
111 xx ...
111 yy ....
111 yy ...
222 yy ..
222 yy ...
222 zz ..
333 xx ...

And want the output like below.

111 xx....( as 111 is having max times xx than others)
222 yy .. ( as 222 is having max times yy thn others)
333 xx ...

We want to accomplish this thru DFSORT.

Can anyone suggest how to do this ..

Thanks in Advance,
hari
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10873
Location: italy
PostPosted: Fri May 07, 2010 12:24 pm
 Reply with quote
Your question is the same as this one
ibmmainframes.com/viewtopic.php?t=48563&highlight=

are You in the same class, or working together on the same problem ???
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Fri May 07, 2010 1:06 pm
 Reply with quote
You can use below sortstep....
Code:

//S1    EXEC  PGM=SORT                                           
//SYSOUT    DD  SYSOUT=*                                         
//SORTIN DD *                                                     
111 XX ...                                                       
111 XX ...                                                       
111 XX ...                                                       
111 YY ...                                                       
111 YY ...                                                       
222 YY ...                                                       
222 YY ...                                                       
222 ZZ ...                                                       
333 XX ...                                                       
/*                                                               
//SORTOUT DD SYSOUT=*                                             
//SYSIN    DD    *                                               
  SORT FIELDS=(1,3,CH,A,81,8,ZD,D)                               
  INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,5))                   
  OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
  OUTFIL BUILD=(1,80),INCLUDE=(81,8,ZD,EQ,1)           
/*                                                     
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 07, 2010 3:30 pm
 Reply with quote
Hi,

It is working fine.
Can you please explain the RESTART command used in the above code.

Thanks in Advance,
Hari
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
PostPosted: Fri May 07, 2010 3:34 pm
 Reply with quote
Hello Hari,

RESTART=(1,5)
It will restart the seq number form 1 depending upon first 5 bytes
means if ur i/p is

11111
11111
22222
22222
33333

so o/p will be

11111 1
11111 2
22222 1
22222 2
33333 1

Hope its clear now
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Fri May 07, 2010 3:37 pm
 Reply with quote
hariharan4 wrote:

Can you please explain the RESTART command used in the above code.

RTFM
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 07, 2010 4:17 pm
 Reply with quote
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
111 11 ...
111 11 ...
111 11 ...
111 12 ...
111 12 ...
222 11 ...
222 11 ...
222 12 ...
333 11 ...
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,3,CH,A,81,8,ZD,D)
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,6))
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
OUTFIL BUILD=(1,80),INCLUDE=(81,8,ZD,EQ,1)
/*

The output for the above code is

111 12 ... (wrong)
222 12 ...
333 11 ...

The expected Output should be as below

111 11 ... (correct)
222 12 ...
333 11 ...

Thanks in advance
Hari
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
PostPosted: Fri May 07, 2010 4:44 pm
 Reply with quote
Hello Hari,

Above sort card providing correct output

Code:
111 11
222 11
333 11


Are you sure that 222 12 instead of 222 11 ... ?
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 07, 2010 9:24 pm
 Reply with quote
Hi,

very sorry to bother you again and again!!!
we can understand the RESTART command given in INREC.
But what is the purpose of RESTART = (1,3) in the OUTREC? what it exactly does in the above code? It is only bit confusing for us.

Yes ur correct. The expected output should be:

111 11 ...
222 11 ...
333 11 ...

Thanks In Advance,
Hari
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri May 07, 2010 9:30 pm
 Reply with quote
Hari,

Unless I have misunderstood the requirement isn't it just getting the FIRST record based on the first 3 bytes as key?

IF so SELECT operator with FIRST parm should give the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                   
//TOOLMSG  DD SYSOUT=*                       
//DFSMSG   DD SYSOUT=*                       
//IN       DD *                               
111 XX ...                                   
111 XX ...                                   
111 XX ...                                   
111 YY ...                                   
111 YY ...                                   
222 YY ...                                   
222 YY ...                                   
222 ZZ ...                                   
333 XX ...                                   
//OUT      DD SYSOUT=*                       
//TOOLIN   DD *                               
  SELECT FROM(IN) TO(OUT) ON(1,3,CH) FIRST   
//*
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 07, 2010 9:39 pm
 Reply with quote
Hi Kolusu,

I think you mistook the requirement. It is not just getting the FIRST record based on the first 3 bytes as key.
Actually we are almost done with the output but we couldn't understand the part of the code
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
That too especially RESTART command in the above code.


Thanks in Advance,
Hari
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri May 07, 2010 9:53 pm
 Reply with quote
hariharan4,

Hmm so you want to get the max repeated key but you just need 1 key in the output?

If that is the case then you can use the following JCL

Code:

//STEP0100 EXEC PGM=ICETOOL                               
//TOOLMSG  DD SYSOUT=*                                     
//DFSMSG   DD SYSOUT=*                                     
//IN       DD *                                           
111 XX ...                                                 
111 XX ...                                                 
111 XX ...                                                 
111 YY ...                                                 
111 YY ...                                                 
111 YY ...                                                 
111 YY ...                                                 
222 YY ...                                                 
222 YY ...                                                 
222 ZZ ...                                                 
333 XX ...                                                 
//OUT      DD SYSOUT=*                                     
//TOOLIN   DD *                                           
  SELECT FROM(IN) TO(OUT) ON(1,3,CH) FIRST USING(CTL1)     
//CTL1CNTL DD *                                           
  INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,6))             
  SORT FIELDS=(1,3,CH,A,81,8,ZD,D)     
  OUTFIL FNAMES=OUT,BUILD=(1,80)                   
//*
Back to top
View user's profile Send private message
sguhan

New User


Joined: 15 Mar 2010
Posts: 9
Location: Chennai
PostPosted: Mon May 10, 2010 1:58 pm
 Reply with quote
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
111 XX ...
111 XX ...
111 XX ...
111 YY ...
111 YY ...
222 YY ...
222 YY ...
222 ZZ ...
333 XX ...
/*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,3,CH,A,81,8,ZD,D)
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,5))
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))
OUTFIL BUILD=(1,80),INCLUDE=(81,8,ZD,EQ,1)
/*


Could someone please explain how this code logically obtains the record which occurs maximum number of times for a particular key?
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Mon May 10, 2010 2:06 pm
 Reply with quote
Ekta has already explained part of this.
You can ask something specific, you did not get in it.
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 14, 2010 10:10 am
 Reply with quote
Hi,

The above ICETOOL and SORT code is not working fine for the below input:

//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/23/2010 RAGU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/05/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=(1,18,CH,A,81,8,ZD,D)
INREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,29))
OUTREC OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,18))
OUTFIL BUILD=(1,88),INCLUDE=(81,8,ZD,EQ,1)
/*

The output is :
1111111111111XXXX1 04/24/2009
2222222222222XXXX2 04/01/2010
2222222222222XXXX3 04/01/2010

The expected output should be as follows:
1111111111111XXXX1 04/24/2009
1111111111111XXXX1 04/25/2009(since this also occurs the maximum)
1111111111111XXXX1 04/24/2010(since this also occurs the maximum)
2222222222222XXXX2 04/01/2010
2222222222222XXXX3 04/01/2010

Thanks in Advance,
Hari
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
PostPosted: Fri May 14, 2010 10:22 am
 Reply with quote
Yes you are right, It will give first record in case of matching max count.
Do you want all of the records in that case?
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 14, 2010 11:31 am
 Reply with quote
Hi,

Yes we need all the matching maximum records for the given input. In the below input for the given key 1111111111111XXXX1 the maximum occurrance records are 04/24/2009 , 04/25/2009 and 04/24/2010. similarly for the other keys also. Hope your clear with the requirement now.

1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/24/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/25/2009 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/24/2010 ARUN
1111111111111XXXX1 04/23/2010 RAGU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX2 04/05/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV
2222222222222XXXX2 04/01/2010 RAJU
2222222222222XXXX3 04/01/2010 PRAV

Thanks in Advance,
Hari
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
PostPosted: Fri May 14, 2010 12:17 pm
 Reply with quote
Is your input data already sorted on the first 2 fields?
Back to top
View user's profile Send private message
hariharan4

New User


Joined: 06 Aug 2008
Posts: 22
Location: chennai
PostPosted: Fri May 14, 2010 12:28 pm
 Reply with quote
Yes
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri May 14, 2010 9:10 pm
 Reply with quote
hariharan4,

Use the following DFSORT JCL which will give you the desired results
Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
1111111111111XXXX1 04/24/2009 ARUN                                   
1111111111111XXXX1 04/24/2009 ARUN                                   
1111111111111XXXX1 04/24/2009 ARUN                                   
1111111111111XXXX1 04/25/2009 ARUN                                   
1111111111111XXXX1 04/25/2009 ARUN                                   
1111111111111XXXX1 04/25/2009 ARUN                                   
1111111111111XXXX1 04/24/2010 ARUN                                   
1111111111111XXXX1 04/24/2010 ARUN                                   
1111111111111XXXX1 04/24/2010 ARUN                                   
1111111111111XXXX1 04/23/2010 RAGU                                   
2222222222222XXXX2 04/01/2010 RAJU                                   
2222222222222XXXX2 04/01/2010 RAJU                                   
2222222222222XXXX2 04/05/2010 RAJU                                   
2222222222222XXXX3 04/01/2010 PRAV                                   
2222222222222XXXX2 04/01/2010 RAJU                                   
2222222222222XXXX3 04/01/2010 PRAV                                   
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  SORT FIELDS=(1,18,CH,A,89,8,CH,A,81,8,CH,D),EQUALS                 
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,30))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(81,8,ZD,EQ,1),PUSH=(89:ID=8))           
  OUTREC IFTHEN=(WHEN=INIT,                                         
  OVERLAY=(89:SEQNUM,8,ZD,RESTART=(1,18),                           
           97:SEQNUM,8,ZD,RESTART=(1,30))),                         
  IFTHEN=(WHEN=GROUP,BEGIN=(89,8,ZD,EQ,1),PUSH=(105:81,8),HIT=NEXT),
  IFTHEN=(WHEN=GROUP,BEGIN=(97,8,ZD,EQ,1),PUSH=(81:81,8))           
  OUTFIL INCLUDE=(105,8,CH,EQ,81,8,CH),BUILD=(1,80)                 
//*
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(F1 & F2) and writ... JCL & VSAM 8
No new posts To get the count of rows for every 1 ... DB2 3
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
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