Join on keys and replace when matched

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

Moderators: Frank Yaeger, Moderator Group

Post Reply
go4romel
Member
Posts: 7
Joined: Thu Sep 22, 2011 11:25 pm

Join on keys and replace when matched

Post by go4romel » Fri Sep 23, 2011 12:00 am

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

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 Sep 23, 2011 12:54 am

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

go4romel
Member
Posts: 7
Joined: Thu Sep 22, 2011 11:25 pm

Post by go4romel » Fri Sep 23, 2011 11:28 pm

I think we dont have PGM=SORT in our setup.
We do use SORTD and ICETOOL. Can we achieve the same results with SORTD and ICETOOL??
Please let me know and thanks for your help!

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

Post by Frank Yaeger » Sat Sep 24, 2011 2:53 am

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.

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

go4romel
Member
Posts: 7
Joined: Thu Sep 22, 2011 11:25 pm

Post by go4romel » Mon Sep 26, 2011 6:51 pm

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:

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))                                             
/*  
PLease let me know your comments\suggestions.

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

Post by Frank Yaeger » Mon Sep 26, 2011 10:04 pm

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.
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

academyindia4

Topic deleted by Admin

Post by academyindia4 » Mon Jan 25, 2016 9:55 pm

<< Content deleted By Admin >>

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