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
 

 

Get matching and Non Matching records

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

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Tue May 10, 2011 9:25 pm    Post subject: Get matching and Non Matching records
Reply with quote

Hi,

I want to compare between two files and put the matching data into one file and the non matching data into another.
The first file is a VB file which has 34 bytes of data which looks like this
Code:

4009289999900008708864068848701|Q
0024389999914838321270308411399|Q
0024389999918704172877375860610|I
0024389999918704642778717102236|Q


The second file is a FB file which looks like this
Code:

483832127,13349500374
483832129,12357270656
483832136,12581630634
483832145,12975630596


The key is from position 13 in the VB file and position 1 to 9 in the second file

I want to take out the common records based on the key in both the files and put the record from the second file's 11th position to another file.. These would be the common records, and if I do not find a match I would want the entire record from the first file to be present in another file.

For example for the key 483832127 we have a match in the first file in the second record so I would want my match output file to have 13349500374 and rest of the records from the first file which do not match should come to the non match file in the same layout as my first file.

Can you please help me to do this

Thanks
Sudip[/code]
Back to top
View user's profile Send private message

Skolusu

Senior Member


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

PostPosted: Tue May 10, 2011 10:09 pm    Post subject: Reply to: Get matching and Non Matching records
Reply with quote

sudib19,

Use the following DFSORT JCL which will give you the desired results.
Code:

//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//INA      DD DSN=Your Input VB File,DISP=SHR
//INB      DD DSN=Your Input FB File,DISP=SHR
//MATCH    DD SYSOUT=*                                           
//ONLYF1   DD SYSOUT=*                                           
//SYSIN    DD *                                                 
  OPTION COPY                                                   
  JOINKEYS F1=INA,FIELDS=(17,9,A)                               
  JOINKEYS F2=INB,FIELDS=(01,9,A)                               
  JOIN UNPAIRED                                                 
  REFORMAT FIELDS=(F1:1,4,?,F2:11,11,F1:5)                       
  OUTFIL FNAMES=MATCH,INCLUDE=(5,1,CH,EQ,C'B'),VTOF,BUILD=(6,11)
  OUTFIL FNAMES=ONLYF1,INCLUDE=(5,1,CH,EQ,C'1'),BUILD=(1,4,17)   
//*
Back to top
View user's profile Send private message
sudib19

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Tue May 10, 2011 11:05 pm    Post subject:
Reply with quote

Hi kolusu,
Thanks for the reply
When I try to run this sort job, I get an error
Code:

E1 - DSS20011E - DD 'SORTIN' WAS NOT ALLOCATED.
E2 - DSS11043E - NO SORT OR MERGE CONTROL STATEMENT WAS FOUND


I am not sure if this has something to do with the installation of DFSORT, We have ICETOOL in our shop, can this be done through that or am I missing something here.

Also can you let me know what does the ? indicate in
Code:

REFORMAT FIELDS=(F1:1,4,?,F2:11,11,F1:5)


Thanks again for the help
Sudip
Back to top
View user's profile Send private message
Skolusu

Senior Member


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

PostPosted: Tue May 10, 2011 11:16 pm    Post subject: Reply to: Get matching and Non Matching records
Reply with quote

sudib19 wrote:
When I try to run this sort job, I get an error


sudib19,

Nope you did NOT run the job. They are your JCL syntax checker messages which never are up to date with the latest features of DFSORT. So ignore them and run your job.

sudib19 wrote:

Also can you let me know what does the ? indicate in
Code:
REFORMAT FIELDS=(F1:1,4,?,F2:11,11,F1:5)


Check this link which explain in detail about joinkeys

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/ICE1CA50/5.0
Back to top
View user's profile Send private message
sudib19

New User


Joined: 06 Feb 2007
Posts: 29
Location: Noida

PostPosted: Tue May 10, 2011 11:41 pm    Post subject:
Reply with quote

Thanks a lot Kolusu, It works great. Thanks for all the help.
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 Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


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