Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 Moving a COMP-3 Variable to a Numeric... ajayachander COBOL Programming 2 Thu Dec 14, 2017 5:46 pm
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts Compare VSAM files using DFSORT pshongal DFSORT/ICETOOL 5 Thu Nov 23, 2017 10:24 am
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us