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

Count number of duplicated records


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

New User


Joined: 23 Mar 2008
Posts: 13
Location: china

PostPosted: Tue Nov 11, 2008 2:19 pm
Reply with quote

Hi all:

I got requirement like this,

input file:

aaa 111 222
bbb 333 444
aaa 555 666
bbb 777 888
ccc 999 000

output file(the number of dup shoud be list on left side):

aaa 111 222
aaa 555 666 2
bbb 333 444
bbb 777 888 2
ccc 999 000 1

OR output file can be like this:

aaa 111 222 2
aaa 555 666 2
bbb 333 444 2
bbb 777 888 2
ccc 999 000 1

either one is acceptable, so can anyone help me come up with a solution?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Tue Nov 11, 2008 2:35 pm
Reply with quote

did You care to search the forum for hints ??

- i know I am reusing the same answer,
- but since the questions are most of the time related to already discussed topics
- I found useful to have a reply ready and simply paste it in the box icon_biggrin.gif
Back to top
View user's profile Send private message
liangl

New User


Joined: 23 Mar 2008
Posts: 13
Location: china

PostPosted: Tue Nov 11, 2008 5:16 pm
Reply with quote

I've search the forum, but I didn't find out the answer, I know some topic may help me such as 'Icetool sort for proper trailer totals', but after I read that, it is different with my request. Can you point out which subject is more helpful for me? Thanks
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue Nov 11, 2008 11:10 pm
Reply with quote

liangl,

The following DFSORT/ICETOOL JCL will give you the desired results.

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN       DD *                                                     
AAA 111 222                                                         
BBB 333 444                                                         
AAA 555 666                                                         
BBB 777 888                                                         
CCC 999 000                                                         
//T1       DD DSN=&&T1,DISP=(,PASS),SPACE=(CYL,(1,1),RLSE)         
//OUT      DD SYSOUT=*                                             
//TOOLIN   DD *                                                     
  SORT FROM(IN) TO(T1) USING(CTL1)                                 
  SORT FROM(T1) TO(OUT) USING(CTL2)                                 
//CTL1CNTL DD *                                                     
  OPTION EQUALS                                                     
  SORT FIELDS=(1,3,CH,A)                                           
  OUTREC BUILD=(1,12,SEQNUM,2,ZD,RESTART=(1,3),2X)                 
  OUTFIL REMOVECC,                                                 
  SECTIONS=(1,3,TRAILER3=(1,3,15:MAX=(13,2,ZD,M11,LENGTH=2)))       
/*                                                                 
//CTL2CNTL DD *                                                     
  OPTION EQUALS                                                     
  SORT FIELDS=(1,3,CH,A,15,2,CH,D)                                 
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(15,2,CH,GT,C' '),PUSH=(15:15,2)),
  IFTHEN=(WHEN=(13,2,ZD,LT,15,2,ZD),OVERLAY=(13:2X))               
  OUTFIL OMIT=(4,11,CH,EQ,C' '),BUILD=(1,14)                       
/*                                                                 


The output from this job is
Code:

AAA 111 222   
AAA 555 666 02
BBB 333 444   
BBB 777 888 02
CCC 999 000 01


If you want the following output

Code:

AAA 111 222 02
AAA 555 666 02
BBB 333 444 02
BBB 777 888 02
CCC 999 000 01


Change the build statement in CTL2CNTL as follows
Code:
BUILD=(1,12,15,2)
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Nov 12, 2008 8:50 pm
Reply with quote

liangl,

Here's another way of achieving the same. Output will be as per format-1.
Code:
//STEP01  EXEC PGM=ICETOOL                                 
//TOOLMSG   DD SYSOUT=*                                     
//DFSMSG    DD SYSOUT=*                                     
//OUT       DD SYSOUT=*                                     
//T1        DD DSN=&&T1,DISP=(,PASS)                       
//IN        DD *                                           
AAA 111 222                                                 
AAA 111 222                                                 
BBB 333 444                                                 
AAA 555 666                                                 
BBB 777 888                                                 
CCC 999 000                                                 
//TOOLIN    DD *                                           
  SORT FROM(IN) TO(T1) USING(CTL1)                         
  SELECT FROM(T1) TO(OUT) ON(1,11,CH) LAST                 
//CTL1CNTL  DD *                                           
  SORT FIELDS=(1,11,CH,A)                                   
  OUTFIL FNAMES=T1,REMOVECC,                               
         SECTIONS=(1,3,TRAILER3=(1,12,COUNT=(M11,LENGTH=2)))

OUT
Code:
AAA 111 222   
AAA 555 666 03
BBB 333 444   
BBB 777 888 02
CCC 999 000 01
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Nov 12, 2008 10:56 pm
Reply with quote

liangl,

The above ICETOOL assumes that a record will not occur multiple times. My bad...I dint notice that my own example violates that. icon_mad.gif

For your sample data, it gives the required output.
Code:
AAA 111 222   
AAA 555 666 02
BBB 333 444   
BBB 777 888 02
CCC 999 000 01
Back to top
View user's profile Send private message
liangl

New User


Joined: 23 Mar 2008
Posts: 13
Location: china

PostPosted: Wed Nov 12, 2008 11:12 pm
Reply with quote

right, I also tried input like this:

AAA 111 222
BBB 333 444
BBB 333 444
AAA 555 666
BBB 777 888
CCC 999 000

you control statement can't handle it, but we can add more flags to indetify the orignal records.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA

PostPosted: Thu Nov 13, 2008 12:02 am
Reply with quote

Note that Kolusu's job can handle all of these situations.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Nov 13, 2008 11:26 pm
Reply with quote

liangl,

Here's a modified version which handles "entire record" duplicates.
Code:
//STEP01  EXEC PGM=ICETOOL                                       
//TOOLMSG   DD SYSOUT=*                                           
//DFSMSG    DD SYSOUT=*                                           
//OUT       DD SYSOUT=*                                           
//T1        DD DSN=&&T1,DISP=(,PASS)                             
//IN        DD *                                                 
AAA 111 222                                                       
AAA 111 222                                                       
BBB 333 444                                                       
AAA 555 666                                                       
BBB 777 888                                                       
BBB 333 444                                                       
CCC 999 000                                                       
//TOOLIN    DD *                                                 
  SORT FROM(IN) TO(T1) USING(CTL1)                               
  SELECT FROM(T1) TO(OUT) ON(1,11,CH) ON(15,2,CH) LAST USING(CTL2)
//CTL1CNTL  DD *                                                 
  SORT FIELDS=(1,11,CH,A)                                         
  OUTFIL FNAMES=T1,REMOVECC,                                     
         SECTIONS=(1,3,TRAILER3=(1,12,COUNT=(M11,LENGTH=2))),     
         BUILD=(1,14,SEQNUM,2,ZD,RESTART=(1,3))                   
//CTL2CNTL  DD *                                                 
  INREC IFTHEN=(WHEN=(15,2,CH,EQ,C' '),OVERLAY=(15:13,2))
  OUTFIL FNAMES=OUT,BUILD=(1,14)     
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 Compare only first records of the fil... SYNCSORT 7
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
Search our Forums:

Back to Top