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: 147
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: 147
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: 2284
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: 2284
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: 147
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 How to generate a new unique Input fi... for1ranjith CLIST & REXX 11 Sat Jul 01, 2017 12:09 pm
No new posts Need input in Scheduling Benchwarmer CA Products 5 Tue Jun 06, 2017 1:44 am
No new posts getting three output files out of JOI... migusd SYNCSORT 7 Mon Jun 05, 2017 11:30 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm


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