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

Comparing flat files and updating based on matching criteria
IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
morpheus007

New User


Joined: 27 Dec 2005
Posts: 58
PostPosted: Tue Nov 13, 2007 5:06 pm
 Reply with quote
We have the following requirement:

FILE1:(INPUT FILE-->FILE3 has 1 record for every record from FILE1).
FIELD1-----FIELD2---FIELD3---FIELD4
------------------------------------------
------------------------------------------

FILE2:(INPUT FILE-->Used for matching with FILE1)
FIELD1-----FIELD2---FIELD3---FIELD4
------------------------------------------
------------------------------------------

FILE3:(OUTPUT FILE)
FIELD1-----FIELD2---FIELD3---FIELD4---FIELD5(From FIELD4 of FILE2)
------------------------------------------
------------------------------------------


Both FILE1 and FILE2 are flat files.We need the output on a third flat file FILE3.

The criteria is the following:
1.The number of records in FILE3 is same as number of records in FILE1.
2.FILE2 is used for matching.
3.FILE3 has 5 fields.The 4 fields of FILE1 and an additional field which would be FIELD4 of FILE2(Populated by either SPACES or valid value)
3.IF FIELD1/FIELD2 and FIELD3 values of FILE1 and FILE2 match then
The value of FIELD4 of FILE2 needs to be appended to that FILE1 record
and the 5 fields need to be written to FILE3.
4.For those FILE1 records where the 3 fields do not match the corresponding values in FILE2 we need SPACES for the last field in FILE3.

Kindly let me know if there is a solution for this using SYNCSORT or we need to write a program for this. I think I can solve this programmatically except that it might take longer.
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
PostPosted: Tue Nov 13, 2007 5:39 pm
 Reply with quote
morpheus,

You need to provide few more details on the field positions. Use this JCL for your requirement and change the same as per your field positions.

Code:
//STEP01   EXEC PGM=SYNCTOOL                                       
//TOOLMSG  DD SYSOUT=*                                             
//DFSMSG   DD SYSOUT=*                                             
//IN1      DD *                                                   
11223344                                                           
11223344                                                           
AABBCCDD                                                           
/*                                                                 
//IN2      DD *                                                   
11223388                                                           
11XX3388                                                           
AABBCCFF                                                           
/*                                                                 
//T1       DD DSN=&&X1,UNIT=SYSDA,SPACE=(CYL,(15,5),RLSE),         
//            DISP=(MOD,PASS)                                     
//OUT      DD DSN=HLQ.XX.Y7,DISP=(,CATLG,DELETE)               
//OU1      DD DSN=HLQ.XX.Y8,DISP=(,CATLG,DELETE)               
//SYSPRINT DD   SYSOUT=*                                           
//SYSOUT   DD   SYSOUT=*                                           
//TOOLIN   DD *                                                   
  COPY FROM(IN1) TO(T1) USING(CTL1)                               
  COPY FROM(IN2) TO(T1) USING(CTL2)                                 
  SPLICE FROM(T1) TO(OUT) ON(1,6,CH) WITH(82,1) WITH(9,2) -         
  USING(CTL3) WITHALL                                               
/*                                                                   
//CTL1CNTL DD *                                                     
  OUTREC FIELDS=(1,8,81:C'11')                                       
/*                                                                   
//CTL2CNTL DD *                                                     
  OUTREC FIELDS=(1,6,2X,7,2,81:C'22')                               
/*                                                                   
//CTL3CNTL DD *                                                     
  OUTFIL FNAMES=OUT,INCLUDE=(81,2,CH,EQ,C'12'),OUTREC=(1,10)         
  OUTFIL FNAMES=OU1,INCLUDE=(81,2,CH,EQ,C'11'),OUTREC=(1,8,2X)       
/*                                                                   



output:

HLQ.XX.Y7

Code:
****** ***************************** Top of Data ******************************
000001 AABBCCDDFF                                                             
000002 1122334488                                                             
****** **************************** Bottom of Data ****************************


HLQ.XX.Y8

Code:
****** ***************************** Top of Data ******************************
000001 11223344                                                               
****** **************************** Bottom of Data ****************************


You need to add a step to concatenate both the files.
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India
PostPosted: Tue Nov 13, 2007 10:56 pm
 Reply with quote
morpheus007
Show some sample i/p and o/p records for clear understanding of your requirement.

Quote:
3.IF FIELD1/FIELD2 and FIELD3 values of FILE1 and FILE2 match then

Do you mean to say that if either filed1 or field2 and field3 match then.....?
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
PostPosted: Wed Nov 14, 2007 9:49 am
 Reply with quote
Krisprems,

Quote:
Do you mean to say that if either filed1 or field2 and field3 match then.....?


morpheus wrote:

Quote:
4.For those FILE1 records where the 3 fields do not match the corresponding values in FILE2 we need SPACES
for the last field in FILE3.


hence i guess his requirement is to check for all the 3 fields. Anyways let us wait for the clarification from the OP.

This is one more example where people post their queries but never bother to reply back icon_sad.gif
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 -> JCL & VSAM

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
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 Merge two VSAM KSDS files into third ... JCL & VSAM 6
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