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
 

 

Combining TWO files on a KEY - Help required

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

New User


Joined: 08 Mar 2009
Posts: 10
Location: Hyderabad

PostPosted: Fri Dec 10, 2010 3:35 am    Post subject: Combining TWO files on a KEY - Help required
Reply with quote

I have two files having different LRECL but both are fixed length,having keys at different locations.

File1 - LRECL=30,RECFM=FB -
key is 1,5 - char and 11,5 comp-3 field
This is having duplicate records
Code:

----+----1----+----2----+----3
************************
12345................................
FFFFF.....00001....................
12345.....0000C......
 ------------------------
12345.................
FFFFF.....00002....
12345.....0000C......
 ------------------------
12345.................
FFFFF.....00003....
12345.....0000C......
 ------------------------
23456.................
FFFFF.....00018....
23456.....0002C......
 ------------------------
34567.................
FFFFF.....00020....
34567.....0002C......
 ------------------------
45678.................
FFFFF.....00018....
45678.....0002C......
------------------------
12345.................
FFFFF.....00003....
12345.....0000C......
 


File 2 - LRECL=25,RECFM=FB -
key is 1,5 - char and 6,5 comp-3 field
No duplicates are there in this file
Code:

----+----1----+----2----+-
**********************
12345..........ABCDE
FFFFF00001.....CCCCC
123450000C.....12345
 ------------------------
12345..........BCDEF
FFFFF00003.....CCCCC
123450000C.....23446
 ------------------------
 23456.........BBBBB
FFFFF00002.....CCCCC
234560000C.....22222
 ------------------------
45678..........CDEFG
FFFFF00003.....CCCCC
456780000C.....34567
------------------------


I want records all records from file1 to output file(file3).
If any record of file1 is present in file2 then last 5 chars of file2 have to be there in the output file from 25th position.
If not matched with file2 then it has to be spaces.

I want output file (file3) to look like this
Code:

----+----1----+----2----+----3----+----4
**********************************
12345....................ABCDE
FFFFF.....00001..........CCCCC
12345.....0000C..........12345
 -----------------------------
12345.................
FFFFF.....00002....
12345.....0000C......
 ------------------------
12345....................BCDEF
FFFFF.....00003..........CCCCC
12345.....0000C..........23456
 -----------------------------
23456....................
FFFFF.....00018..........44444
23456.....0002C..........00000
 ------------------------
34567....................
FFFFF.....00020..........44444
34567.....0002C..........00000
 ------------------------
45678....................CDEFG
FFFFF.....00018..........CCCCC
45678.....0002C..........34567
 ------------------------
12345....................BCDEF
FFFFF.....00003..........CCCCC
12345.....0000C..........23456


I have tried the DFSORT joinkeys but not giving the desired results

Code:

//SORTJNF1 DD DSN=file1,   
//            DISP=SHR                                       
//SORTJNF2 DD DSN=file2,
//            DISP=SHR                                       
//SORTOUT  DD DSN=File3,DISP=(NEW,CATLG,CATLG),                       
//            SPACE=(CYL,(2,2),RLSE),                     
//            DCB=(RECFM=FB,LRECL=30,BLKSIZE=0)             
//SYSOUT   DD SYSOUT=*                                       
//SYSIN    DD *                                             
  OPTION COPY                                               
  JOINKEYS FILE=F1,FIELDS=(1,5,A,11,5,A)                     
  JOINKEYS FILE=F2,FIELDS=(1,5,A,6,5,A)                     
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,25,F2:15,5)                       
//*


I have a doubt this is not working because of the comp-3 values in the second key.
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Dec 10, 2010 4:30 am    Post subject:
Reply with quote

Quote:
I have tried the DFSORT joinkeys but not giving the desired results


Well, the output you show is NOT the correct expected output - the 45678 record does not match, but you show it as matching. Also, it appears you want the output records in their original order, but you didin't set up the JOINKEYS job to do that.

Here's a DFSORT job that will give you the correct expected output:

Code:

//S1 EXEC PGM=SORT
//SYSOUT   DD SYSOUT=*
//IN1 DD DSN=file1,
//       DISP=SHR
//IN2 DD DSN=file2,
//       DISP=SHR
//SORTOUT  DD DSN=File3,DISP=(NEW,CATLG,CATLG),
//            SPACE=(CYL,(2,2),RLSE)
//SYSIN    DD *
  JOINKEYS F1=IN1,FIELDS=(1,5,A,11,5,A)
  JOINKEYS F2=IN2,FIELDS=(1,5,A,6,5,A)
  JOIN UNPAIRED,F1
  REFORMAT FIELDS=(F1:1,25,F2:15,5,F1:26,8)
  SORT FIELDS=(31,8,ZD,A)
  OUTREC BUILD=(1,30)
//JNF1CNTL DD *
  INREC OVERLAY=(26:SEQNUM,8,ZD)
/*


The output would be (PD shown as ZD for simplicity):

Code:

12345 000000001 ABCDE     
12345 000000002           
12345 000000003 BCDEF     
23456 000000128           
34567 000000220           
45678 000000128           
12345 000000003 BCDEF     


If that's NOT what you want, then you need to do a better job of explaining what you do want vs what you got.

Note that using the PD values for the JOINKEYS binary key is fine since they all have the same positive sign (C).
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 Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm


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