Need help to compare 2 files and write the matched records

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Need help to compare 2 files and write the matched records

Post by pgoe9 » Fri Mar 25, 2011 6:36 pm

Hi,

I have 2 files. First file (Input1) having only 1 record is of 8 bytes containing the date in YYYYMMDD format. The second input file (Input2) having many records is of 107 bytes. This 2nd input file (Input2) contains the date from the column 100 to 107.

I need to compare both these files on the dates value and if the date matches then I need to copy all the contents from file 2 (input2) from column 1 to 99 in my output file.

For example: INPUT1
20110110

INPUT2
emp no1, name1, address,.........20110101
emp no2, name2, address,.........20110211
emp no3, name3, address,.........20110110

OUTPUT
emp no3, name3, address,.........

I was using ICETOOL utility but not able to code the parameters properly.
Can someone please help? Let me know if I have missed providing additional details.

Thanks,
Preeti

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Mar 25, 2011 10:44 pm

Here's a DFSORT job that will do what you asked for:

Code: Select all

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/8)
//IN2 DD DSN=...  input file2 (FB/107)
//SORTOUT DD DSN=... output file (FB/99)
//SYSIN DD *
  JOINKEYS F1=IN2,FIELDS=(100,8,A)
  JOINKEYS F2=IN1,FIELDS=(1,8,A)
  REFORMAT FIELDS=(F1:1,99)
  OPTION COPY
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Post by pgoe9 » Mon Mar 28, 2011 11:54 am

Thanks Frank. I did try your option, but its giving the following error in sysout:

1 SYNCSORT 3.7BR TPF1 US PATENTS: 4210961,5117495, OTHER PAT. PEND. (C) 1998 61,5117495, OTHER PAT. PEND. (C) 1998 SYNCSORT INC. DATE=2011/087 TIME=02.40.32
z/OS 1.0.0 CPU MODEL 2097
SYSIN :
JOINKEYS F1=IN2,FIELDS=(100,8,A)
*
JOINKEYS F2=IN1,FIELDS=(1,8,A)
*
REFORMAT FIELDS=(F1:1,99)
*
SORT FIELDS=COPY
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000

Is it that DFSORT utility is not supported on my mainframe application?

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Mon Mar 28, 2011 1:55 pm

SYSOUT mesages from your failed Job says - that you are using SYNCSORT, on the LPAR you execute this Job, so what Frank has posted might/might-not work with the SyncSort Shop but would work witha DFSort Shop for sure.

On the other hand, JOINKEYS are available with your release of SyncSort - though they use little differnt Syntax for them. Suggest you look in to the manuals and see what interface SyncSort needs for the JOINKEYs application.

Hint: For an Apllication developer, it's just the different DDnames...
Regards,
Anuj

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Post by pgoe9 » Mon Mar 28, 2011 2:09 pm

Yes you are right. The job is using SYNCSORT. As far as I know, this utility somehow does not work in our jobs.

Can the same job be converted using ICETOOL that will perform the same functionality? If yes, then please help me.
ICETOOL works quite well in my other mainframe jobs.

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Post by pgoe9 » Tue Mar 29, 2011 9:40 am

Hello,

