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
 

 

SPLICE option when there are duplicate records in input file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Smita Pattnaik

New User


Joined: 19 Mar 2008
Posts: 10
Location: Noida

PostPosted: Wed Mar 19, 2008 12:17 pm    Post subject: SPLICE option when there are duplicate records in input file
Reply with quote

Hi All,

File1 is having duplicate User IDs(starting position 5 length 5) I need to compare the user id field of file 1 with the user id field of file 2 (starting position 5, length 5), File2 is having no duplicates,
if the user id matches then the complete record from file 1 has to be written to the output file.

Layout of file1: (It has user id from starting position 5 and length 5, the user id field has duplicates but no two rows are same.)
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000001 900 AA4UT ALMA ALEJANDRE 3363 ACUNA LAMP PLANT 3363 21101
000002 740 AA4UT ALMA ALEJANDRE 3500 SPECIAL PACK, INC. 3500 50027
000003 709 AC3UT CHOJNACKA, ANITA 3365 OAKVILLE LAMP PLANT MULT 21100
000004 740 AC9UT AMY CONDON 3500 SPECIAL PACK, INC. 3500 50027
000005 914 AECUT ALAN CHAPMAN 3174 C.E.N MULT 21100

Layout of filel 2 (no duplicates in user id field - starting position 5, length 5)
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000001 AA4UT
000002 AC3UT
000003 CP7UT
000004 DH4UT
000005 DPSUT

Desired output:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000001 900 AA4UT ALMA ALEJANDRE 3363 ACUNA LAMP PLANT 3363 21101
000002 740 AA4UT ALMA ALEJANDRE 3500 SPECIAL PACK, INC. 3500 50027
000003 709 AC3UT CHOJNACKA, ANITA 3365 OAKVILLE LAMP PLANT MULT 21100

When I am using ICETOOL and SPLICE, WITHALL option i.e
000010 //STEP000E EXEC PGM=ICETOOL
000011 //TOOLMSG DD SYSOUT=*
000012 //DFSMSG DD SYSOUT=*
000013 //SYSOUT DD SYSOUT=*
000014 //SYSDUMP DD SYSOUT=*
000015 //CONCAT DD DSN=&HLQ..TEST.RC.USER.FILE1,
000016 // DISP=SHR
000017 // DD DSN=&HLQ..TEST.RC.USER.FILE2,
000018 // DISP=SHR
000019 //OUT DD DSN=&HLQ..LISP1PT.TEST.RC.USER.DATA,
000020 // UNIT=SYSDA,SPACE=(CYL,(10,2),RLSE),
000021 // DCB=(RECFM=FB,LRECL=190,BLKSIZE=3800),
000022 // DISP=(OLD,CATLG,DELETE)
000023 //TOOLIN DD *
000024 SPLICE FROM(CONCAT) TO(OUT) ON(5,5,CH) WITHALL WITH(5,5)


I am getting the following result:
000001 900 AA4UT ALMA ALEJANDRE 3363 ACUNA LAMP PLANT 3363 21101
000002 900 AA4UT ALMA ALEJANDRE 3363 ACUNA LAMP PLANT 3363 21101
000003 709 AC3UT CHOJNACKA, ANITA 3365 OAKVILLE LAMP PLANT MULT 21100

i.e for the second row(duplicate user id row) should be
000002 740 AA4UT ALMA ALEJANDRE 3500 SPECIAL PACK, INC. 3500 50027

Please let me know what option should I use to get the desired result.

Regards,
Smita
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Wed Mar 19, 2008 12:53 pm    Post subject:
Reply with quote

I can vaguely recall a similar problem in the dark and distant past, and for some reason when I changed the input order of the files the results were as required.

I suggest that you try the same, by changing your CONCAT DD to read FILE2 before FILE1.

It certainly worked for me.
Back to top
View user's profile Send private message
Smita Pattnaik

New User


Joined: 19 Mar 2008
Posts: 10
Location: Noida

PostPosted: Wed Mar 19, 2008 4:11 pm    Post subject:
Reply with quote

If I alter the files i.e,

