IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Difficult matching problem...


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
gabriel.ryoga

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Thu Jun 07, 2007 5:52 pm
Reply with quote

File 1
1 a
1 b
2 d
2 e

File 2
1 x
1 y
2 z
3 w

Output:
1 a x
1 a y
1 b x
1 b y
2 d z
2 e z

The problem is that I need to match every register of file 1 with all the registers of file 2, like a INNER JOIN in a SQL QUERY.

Can somebody help me?? ^_^
Back to top
View user's profile Send private message
krisprems

Active Member


Joined: 27 Nov 2006
Posts: 649
Location: India

PostPosted: Thu Jun 07, 2007 7:28 pm
Reply with quote

gabriel.ryoga

Your requirement is a cartesian join, check for the frank's comments about this through this link http://ibmmainframes.com/viewtopic.php?t=21424&highlight=cartesian
Back to top
View user's profile Send private message
gabriel.ryoga

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Thu Jun 07, 2007 8:37 pm
Reply with quote

Thanks for the answers...., it seems that I will have to make some program if I want to do the cartesian join.

Thanks for the help!

^_^
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri Jun 08, 2007 4:42 am
Reply with quote

Hello,

I do not believe you need a full cartesian product. If i understand, you want to match on the first value in each file and then write out all of the existing combinations for that "match".

How close to what you've posted is the actual data? How many records will be in the first 2 files? Is the "3 w" record supposed to be omitted in the output file?
Back to top
View user's profile Send private message
gabriel.ryoga

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Fri Jun 08, 2007 11:16 am
Reply with quote

I don't need a full cartesian product, just a cartesian product between the records with the same key. If some records don't match they have to be omitted

The files will have lots of records maybe 7 million or more, so I need some utility with enough power to do that without spending all the day.
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Fri Jun 08, 2007 11:52 am
Reply with quote

Hi,

No need for the program to do this.
You can do it thru Synsort or DFsort using the joinkey.

Joinkeys files=F1,Fields=(1,1,A)
Joinkeys files=F2,Fields=(1,1,A)
Join unpaired,F1
Reformat Fields=(F1:1,2,F2:,2,1)
Sort fields=(1,1,CH,A)

If you want all the records in the file1, even match is not found
use the unpaired option. Else if you only matched records remove Join unpaired option.

Thanks
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Sat Jun 09, 2007 9:16 pm
Reply with quote

Quote:
You can do it thru Synsort or DFsort using the joinkey.


DFSORT does NOT support that function.
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Wed Jun 13, 2007 3:34 pm
Reply with quote

Hi,

Can you clear me whether DFSORT and ICEMAN are the same one ?.

IF yes , then DFSORT is supporting the Joinkey fuction. Because I have
tried with ICEMAN, it is working fine.

Thanks,
Back to top
View user's profile Send private message
gabriel.ryoga

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Wed Jun 13, 2007 4:04 pm
Reply with quote

I've tried with this:

//STEP0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
123 AAAAAA
234 BBBBBBBB
345 CC
//SORTJNF2 DD *
123 JJJJJJJJJJJJ RRRRRRRR
123 BBBBBBBB GGGGG
123 CC KKKKKKKKKK
234 JJJJJJJJJJJ RRRRRRRR
234 BBBBBBBB GGGGG
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(01,3,A)
JOINKEYS FILES=F2,FIELDS=(01,3,A)
REFORMAT FIELDS=(F1:1,40,F2:10,60)
//*

and this is the result:

********************************* TOP OF DATA **********************************
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 15:17 ON FRI JUN
JOINKEYS FILES=F1,FIELDS=(01,3,A)
$
ICE005A 0 STATEMENT DEFINER ERROR
JOINKEYS FILES=F2,FIELDS=(01,3,A)
$
ICE005A 0 STATEMENT DEFINER ERROR
REFORMAT FIELDS=(F1:1,40,F2:10,60)
$
ICE005A 0 STATEMENT DEFINER ERROR
ICE010A 0 NO SORT OR MERGE CONTROL STATEMENT
ICE751I 0 C5-K90007 C6-K90007 E7-K11698
ICE026I 1 SMF RECORD NOT WRITTEN TO THE SMF DATA SET(RC=20)
ICE052I 3 END OF DFSORT
******************************** BOTTOM OF DATA ********************************
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Wed Jun 13, 2007 4:20 pm
Reply with quote

