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
 

 

Query in comparing input files

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 141
Location: Coimbatore

PostPosted: Mon Jul 25, 2011 9:31 pm    Post subject: Query in comparing input files
Reply with quote

I do have a requirement like the one below,

input file1:
Code:
============
123456789  23456  1976  225  50
123456789  23456  1977  125  50
123456789  23456  1978  425  50
123456789  23456  1979  625  50
123456789  23456  1980  25   50
765878699  12344  1981  245  45
230494729  09097  1982  56   78


input file2:
Code:
============
439879870
123456789
098097868


The output needs to be based upon comparing the input
files like the one below:

output file:
Code:
============
123456789  23456  1976  225  50
123456789  23456  1977  125  50
123456789  23456  1978  425  50
123456789  23456  1979  625  50
123456789  23456  1980  25   50


To accomplish this i used joinkeys in jcl, but it dint workout.
Please help me if you have any suggestions.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Jul 25, 2011 9:40 pm    Post subject:
Reply with quote

Hello,

Quote:
To accomplish this i used joinkeys in jcl, but it dint workout.
Please help me if you have any suggestions.
Do not post "it didn't work". This just wastes everyone's time. . . icon_sad.gif

You need to post the jcl and sort control statements used as will as the "rules" for getting fro the sample input to the sample output. Mention the recfm and lrecl of all files. Post all of the informational messages generated by the run along with their messag ids.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 141
Location: Coimbatore

PostPosted: Tue Jul 26, 2011 6:05 pm    Post subject:
Reply with quote

This is the jcl I have used.

Code:
//T3849ARS JOB ('SORT'),MSGCLASS=Y,NOTIFY=T3849AR
//STEP01   EXEC PGM=SYNCSORT                                             
//SORTJNF1 DD DISP=SHR,DSN=T3849AR.INPUT2         
//SORTJNF2 DD DISP=SHR,DSN=T3849AR.OUTPUT.REPORT 
//***  THIS WILL GIVE UNMATCHED RECORDS  ***     
//SORTOUT  DD DSN=T3849AR.OUTPUT,DISP=SHR                                 
//SYSOUT   DD SYSOUT=*                           
//SYSIN    DD *                                   
  JOINKEYS FILE=F1,FIELDS=(1,9,A)                 
  JOINKEYS FILE=F2,FIELDS=(21,9,A)               
  JOIN UNPAIRED,F1,ONLY                           
  SORT FIELDS=COPY                               
/*                           


the lrecl for T3849AR.INPUT2 is 255. record format is VB
the lrecl for T3849AR.OUTPUT.REPORT is 118. record format is VB
the lrecl for T3849AR.OUTPUT is 118. record format is VB


After execution I am getting all the data available in input1, ie) from T3849AR.INPUT2.

I am unable to get what i expected. :-( plz help me
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2238
Location: @my desk

PostPosted: Tue Jul 26, 2011 6:24 pm    Post subject:
Reply with quote

Code:
//***  THIS WILL GIVE UNMATCHED RECORDS  ***
Does n't this explain why it's "not working"?

From the sample input and expected output provided, it seems like you need only matched records from file-1 into output.
For this you need to omit the JOIN statement and include the the REFORMAT FIELDS statement to select fields from file-1.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2238
Location: @my desk

PostPosted: Tue Jul 26, 2011 7:10 pm    Post subject:
Reply with quote

Also since the input is VB, the starting positions of the key fields in JOINKEYS need to be incremented by 4 to take the RDW also into consideration.
Back to top
View user's profile Send private message
abdulrafi

Active User


Joined: 14 Sep 2009
Posts: 141
Location: Coimbatore

PostPosted: Wed Jul 27, 2011 4:15 pm    Post subject:
Reply with quote

Hi,

I got the output. This is the jcl. Thought just to share it. Thanks.
Code:
//T3849ARS JOB ('SORT'),MSGCLASS=Y,NOTIFY=T3849AR     
//STEP01   EXEC PGM=SYNCSORT                         
//TOOLMSG  DD SYSOUT=*                               
//DFSMSG   DD SYSOUT=*                               
//SORTJNF1 DD DISP=SHR,DSN=T3849AR.INPUT2             
//SORTJNF2 DD DISP=SHR,DSN=T3849AR.INPUT.REPORT       
//SORTOUT  DD DSN=T3849AR.OUTPUT.REPORT,DISP=SHR     
//SYSPRINT DD SYSOUT=*                               
//SYSOUT   DD SYSOUT=*                               
//SYSIN    DD *                                       
  SORT FIELDS=COPY                                   
  JOINKEYS FILE=F1,FIELDS=(5,9,A)                     
  JOINKEYS FILE=F2,FIELDS=(25,9,A)                   
  REFORMAT FIELDS=(F2:1,4,5)                         
/*                                                   
//                                         
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 27, 2011 8:01 pm    Post subject: Reply to: Query in comparing input files
Reply with quote

Good to hear it is working - thank you for letting us know icon_smile.gif

d
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
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 JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Formating the Input file sreekusr DFSORT/ICETOOL 3 Fri Feb 17, 2017 10:40 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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