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
 

 

Cross join of 2 files

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

New User


Joined: 30 Sep 2006
Posts: 60

PostPosted: Thu Jul 02, 2009 3:35 am    Post subject: Cross join of 2 files
Reply with quote

Hi,

I have 2 files FB of length 5 bytes each. I want to create an output file that has the cross join of all records from the first file to the second.

Input file 1
11111
22222
33333

Input file 2
AAAAA
BBBBB

Output file
11111AAAAA
11111BBBBB
22222AAAAA
22222BBBBB
33333AAAAA
33333BBBBB

Can this be done using sort?

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

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 02, 2009 4:51 am    Post subject:
Reply with quote

Hello,

No.

What you want to do is a cartesian join.

The last several times this was asked (searching this dfsort part of the forum will find these topics), the answer from IBM was that
Quote:
DFSORT does not have a built-in function to do a cartesian join.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Thu Jul 02, 2009 5:27 am    Post subject:
Reply with quote

Aneesh,

The following JCL will give you the desired results. I assumed that your input 2 has just 2 records and you are interested in only the first 5 bytes. If you have more records in your file2 , the output will be huge.

Code:

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//SORTIN   DD *                                           
AAAAA                                                     
BBBBB                                                     
//SORTOUT  DD DSN=&&S1,DISP=(,PASS),SPACE=(TRK,(1,1),RLSE)
//SYSIN    DD *                                           
  OPTION COPY,STOPAFT=2                                   
  OUTREC BUILD=(C'SYM',SEQNUM,1,ZD,C',C''',1,5,C'''',80:X)
/*                                                         
//STEP0200 EXEC PGM=SORT             
//SYSOUT   DD SYSOUT=*               
//SYMNAMES DD DSN=&&S1,DISP=SHR     
//SORTIN   DD *                     
11111                               
22222                               
33333                               
//SORTOUT  DD SYSOUT=*               
//SYSIN    DD *                     
  OPTION COPY                       
  OUTFIL BUILD=(1,5,SYM1,/,1,5,SYM2)
/*
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8002
Location: Bellevue, IA

PostPosted: Thu Jul 02, 2009 5:38 am    Post subject:
Reply with quote

SAS PROC SQL without a WHERE clause will do a Cartesian join (every record in file 1 will be matched to every record in file 2, yielding output of the number of records in file 1 times the number of records in file 2).

Googling SAS Cartesian join will get over 14,000 hits including some good explanations of the process.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Nov 17, 2009 5:03 am    Post subject:
Reply with quote

Aneesh,

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707, you can use the Joinkeys function like shown below to get the desired results. Since both files don't have a common key we add an extra byte at pos 6 using INREC for each file via JNF1CNTL and JNF2CNTL and use that as a key for matching

Code:


//STEP0100 EXEC PGM=SORT                           
//SYSOUT   DD SYSOUT=*                             
//SORTJNF1 DD DSN=Your input 5 byte file1,DISP=SHR
//SORTJNF2 DD DSN=Your input 5 byte file2,DISP=SHR
//SORTOUT  DD SYSOUT=*                             
//SYSIN    DD *                                   
  SORT FIELDS=COPY                                 
  JOINKEYS FILE=F1,FIELDS=(6,1,A),SORTED,NOSEQCK   
  JOINKEYS FILE=F2,FIELDS=(6,1,A),SORTED,NOSEQCK   
  REFORMAT FIELDS=(F1:1,5,F2:1,5)                 
//JNF1CNTL DD *                                   
  INREC BUILD=(1,5,X)                             
//JNF2CNTL DD *                                   
  INREC BUILD=(1,5,X)                             
//*


For complete details on Joinkey functions and the other new functions available with the Nov, 2009 DFSORT PTF, see:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
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 Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 15 Thu Dec 01, 2016 8:28 pm


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