Join on keys and replace when matched
Moderators: Frank Yaeger, Moderator Group
Join on keys and replace when matched
Hi,
I have File1 with LRECL = 1578 and File2 with LRECL = 279.
In file1 I have 10 char Key from 254-263 whereas in File2 it is from 270-279.
I want to join file1 and File2 on Keys and wherever the key matches replace data at location 9-14 in File1 with data from location 1-6 in File2.
While doing this I want to make sure that we dont change any of the data in File1 for non matching keys.
Please help.
here is an example:
File1
data----------------- Key
(9-14)--------------(254-263)
------ --------------0000000001
------ --------------0000000002
ccccc --------------0000000003
File2
data----------------- Key
(1-6) --------------(270-279)
aaaaa--------------0000000001
bbbbb--------------0000000002
output File1:
data----------------- Key
(9-14)--------------(254-263)
aaaaa--------------0000000001
bbbbb--------------0000000002
ccccc --------------0000000003
I have File1 with LRECL = 1578 and File2 with LRECL = 279.
In file1 I have 10 char Key from 254-263 whereas in File2 it is from 270-279.
I want to join file1 and File2 on Keys and wherever the key matches replace data at location 9-14 in File1 with data from location 1-6 in File2.
While doing this I want to make sure that we dont change any of the data in File1 for non matching keys.
Please help.
here is an example:
File1
data----------------- Key
(9-14)--------------(254-263)
------ --------------0000000001
------ --------------0000000002
ccccc --------------0000000003
File2
data----------------- Key
(1-6) --------------(270-279)
aaaaa--------------0000000001
bbbbb--------------0000000002
output File1:
data----------------- Key
(9-14)--------------(254-263)
aaaaa--------------0000000001
bbbbb--------------0000000002
ccccc --------------0000000003
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Here's a DFSORT JOINKEYS job that will do what you asked for:
Code: Select all
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/1578)
//IN2 DD DSN=... input file2 (FB/279)
//SORTOUT DD DSN=... output file (FB/1578)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(254,10,A),SORTED
JOINKEYS F2=IN2,FIELDS=(270,10,A),SORTED
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,1578,F2:1,6,?)
OPTION COPY
OUTFIL IFOUTLEN=1578,
IFTHEN=(WHEN=(1585,1,CH,EQ,C'B'),
OVERLAY=(9:1579,6))
/*
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
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
SORTD is just a SORT PROC. You can use PGM=SORT. Try it.
Or if you want, use SORTD which will do the same thing.
Or if you want, use SORTD which will do the same thing.
Code: Select all
//S1 EXEC SORTD
//IN1 DD DSN=... input file1 (FB/1578)
//IN2 DD DSN=... input file2 (FB/279)
//SORTOUT DD DSN=... output file (FB/1578)
//SYSIN DD *
JOINKEYS F1=IN1,FIELDS=(254,10,A),SORTED
JOINKEYS F2=IN2,FIELDS=(270,10,A),SORTED
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,1578,F2:1,6,?)
OPTION COPY
OUTFIL IFOUTLEN=1578,
IFTHEN=(WHEN=(1585,1,CH,EQ,C'B'),
OVERLAY=(9:1579,6))
/*
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
Hi Frank,
The JCL given by you was giving SYNTAX ERRORS in JOINKEYS and REFORMAT.
I modified it and it worked.
here is the JCL:
PLease let me know your comments\suggestions.
The JCL given by you was giving SYNTAX ERRORS in JOINKEYS and REFORMAT.
I modified it and it worked.
here is the JCL:
Code: Select all
//STEP60 EXEC SORTD
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD DSN=...(FB\1578)
//SORTJNF2 DD DSN=...(FB\279)
//SORTOUT DD DSN=...(FB\1578)
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(254,10,A),SORTED
JOINKEYS FILE=F2,FIELDS=(270,10,A),SORTED
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,1578,F2:1,6)
OPTION COPY
OUTFIL IFOUTLEN=1578,
IFTHEN=(WHEN=(1584,1,CH,NE,C' '),
OVERLAY=(9:1579,6))
/*
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
My job works fine with DFSORT. If you are getting syntax errors, then I suspect you are using Syncsort (WER messages), not DFSORT (ICE messages). DFSORT supports JOINKEYS syntax that Syncsort does not support.
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.
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
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
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