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
 

 

Difficult matching problem...
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
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    Post subject: Difficult matching problem...
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    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Fri Jun 08, 2007 4:42 am    Post subject:
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    Post subject:
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    Post subject:
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 Moderator


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

PostPosted: Sat Jun 09, 2007 9:16 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1439
Location: Bangalore,India

PostPosted: Wed Jun 13, 2007 4:20 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1439
Location: Bangalore,India

PostPosted: Wed Jun 13, 2007 4:50 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 1439
Location: Bangalore,India

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

Bala,

This might help you -

http://ibmmainframes.com/about1184.html
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: Wed Jun 13, 2007 9:31 pm    Post subject:
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    Post subject:
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    Post subject: Reply to: Difficult matching problem...
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Matching with Key at different postions. rajatbagga DFSORT/ICETOOL 12 Wed Nov 09, 2016 10:58 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts DB2 Streaming Batch Processing Problem Manshadi DB2 4 Sat Sep 24, 2016 12:14 pm
No new posts Problem with GETMAIN command amitc23 CICS 6 Thu Sep 01, 2016 1:01 pm


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