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

Count the duplicates and number of duplicated groups


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Tue Jun 24, 2008 7:35 pm
Reply with quote

There is the next FB/80 file:

001aaaaaa9990..... (up to 70 chars, last 10 are always empty)
002ccccccc9990.....
001bbbbbb9990.....
002dddddd9991.....
002eeeeee9990.....
003oooooo9991.....
004nnnnnn9991.....
003uuuuuu9991.....
002xxxxxx9990.....

The key = first 3 chars + 4 chars from 10th pos

The requirement is to create a report (can be FB/80) of records duplicated by the key:

001aaaaaa9990..... 00000001 (seqnum)
001bbbbbb9990..... 00000003

002ccccccc9990..... 00000002
002eeeeee9990..... 00000005
002xxxxxx9990..... 00000009

003oooooo9991..... 00000006
003uuuuuu9991..... 00000008
=====================
Groups: 3
Duplicates: 7

Are there any ways to create this report by using just one SELECT? My problem is to count groups in this case...
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 24, 2008 9:43 pm
Reply with quote

Andrew Shinkarev,

How did you come up with a total of 7 for the duplicates? Also why did you miss the records ?

Code:

002DDDDDD9991  - 4th record

004NNNNNN9991  - 7th record


You need to define the rules to get the total of duplicates

Kolusu
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Tue Jun 24, 2008 9:47 pm
Reply with quote

7 is amount of all the records in the report.

The records you've shown are different because of the key:

002 <> 004 (9991=9991)
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Jun 24, 2008 10:33 pm
Reply with quote

Andrew Shinkarev,

The following DFSORT JCL will give you the desired results. Getting the unique count of the groups requires an additional pass

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN       DD *                                                     
001AAAAAA9990.....                                                 
002CCCCCC9990.....                                                 
001BBBBBB9990.....                                                 
002DDDDDD9991.....                                                 
002EEEEEE9990.....                                                 
003OOOOOO9991.....                                                 
004NNNNNN9991.....                                                 
003UUUUUU9991.....                                                 
002XXXXXX9990.....                                                 
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  SELECT FROM(IN) TO(T1) ON(1,3,CH) ON(10,4,CH) ALLDUPS USING(CTL1)
  COPY FROM(T1) USING(CTL2)                                         
//CTL1CNTL DD *                                                     
  INREC OVERLAY=(81:SEQNUM,8,ZD)                                   
  OUTFIL FNAMES=T1,BUILD=(1,70,X,81,8,X)                           
//CTL2CNTL DD *                                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,RESTART=(1,3))), 
  IFTHEN=(WHEN=(81,8,ZD,EQ,1),OVERLAY=(90:C'1'))                   
  OUTFIL FNAMES=OUT,REMOVECC,                                       
  BUILD=(1,80),                                                     
  TRAILER1=('======================',/,                             
            'GROUPS     :  ',TOT=(90,1,ZD,M10,LENGTH=8),/,         
            'DUPLICATES :  ',COUNT)                                 
/*                                                                 
Back to top
View user's profile Send private message
Ramya A

Active User


Joined: 26 Jul 2004
Posts: 104

PostPosted: Tue Jun 24, 2008 10:41 pm
Reply with quote

Here is the logic that you can use with SORT:

Step1:

Append "00000001" to all the records of the file in pos 71 using OUTREC FIELDS=(1,70,C'00000001').

Step2:

Do a SORT with the key field summarizing the last column (Using SORT FIELDS=(1,3,A,10,4,A) AND SUM FIELDS=(71,8,C'00000001')).
Now you'll have only one record for each key with the number of duplicates for that key as the last column (as u requested).

Hope this helps... I don't have the SORT jcl for this right now.. let me know if you need one.
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Wed Jun 25, 2008 11:12 pm
Reply with quote

Thank you, Skolusu.

For me it's quite good example of IFTHEN usage.

I've spent some time for the test and found the problem with the next input:

Code:

001AAAAAA9990.....
001CCCCCC9991.....
001BBBBBB9990.....
003DDDDDD9991.....
001EEEEEE9991.....
002OOOOOO9991.....
004NNNNNN9991.....
002UUUUUU9991.....
001XXXXXX9991.....


Code:

001AAAAAA9990.....                                                     00000001
001BBBBBB9990.....                                                     00000003
001CCCCCC9991.....                                                     00000002
001EEEEEE9991.....                                                     00000005
001XXXXXX9991.....                                                     00000009
002OOOOOO9991.....                                                     00000006
002UUUUUU9991.....                                                     00000008
======================                                                         
GROUPS     :         2                                                         
DUPLICATES :         7                                                         


But here should be 3 groups. Maybe it makes sense to copy the key as one string by INREC somewhere to the right of 80th pos to check it then by RESTART. This approach could help as well to separate the groups by blank line as requested by the customer (by SECTION). I'll try to realize it, but any help would be greatly appreciated.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Jun 26, 2008 2:22 am
Reply with quote

Quote:

But here should be 3 groups. Maybe it makes sense to copy the key as one string by INREC somewhere to the right of 80th pos to check it then by RESTART. This approach could help as well to separate the groups by blank line as requested by the customer (by SECTION). I'll try to realize it, but any help would be greatly appreciated.


Andrew Shinkarev,

You got only 2 groups because I just considered only the first 3 bytes as a group. I guess you need to even consider the 10, 4 bytes also as part of the group. Change your CTL2 control cards to the following and you will get the desired results

Code:

//CTL2CNTL DD *                                           
  INREC IFTHEN=(WHEN=INIT,                                 
       OVERLAY=(81:1,3,10,4,SEQNUM,8,ZD,RESTART=(81,7))), 
  IFTHEN=(WHEN=(88,8,ZD,EQ,1),OVERLAY=(96:C'1'))           
  OUTFIL FNAMES=OUT,REMOVECC,                             
  BUILD=(1,80),                                           
  TRAILER1=('======================',/,                   
            'GROUPS     :  ',TOT=(96,1,ZD,M10,LENGTH=8),/,
            'DUPLICATES :  ',COUNT)                       
/*                                                         
Back to top
View user's profile Send private message
Andrew Shinkarev

New User


Joined: 10 Jan 2008
Posts: 22
Location: Belarus

PostPosted: Thu Jun 26, 2008 7:16 pm
Reply with quote

Thank you very much, Skolusu, the job works perfectly
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 To get the count of rows for every 1 ... DB2 3
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Substring number between 2 characters... DFSORT/ICETOOL 2
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Generate random number from range of ... COBOL Programming 3
Search our Forums:

Back to Top