000010 //STEP000E EXEC PGM=ICETOOL
000011 //TOOLMSG DD SYSOUT=*
000012 //DFSMSG DD SYSOUT=*
000013 //SYSOUT DD SYSOUT=*
000014 //SYSDUMP DD SYSOUT=*
000015 //CONCAT DD DSN=&HLQ..TEST.RC.USER.FILE2, DISP=SHR
000017 // DD DSN=&HLQ..TEST.RC.USER.FILE1,DISP=SHR
000019 //OUT DD DSN=&HLQ..LISP1PT.TEST.RC.USER.DATA,
000020 // UNIT=SYSDA,SPACE=(CYL,(10,2),RLSE),
000021 // DCB=(RECFM=FB,LRECL=190,BLKSIZE=3800),
000022 // DISP=(OLD,CATLG,DELETE)
000023 //TOOLIN DD *
000024 SPLICE FROM(CONCAT) TO(OUT) ON(5,5,CH) WITHALL WITH(1,190)

I am getting the desired result with the above mentioned example.

file 2:
But with the following example its not working:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000001 AA2UT
000002 AC3UT
000003 CP7UT
000004 DH4UT
000005 DPSUT

File 1

=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000001 900 AA4UT ALMA ALEJANDRE 3363 ACUNA LAMP PLANT 3363 21101 101
000002 740 AA4UT ALMA ALEJANDRE 3500 SPECIAL PACK, INC. 3500 50027
000003 709 AC3UT CHOJNACKA, ANITA 3365 OAKVILLE LAMP PLANT MULT 21100
000004 740 AC9UT AMY CONDON 3500 SPECIAL PACK, INC. 3500 50027
000005 914 AECUT ALAN CHAPMAN 3174 C.E.N MULT 21100


the output comes out as :
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000001 740 AA4UT ALMA ALEJANDRE 3500 SPECIAL PACK, INC. 3500 50027
000002 709 AC3UT CHOJNACKA, ANITA 3365 OAKVILLE LAMP PLANT MULT 21100

which is not desired. It should come as:
=COLS> ----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
000001 709 AC3UT CHOJNACKA, ANITA 3365 OAKVILLE LAMP PLANT MULT 21100

as AC3UT is the only user id present in both the files. What should be done to get the desired result.
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Wed Mar 19, 2008 8:11 pm    Post subject:
Reply with quote

I have had a look throuh my JCL archive and found something similar

My job deals with the different operations with seperate steps due to the volume of data being processed

In the first step I selected only duplicate records using
Code:

SELECT FROM(INFILE) TO(OUFILE)   ON(1,44,CH) ALLDUPS

And in the next step, the output file (OUFILE) is used as input for the splice
Code:
 SPLICE FROM(INFILE) TO(OUFILE)   ON(1,44,CH) WITH(101,20)


I'm sure that Frank or Kolusu will give you a more efficient coding icon_wink.gif
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Mar 19, 2008 10:46 pm    Post subject:
Reply with quote

smita,

The following DFSORT/ICETOOL JCl will give you the desired results

Code:

//STEP0100 EXEC PGM=ICETOOL                                         
//TOOLMSG  DD SYSOUT=*                                               
//DFSMSG   DD SYSOUT=*                                               
//IN1      DD DSN=YOUR DUPLICATE ID FILE,
//            DISP=SHR
//IN2      DD DSN=YOUR UNIQUE ID FILE,
//            DISP=SHR                                               
//T1       DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)       
//OUT      DD SYSOUT=*                                               
//TOOLIN   DD *                                                     
  COPY FROM(IN2) USING(CTL1)                                         
  COPY FROM(IN1) USING(CTL2)                                         
  SPLICE FROM(T1) TO(OUT) ON(5,5,CH) WITHALL WITH(01,190) USING(CTL3)
//CTL1CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(191:5,5)                                 
//CTL2CNTL DD *                                                     
  OUTFIL FNAMES=T1,OVERLAY=(191:5X)                                 
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=OUT,BUILD=(01,190),                                   
  INCLUDE=(5,5,CH,EQ,191,5,CH)     
/*
Back to top
View user's profile Send private message
Smita Pattnaik

New User


Joined: 19 Mar 2008
Posts: 10
Location: Noida

PostPosted: Thu Mar 20, 2008 10:17 am    Post subject:
Reply with quote

Hi Kolusu/expat,

Thanks a lot for all your help. Its working fine.

Cheers,
Smita
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 SORTJOIN - Copy Matched and Unmatched... Steve Ironmonger DFSORT/ICETOOL 5 Tue Jan 17, 2017 4:26 pm
No new posts Copy RECFM =VB TO FB file with RECL =... sahil41352 DFSORT/ICETOOL 3 Wed Dec 28, 2016 11:29 pm
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 31 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm


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