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

SPLICE option when there are duplicate records in input file


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
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
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: 8797
Location: Welsh Wales

PostPosted: Wed Mar 19, 2008 12:53 pm
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
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: 8797
Location: Welsh Wales

PostPosted: Wed Mar 19, 2008 8:11 pm
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts Access to non cataloged VSAM file JCL & VSAM 18
Search our Forums:

Back to Top