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 two files - With key at same position.

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

New User


Joined: 30 Jan 2007
Posts: 6
Location: Chennai

PostPosted: Wed Aug 12, 2009 12:23 pm    Post subject: SPLICE two files - With key at same position.
Reply with quote

Hi,

I have the below requirement to merge two files laterally.

File details
File A - FB, 80, No of fields - 7, Key field is at position 1 to 8, the file sorted on key field.
File B - FB, 80, No of fields - 3, Key field is at position 1 to 8, the file sorted on key field.

File A - data (Key is not unique)

AAA12345 Record1
AAA12345 Record2
AAA12456 Record3
BBC99999 Record4
BBC99999 Record5
CDE34561 Record6

File B - data (Key is unique)

AAA12456 Phone1
BBC99999 Phone3

Expected Output file ( The output should contains all the records in file A, If file B Key column has any match with File A key column, then the corresponding details has to be appended at the end of File A)

AAA12345 Record1
AAA12345 Record2
AAA12456 Record3 Phone1
BBC99999 Record4 Phone3
BBC99999 Record5 Phone3
CDE34561 Record6

I have tried the below SPLICE technique, but not getting the expected output. Please help. Thanks.

COPY FROM(SORTIN1) TO(TEMP1) USING(CTL1)
COPY FROM(SORTIN2) TO(TEMP1) USING(CTL2)
SPLICE FROM(TEMP1) TO(SORTOUT) ON(1,8,CH) WITHALL KEEPNODUPS -
WITH(81,72)

CNTL1 - OUTREC BUILD=(1:1,80,81:80X)
CNTL2 - OUTREC FIELDS=(1:1,8,9:72X,81:9,72,153:8X)
CNTL3 - OUTFIL FNAMES=SORTOUT,OUTREC=(1:1,160)

Here SORTIN1 is File A, SORTIN2 is File B
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 Aug 12, 2009 12:39 pm    Post subject:
Reply with quote

What are the unexpected results.

One common error I make is by reading in the two datasets in the wrong order. Try this, reading in the file without duplicates first.
Code:

COPY FROM(SORTIN2) TO(TEMP1) USING(CTL2)
COPY FROM(SORTIN1) TO(TEMP1) USING(CTL1)
SPLICE FROM(TEMP1) TO(SORTOUT) ON(1,8,CH) WITHALL KEEPNODUPS -
WITH(81,72)
Back to top
View user's profile Send private message
craig2020

New User


Joined: 30 Jan 2007
Posts: 6
Location: Chennai

PostPosted: Wed Aug 12, 2009 1:26 pm    Post subject: Reply to: SPLICE two files - With key at same position.
Reply with quote

Hi Expat,
Thanks for your response..

I missed to mention one more thing in File B.

File B - data (Key is unique)

AAA12456 Phone1
BBC99999 Phone3
XXX99999 Phone3

Still the expected output in post 1 holds true.

The result I am getting is.

AAA12345 Record1
AAA12345 Record2
AAA12456 Record3 Phone1
BBC99999 Record4 Phone3
BBC99999 Record5 Phone3
CDE34561 Record6
XXX99999 <space> Phone3

I could not able to remove the record number 7, which is marked in Bold.
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Wed Aug 12, 2009 9:16 pm    Post subject:
Reply with quote

Craig,

Since both input files are of the same lrecl and recfm , we really don't have to use splice to get the desired results. We can use the new function when=group to push the contents of file2 on to file 1 record

Use the following DFSORT JCL. We concatenate 1 line record with HDR before each file so as to identify as to which file the record came from.


Code:

//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//SORTIN   DD *                                                     
HDR                                                                 
//         DD *                                                     
AAA12456 PHONE1                                                     
BBC99999 PHONE3                                                     
XXX99999 PHONE3                                                     
//         DD *                                                     
HDR                                                                 
//         DD *                                                     
AAA12345 RECORD1                                                     
AAA12345 RECORD2                                                     
AAA12456 RECORD3                                                     
BBC99999 RECORD4                                                     
BBC99999 RECORD5                                                     
CDE34561 RECORD6                                                     
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,3,CH,EQ,C'HDR'),PUSH=(81:ID=1)) 
  SORT FIELDS=(1,8,CH,A),EQUALS                                     
  OUTREC IFTHEN=(WHEN=INIT,OVERLAY=(83:SEQNUM,8,ZD,RESTART=(1,8))), 
  IFTHEN=(WHEN=GROUP,BEGIN=(83,8,ZD,EQ,1),PUSH=(20:10,7,82:81,1)),   
  IFTHEN=(WHEN=(81,2,ZD,EQ,22),OVERLAY=(20:7X))                     
  OUTFIL BUILD=(1,80),INCLUDE=(81,2,ZD,GE,21,AND,1,3,CH,NE,C'HDR')   
/*
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 Unable to create multiple files using... mbattu COBOL Programming 3 Fri May 05, 2017 5:35 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Dynamic split of files under groups sril.krishy DFSORT/ICETOOL 4 Mon Apr 17, 2017 1:09 pm
No new posts Compare two files and subtract values ameetmund DFSORT/ICETOOL 7 Fri Mar 31, 2017 3:35 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm


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