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
 

 

To merge data from 2 files using Splice

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: To merge data from 2 files using Splice
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    Post subject:
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 Moderator


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

PostPosted: Tue Mar 03, 2009 10:44 pm    Post subject:
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    Post subject: Reply to: To merge data from 2 files using Splice
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 Moderator


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

PostPosted: Wed Mar 04, 2009 11:13 pm    Post subject:
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    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 Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts Overriding PS data in rexx Shaheen Shaik CLIST & REXX 8 Fri Mar 03, 2017 5:08 pm
No new posts To Merge mutliple records into a sing... anandgbe DFSORT/ICETOOL 6 Wed Feb 22, 2017 8:49 am


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