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
 

 

Want to join two flat files into a new file

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
trevor_rebelo

New User


Joined: 18 Aug 2005
Posts: 17

PostPosted: Thu Jun 29, 2006 12:42 pm    Post subject: Want to join two flat files into a new file
Reply with quote

Hi All,

I have a req. I have 2 file and i want to join them in new file based on keys. (Like the left Join in SQL)

E.g
File A (records)
1234ABCD
1234EFGH
2222KKKK
1111LMNO


File B (records)
1234TREVOR
1111REBELO


Output File
1234ABCDTREVOR
1234EFGHTREVOR
2222KKKK
1111LMNOREBELO


is there a way to do it other then writing a program.

The Splice option is removing the dulpicates.

Thanks in advance
Back to top
View user's profile Send private message

sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 182
Location: hyderabad

PostPosted: Thu Jun 29, 2006 1:45 pm    Post subject:
Reply with quote

Hi,
There were a lot of examples for this in the forum.Please check for them.

Here is the logic you need to use for this.

1)Generate the sequence number for both files.
2)Match the records on the sequence number.

Thank you
Krishy
Back to top
View user's profile Send private message
trevor_rebelo

New User


Joined: 18 Aug 2005
Posts: 17

PostPosted: Thu Jun 29, 2006 3:02 pm    Post subject:
Reply with quote

Hi Krishy

file A has multiple records for each record in file B
and i need all the records in file A to Output File. (Kind a LEFT OUTER JOIN in SQL)

How can the sequence No match?
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 182
Location: hyderabad

PostPosted: Thu Jun 29, 2006 5:21 pm    Post subject:
Reply with quote

Hi,
If you are having the SYNCSORT FOR Z/OS 1.2.0.0RI,the below JCL will give you the desired results.

Code:


//PS020    EXEC PGM=SYNCTOOL                           
//TOOLMSG  DD SYSOUT=*                                 
//DFSMSG   DD SYSOUT=*                                 
//IN1      DD *                                         
1234ABCD                                               
1234EFGH                                               
2222KKKK                                               
1111LMNO                                               
//CTL1JNF1 DD DSN=xxxx.S.T1,                         
//            DISP=(,CATLG,DELETE),                     
//            UNIT=SYSDA,                               
//            SPACE=(CYL,(55,55),RLSE)                 
//CTL1JNF2 DD *                                         
1234TREVOR                                             
1111REBELO                                             
//CTL1OF01 DD SYSOUT=*                                 
//TOOLIN    DD *                                       
  SORT FROM(IN1) TO(CTL1JNF1) USING(CTL0)               
  SORT FROM(CTL1JNF1) USING(CTL1)                       
//CTL0CNTL  DD *                                       
  SORT FIELDS=COPY                                     
  OUTREC FIELDS=(1,20,SEQNUM,8,ZD)                     
//CTL1CNTL  DD *                                       
  JOINKEYS FILES=F1,FIELDS=(1,4,A)                     
  JOINKEYS FILES=F2,FIELDS=(1,4,A)                     
  JOIN UNPAIRED,F1                                     
  REFORMAT FIELDS=(F1:1,8,F2:5,6,F1:21,8)               
  SORT FIELDS=(15,8,CH,A)                               
  OUTFIL FILES=01,OUTREC=(1,14)                         
/*                                                     



Thank you
Krishy
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: Thu Jun 29, 2006 9:12 pm    Post subject:
Reply with quote

Trevor,

Here's a DFSORT/ICETOOL job that will do what you asked for. Notice that KEEPNODUPS with SPLICE is used to keep the non-duplicates. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed for other attributes.

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//FILEA DD DSN=...  input fileA (FB/80)
//FILEB DD DSN=...  input fileB (FB/80)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//T2 DD DSN=&&T2,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(MOD,PASS)
//OUT DD DSN=...  output file (FB/80)
//TOOLIN DD *
COPY FROM(FILEB) TO(T1) USING(CTL1)
COPY FROM(FILEA) TO(T1) USING(CTL2)
SPLICE FROM(T1) TO(T2) ON(1,4,CH) -
  WITHALL WITH(5,4) WITH(81,8) KEEPNODUPS
SORT FROM(T2) TO(OUT) USING(CTL3)
/*
//CTL1CNTL DD *
  INREC BUILD=(1,4,9:5,6,88:X)
/*
//CTL2CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,ZD)
/*
//CTL3CNTL DD *
  SORT FIELDS=(81,8,ZD,A)
  OUTREC BUILD=(1,80)
/*
Back to top
View user's profile Send private message
trevor_rebelo

New User


Joined: 18 Aug 2005
Posts: 17

PostPosted: Fri Jun 30, 2006 5:36 pm    Post subject:
Reply with quote

Hi Frank,

I tried ur JCL
It is giving Syntax error in the following code
INREC BUILD=(1,4,9:5,6,88:X)
INREC OVERLAY=(81:SEQNUM,8,ZD)

so i used the following instead
OUTREC FIELDS=(1,4,9:5,6)
OUTREC FIELDS=(1,8)

The SPLICE option worked fine

But in the SORT option it is giving following error
CTL3CNTL :
SORT FIELDS=(1,4,CH,A)
OUTREC FIELDS=(1,80)
* OUTREC BUILD=(1,80)
PARMLIST :
OPTION RESINV=0,ARESINV=0,MSGDDN=DFSMSG,SORTIN=T2,SORTOUT=OUT,SORTDD=CTL3,DYNAL
OC
WER428I CALLER-PROVIDED IDENTIFIER IS "0004"
WER164B 4,860K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,020K BYTES USED
WER146B 4K BYTES OF EMERGENCY SPACE ALLOCATED
WER189A T2 DCB RECFM REQUIRED
WER108I T2 : RECFM= ; LRECL= ; BLKSIZE=
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000

But if T2 is sorted in a seperate Step with the following parameter it is working fine
SORT FIELDS=(1,4,CH,A)
OUTREC FIELDS=(1,14,66X)


Is the SEQNUM use only to keep the records in the original order (as in FILEA)?

Thanks for ur solution.
Back to top
View user's profile Send private message
trevor_rebelo

New User


Joined: 18 Aug 2005
Posts: 17

PostPosted: Fri Jun 30, 2006 5:38 pm    Post subject:
Reply with quote

Hi Krishy

I am not familiar with SYNCTOOL Can u plz suggest any handout?

Thanks for ur efforts
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 Jun 30, 2006 8:58 pm    Post subject:
Reply with quote

Trevor,

The WER messages indicate you're using Syncsort, not DFSORT. The job I showed works fine with DFSORT.

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.
Back to top
View user's profile Send private message
sril.krishy

Active User


Joined: 30 Jul 2005
Posts: 182
Location: hyderabad

PostPosted: Sun Jul 02, 2006 11:35 pm    Post subject:
Reply with quote

Hi,
Since your shop is having SYNCSORT,Please run the code shown above by mw with JOIN kewords and let me know if you face any problems.

Thank you
Krishy
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 -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts splitting of file using XMITIP abdulrafi TSO/ISPF 3 Mon Mar 27, 2017 3:01 pm
No new posts Changing of LRECL of a file abdulrafi DFSORT/ICETOOL 2 Fri Mar 24, 2017 3:25 pm
No new posts splitting a file abdulrafi DFSORT/ICETOOL 4 Fri Mar 24, 2017 11:51 am
No new posts Receive a file using PCOMM macro Harald.v.K IBM Tools 0 Thu Mar 23, 2017 6:50 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm


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