Need help to compare 2 files and write the matched records
Moderators: Frank Yaeger, Moderator Group
Need help to compare 2 files and write the matched records
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
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
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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?
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?
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
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...
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
Anuj
Need help to compare 2 files and write the matched records
Hi Preeti,
Here is the SORT Card which will work in SYNCSORT !!!!
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)
/*
Its still not working 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??
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??
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
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
Anuj
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
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, if you can help with the job using ICETOOL and splice statements, it will be great.
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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.
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.
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
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.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.
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
Anuj
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?
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?
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...
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...
-
- Moderator
- Posts: 1625
- Joined: Sat Aug 09, 2008 9:02 am
- Location: Mumbai, India
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page