Can someone please help me with this query? Frank, if you can help with the job using ICETOOL and splice statements, it will be great. Unfortunately joinkeys is not working in my mainframe job... :(

hparthas
Member
Posts: 43
Joined: Thu Oct 21, 2010 11:17 pm

Need help to compare 2 files and write the matched records

Post by hparthas » Tue Mar 29, 2011 4:10 pm

Hi Preeti,

Here is the SORT Card which will work in SYNCSORT !!!!

Code: Select all

//S1 EXEC PGM=SORT 
//SYSOUT DD SYSOUT=* 
//IN1 DD DSN=...  input file1 (FB/8) 
//IN2 DD DSN=...  input file2 (FB/107) 
//SORTOUT DD DSN=... output file (FB/99) 
//SYSIN    DD  *                                      
  JOINKEYS FILE=F1,FIELDS=(1,8,A)                     
  JOINKEYS FILE=F2,FIELDS=(100,8,A)                   
  JOIN UNPAIRED                                       
  REFORMAT FIELDS=(F1:1,8,F2:1,107),FILL=X'FF'        
  SORT FIELDS=COPY                                    
  OUTFIL FILES=01,                                    
    INCLUDE=(9,2,BI,NE,X'FFFF',AND,1,2,BI,NE,X'FFFF'),
     OUTREC=(9,99)                                     
/* 

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Post by pgoe9 » Tue Mar 29, 2011 5:16 pm

Its still not working :cry: The same error pops up again...

SYNCSORT 3.7BR TPF1 US PATENTS: 4210961,5117495, OTHER PAT. PEND. (C) 1998 S61,5117495, OTHER PAT. PEND. (C) 1998 SYNCSORT INC. DATE=2011/088 TIME=08.08.42
z/OS 1.0.0 CPU MODEL 2097

SYSIN :
JOINKEYS FILE=F1,FIELDS=(1,8,A)
*
JOINKEYS FILE=F2,FIELDS=(100,8,A)
*
JOIN UNPAIRED
*
REFORMAT FIELDS=(F1:1,8,F2:1,107),FILL=X'FF'
*
SORT FIELDS=COPY
OUTFIL FILES=01,
INCLUDE=(9,2,BI,NE,X'FFFF',AND,1,2,BI,NE,X'FFFF'),
OUTREC=(9,99)
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000


Its urgent. Will I be able to achieve the desired output??

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Tue Mar 29, 2011 6:14 pm

Try this:

Code: Select all

//S1 EXEC PGM=SORT 
//SYSOUT DD SYSOUT=* 
//SORTJNF1 DD DSN=...  input file1 (FB/8) 
//SORTJNF2 DD DSN=...  input file2 (FB/107) 
//SORTOUT DD DSN=... output file (FB/99) 
//SYSIN DD * 
  JOINKEYS FILES=F1,FIELDS=(100,8,A) 
  JOINKEYS FILES=F2,FIELDS=(1,8,A) 
  REFORMAT FIELDS=(F1:1,99) 
  OPTION COPY 
/*
Regards,
Anuj

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Mar 29, 2011 10:23 pm

Frank, if you can help with the job using ICETOOL and splice statements, it will be great.
Since you are using Syncsort, you are actually using Syncsort's SYNCTOOL, not DFSORT's ICETOOL. I'm a DFSORT developer. DFSORT and Syncsort are competitive products. I'm happy to answer questions on DFSORT and DFSORT's ICETOOL, but I don't answer questions on Syncsort.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Post by pgoe9 » Wed Mar 30, 2011 10:26 am

Well, this is what I've been writing from the beginning. I requested your help on the ICETOOL utility because syncsort doesn't work in my mainframe job. I'm not asking any questions on Syncsort.

Please let me know how can I achieve the result using ICETOOL since you are an expert in that field, Frank.

I'm losing time... I have to submit the results very soon.

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Wed Mar 30, 2011 2:02 pm

pgoe9 wrote:Well, this is what I've been writing from the beginning. I requested your help on the ICETOOL utility because syncsort doesn't work in my mainframe job. I'm not asking any questions on Syncsort.

Please let me know how can I achieve the result using ICETOOL since you are an expert in that field, Frank.

I'm losing time... I have to submit the results very soon.
You need to understand what Frank has told you. When your site is a SyncSort site-- even if in your Job, you write PGM=ICETOOL, it's not going to invoke DFSORT's ICETOOL, it will invoke SYNCTOOL, a product from SyncSort.

Well, had you had a chance to have a look at what I posted in an earlier post?
Last edited by Anuj Dhawan on Thu Mar 31, 2011 1:15 pm, edited 1 time in total.
Regards,
Anuj

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Post by pgoe9 » Wed Mar 30, 2011 2:49 pm

Ok Anuj, I got what you and Frank are saying. So even if I code ICETOOL utility its going to invoke Synctool.

I know you suggested me to look in to the manuals and see what interface SyncSort needs for the JOINKEYs application. Unfortunately I don't have access to such manuals.

I tried your code too, but it gives the same error.
My code is as follows:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=Input 1,DISP=SHR
//SORTJNF2 DD DSN=Input 2,DISP=SHR
//SORTOUT DD DSN=Output,
// DISP=(NEW,CATLG,DELETE),
// UNIT=SYSDA,
// SPACE=(CYL,(230,4),RLSE),
// DCB=(RECFM=FB,LRECL=99,BLKSIZE=990)
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(100,8,A)
JOINKEYS FILES=F2,FIELDS=(1,8,A)
REFORMAT FIELDS=(F1:1,99)
OPTION COPY
/*

The error is as follows:

SYSIN :
JOINKEYS FILES=F1,FIELDS=(100,8,A)
*
JOINKEYS FILES=F2,FIELDS=(1,8,A)
*
REFORMAT FIELDS=(F1:1,99)
*
OPTION COPY
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000

Do you have any other suggestion?

pgoe9
Member
Posts: 9
Joined: Fri Mar 25, 2011 6:25 pm

Post by pgoe9 » Wed Mar 30, 2011 5:16 pm

By the way, I also tried using PGM=SYNCTOOL but it failed with RC 0020.
I know this forum is mainly for DFSORT but if anyone is having info whether joinkeys utility can work on Syncsort for z/OS 1.0.0.

What I mean is, am I trying to use Joinkeys on the OS not upgraded to use this facility?
If anyone can help me in some direction...

Anuj Dhawan
Moderator
Posts: 1625
Joined: Sat Aug 09, 2008 9:02 am
Location: Mumbai, India

Post by Anuj Dhawan » Thu Mar 31, 2011 1:20 pm

WER275A NO KEYWORDS FOUND ON CONTROL STATEMENT
Where did you start the JOINKEYS SYSIN statements? Do you leave 2-blank spaces before these statements? Show your Job using BBcode next time, as showed by Frank and me in some of the posts.
Regards,
Anuj

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others