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

To merge data from 2 files using Splice
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Bhagyashree Panigrahi

New User


Joined: 03 Mar 2009
Posts: 7
Location: Pune
PostPosted: Tue Mar 03, 2009 2:52 pm
 Reply with quote
Hi,

I need to compare 2 files and replace the field 2 in file 1 whenever a match is found in file 2 with the related field value. I have used ICETOOL SPLICE. However for more than 1 match (duplicates), the data is not getting replaced.

Example -
File 1 -

Code:

Field1     Key Field      Field2
------------------------------------
ABC         PPP           111
DEF         QQQ           222
GHI         QQQ           333
IJK         RRR           444


Position of fields in file 1-
Field 1 - Pos 1, length - 12 bytes
Key Field - Pos 73,length - 6 bytes
Field 2 - Pos 79,length - 5 bytes

File 2 -

Code:

Key field   Field2
-----------------------
QQQ         XXX
RRR         YYY


Position of fields in file 2-
Key Field - Pos 1,length - 6 bytes
Field 2 - Pos 7,length - 5 bytes

Output required -

Code:

Field1     Key field      Field2
-------------------------------------
ABC         PPP           111
DEF         QQQ           XXX
GHI         QQQ           XXX
IJK         RRR           YYY


Output I am getting is -

Code:

Field1     Key field      Field2
-------------------------------------
ABC         PPP           111
DEF         QQQ           XXX
GHI         QQQ           333
IJK         RRR           YYY


JCL step -

Code:

//ICESTEP  EXEC PGM=ICETOOL                                       
//TOOLMSG  DD   SYSOUT=*                                         
//DFSMSG   DD   SYSOUT=*                                         
//IN1      DD  DISP=SHR, DSN= File1                 
//IN2      DD  DISP=SHR, DSN=File 2                 
//T1       DD  DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(300,50),RLSE),     
//             DISP=(MOD,PASS)                                   
//OUT12    DD  DSN=Output file,                         
//             DISP=(NEW,CATLG,DELETE),                           
//             UNIT=SYSDA,SPACE=(CYL,(100,50),RLSE)                   
//TOOLIN   DD *                                                   
  COPY FROM(IN1) TO(T1) USING(CTL1)                               
  COPY FROM(IN2) TO(T1) USING(CTL2)
  SPLICE FROM(T1) TO(OUT12) ON(13,6,CH) WITH(19,5) -         
  WITHALL KEEPNODUPS                                         
/*                                                           
//CTL1CNTL DD *                                             
  OUTREC FIELDS=(1:1,12,13:73,11)                           
/*                                                           
//CTL2CNTL DD *                                             
  OUTREC FIELDS=(1:12X,13:1,6,19:7,5)                       
/*
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Tue Mar 03, 2009 9:54 pm
 Reply with quote
Bhagyashree Panigrahi,


Which file has the duplicates? Does both files have duplicates?
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: Tue Mar 03, 2009 10:44 pm
 Reply with quote
Bhagyashree,

Assuming file1 has duplicates and file2 doesn't, as shown in your example, here's a DFSORT/ICETOOL job that will do what you asked for:

Code:

//S1  EXEC PGM=ICETOOL
//TOOLMSG  DD   SYSOUT=*
//DFSMSG   DD   SYSOUT=*
//IN1      DD  DISP=SHR,DSN=File1
//IN2      DD  DISP=SHR,DSN=File2
//T1       DD  DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(300,50),RLSE),
//             DISP=(MOD,PASS)
//OUT      DD  DSN=Output file,
//             DISP=(NEW,CATLG,DELETE),
//             UNIT=SYSDA,SPACE=(CYL,(100,50),RLSE)
//TOOLIN   DD *
COPY FROM(IN2) TO(T1) USING(CTL1)
COPY FROM(IN1) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(13,6,CH) WITH(1,18) -
  WITHALL KEEPNODUPS
/*
//CTL1CNTL DD *
  INREC BUILD=(13:1,6,19:7,5)
/*
//CTL2CNTL DD *
  INREC BUILD=(1,12,13:73,6,19:79,5)
/*
Back to top
View user's profile Send private message
Bhagyashree Panigrahi

New User


Joined: 03 Mar 2009
Posts: 7
Location: Pune
PostPosted: Wed Mar 04, 2009 1:30 pm
 Reply with quote
Thanks Frank.

Its working!

I have one question though, if we were to use OUTREC, what would be the syntax and if not, why shouldn't we use it? If possible, can you also explain what was the error in the JCL I had provided?

Thanks,
Bhagyashree
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: Wed Mar 04, 2009 11:13 pm
 Reply with quote
You can use OUTREC or INREC interchangeably here.

Since input file1 has duplicates and input file2 does not have duplicates, T1 must have the input file2 records followed by the input file1 records in order for WITHALL to work.

Notice that I do:

COPY IN2 to T1
COPY IN1 to T1

whereas you do:

COPY IN1 to T1
COPY IN2 to T1

That also changes the WITH field you need to use.
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 Store the data for fixed length COBOL Programming 1
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 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