gabriel.ryoga,

As Frank told DFSORT doesn't support join function.

Quote:
ICE005A 0 STATEMENT DEFINER ERROR


Explanation for ICE005A:

Explanation: Critical. A control statement did not contain one of the
acceptable operation definers (ALTSEQ, DEBUG, END, INCLUDE, INREC, MERGE,
MODS, OMIT, OPTION, OUTFIL, OUTREC, RECORD, SORT, SUM). You can also
receive this message for continuation lines after a line that has an
error.


Quote:
ICE010A 0 NO SORT OR MERGE CONTROL STATEMENT


In the sort control card, you have missed out basic sort statement (sort fields=copy/option copy).
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Wed Jun 13, 2007 4:23 pm
Reply with quote

Hi gabriel,

You have missed the SORT STATEMENT, Please include the SORT STATEMENT in the SYSIN.And try again

Thanks
Back to top
View user's profile Send private message
gabriel.ryoga

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Wed Jun 13, 2007 4:47 pm
Reply with quote

I tried with the sort stament too, and I got the same answer.....
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Wed Jun 13, 2007 4:50 pm
Reply with quote

Quote:
I tried with the sort stament too, and I got the same answer.....


It could be because of join statement.
Back to top
View user's profile Send private message
gabriel.ryoga

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Wed Jun 13, 2007 5:29 pm
Reply with quote

It seems that's no way to do this in a JCL step......
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Wed Jun 13, 2007 5:39 pm
Reply with quote

Hi gabriel,

Can you paste the JCL which have used and ERROR message you got.
Because if you have properly added the SORT statement, you wont get the same error message.

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

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Wed Jun 13, 2007 5:40 pm
Reply with quote

Hi,


Can any one clear me whether DFSORT and ICEMAN are the same one ?.


Thanks,
Back to top
View user's profile Send private message
murmohk1

Senior Member


Joined: 29 Jun 2006
Posts: 1436
Location: Bangalore,India

PostPosted: Wed Jun 13, 2007 5:43 pm
Reply with quote

Bala,

This might help you -

ibmmainframes.com/about1184.html
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Wed Jun 13, 2007 9:31 pm
Reply with quote

Quote:
You have missed the SORT STATEMENT, Please include the SORT STATEMENT in the SYSIN.And try again


DFSORT does NOT support JOINKEYS. You can't make it support JOINKEYS by adding a SORT statement or any other statement.

Quote:
Can you clear me whether DFSORT and ICEMAN are the same one ?.

IF yes , then DFSORT is supporting the Joinkey fuction. Because I have
tried with ICEMAN, it is working fine.


PGM=ICEMAN will invoke the sort product at your site which can be DFSORT, Syncsort or CA-Sort. If JOINKEYS is working, then the sort product at your site is Syncsort, not DFSORT.
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Thu Jun 14, 2007 2:51 pm
Reply with quote

Thanks for the Clarification.
Back to top
View user's profile Send private message
i413678
Currently Banned

Active User


Joined: 19 Feb 2005
Posts: 112
Location: chennai

PostPosted: Mon Dec 03, 2007 12:25 pm
Reply with quote

Hi,

I had given SORT STATEMENT also....But, I am getting the same problem...

Regards,
pavan
Back to top
View user's profile Send private message
gabriel.ryoga

New User


Joined: 07 Jun 2007
Posts: 31
Location: Spain

PostPosted: Mon Dec 03, 2007 12:36 pm
Reply with quote

I finally did a cobol program to do the cartesian product.

Thank's to everybody for the help.

Bye.
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Developer


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

PostPosted: Thu Nov 26, 2009 12:32 am
Reply with quote

With z/OS DFSORT V1R5 PTF UK51706 or z/OS DFSORT V1R10 PTF UK51707 (Nov, 2009), DFSORT now supports JOINKEYS. For complete details on JOINKEYS and the other new functions available with the Nov, 2009 DFSORT PTF, see:

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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts Map Vols and Problem Dataset All Other Mainframe Topics 2
No new posts Rexx pattern matching on PS qualifer ... CLIST & REXX 1
No new posts z/vm installation problem All Other Mainframe Topics 0
No new posts File matching functionality in Easytr... DFSORT/ICETOOL 14
No new posts Job scheduling problem. JCL & VSAM 9
Search our Forums:

Back to Top