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
 

 

Sort with Joinkeys

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

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Sat Sep 04, 2010 4:25 pm    Post subject: Sort with Joinkeys
Reply with quote

Hi All,

I've two files A , B and consider both the files are having duplicates. File A is compared with first seven fields in File B and the common has to written to below OUTPUT result. When i tried with Joinkeys its going for Cross Merging and giving 7*35 records in output file.
Please find the below example

File A File B Output
1000800 1000800001 1000800 1000800001
1000800 1000800002 1000800 1000800002
1000800 1000800003 1000800 1000800003
1000800 1000800004 1000800 1000800004
1000800 1000800005 1000800 1000800005
1000800 1000800006 1000800 1000800006
1000800 1000800007 1000800 1000800007
1000801 1000800008 1000801 1000801035
1000800009
1000800010
1000800011
1000800012
1000800013
1000800014
1000800015
1000800016
1000800017
1000800018
1000800019
1000800020
1000800021
1000800022
1000800023
1000800024
1000800025
1000800026
1000800027
1000800028
1000800029
1000800030
1000800031
1000800032
1000800033
1000800034
1000800035
1000801035
Please advice regarding the same.

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

Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Sat Sep 04, 2010 6:06 pm    Post subject:
Reply with quote

Hi Trinadh

To be honest, I did not understand the sample you have provided...
could you please structure it a little more better

Probably like
File A
Code:
---- ---- -----
---- ---- -----
---- ---- -----

File B
Code:
---- ---- -----
---- ---- -----
---- ---- -----

Expected Output
Code:
---- ---- -----
---- ---- -----
---- ---- -----
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Sat Sep 04, 2010 7:25 pm    Post subject:
Reply with quote

The previous one was copied from the excel. guess only the lines are appearing! This time i copied from the textpad. PLease let me know if you are facing issue in viewing the data.

File A
1000800
1000800
1000800
1000800
1000800
1000800
1000800
1000801

File B
1000800001
1000800002
1000800003
1000800004
1000800005
1000800006
1000800007
1000800008
1000800009
1000800010
1000800011
1000800012
1000800013
1000800014
1000801035


Output :-
1000800 1000800001
1000800 1000800002
1000800 1000800003
1000800 1000800004
1000800 1000800005
1000800 1000800006
1000800 1000800007
1000801 1000801035

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

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Sat Sep 04, 2010 10:09 pm    Post subject:
Reply with quote

bodatrinadh,
Quote:
When i tried with Joinkeys its going for Cross Merging and giving 7*35 records in output file.
It did exactly what you asked for. It produced all the possible combinations for matching records. Cartesian Product.

Please explain the rules for getting output. In your expected output, how do you limit your expected output to only 7 records for matching key from file2? What about matching key from 1000800008 through 1000800014? Where did they go?

Thanks,
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: Sun Sep 05, 2010 4:47 am    Post subject:
Reply with quote

Hello,

Quote:
PLease let me know if you are facing issue in viewing the data.
Look at the sample Binop has provided. This is accomplished using the "Code" tag.

When you post data, jcl, code, etc use copy/paste and the "Code" tag to preserve alignment. We have Preview so you can see your post as it will appear to the forum (rather than how things look in the Reply Editor). When the appearance is as you want, Submit.

You need to explain the rules of which records ahould be "kept" and which should be discarded. When desired output is shown, the rules that cause that output need to be posted.
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Wed Sep 08, 2010 3:33 pm    Post subject: Reply to: Sort with Joinkeys
Reply with quote

Hi,

Here is the continuation to my previous post..

I've two files A , B and consider both the files are having duplicates. File A is compared with first seven fields in File B and the common has to written in to OUTPUT. I don't want the output in cartesian product. It should be one to one matching..

FILE-A :-
1000618
1000628
1000635
1000636
1000650
1000651
1000652
1000653
1000654
1000800
1000800
1000800
1000800
1000800
1000800
1000800

File B:-
1000635004
1000635005
1000635006
1000636001
1000636002
1000636003
1000636004
1000636005
1000637001
1000638001
1000650001
1000650002
1000650003
1000651001
1000651002
1000652001
1000652002
1000653001
1000654001
1000800001
1000800002
1000800003
1000800004
1000800005
1000800006
1000800007
1000800008
1000800009
1000800010
1000800011
1000800012
1000800013
1000800014
1000801035

Output should be:-

1000635 1000635004
1000636 1000636001
1000650 1000650001
1000651 1000651001
1000652 1000652001
1000653 1000653001
1000654 1000654001
1000800 1000800001
1000800 1000800002
1000800 1000800003
1000800 1000800004
1000800 1000800005
1000800 1000800006
1000800 1000800007
Back to top
View user's profile Send private message
Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Wed Sep 08, 2010 4:48 pm    Post subject:
Reply with quote

May I ask - Why do you want to do this using DFSORT rather than a COBOL program.

Anyway, to my understanding - I dont think this is possible just by using DFSORT especially since you have the "File A is compared with first seven fields in File B" rule....
Back to top
View user's profile Send private message
smijoss

Active User


Joined: 30 Aug 2007
Posts: 114
Location: pune

PostPosted: Wed Sep 08, 2010 4:52 pm    Post subject:
Reply with quote

the requirement seems so simple, have you even tried reading JOINKEYS ?
or you simply need the code ?

Quote:
I don't want the output in cartesian product. It should be one to one matching..

you can achieve this through JNF1CNTL
Back to top
View user's profile Send private message
sqlcode1

Active Member


Joined: 08 Apr 2010
Posts: 578
Location: USA

PostPosted: Wed Sep 08, 2010 5:58 pm    Post subject:
Reply with quote

bodatrinadh,
Do you have JOINKEYS ptf installed at your shop? Please provide RECFM and LRECL for both input(s) and output files.

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: Wed Sep 08, 2010 11:45 pm    Post subject:
Reply with quote

Bodatrinadh,

Here's a DFSORT JOINKEYS job that will do what you asked for. I assumed your input files have RECFM=FB and LRECL=80, but the job can be changed appropriately for other attributes.

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=... input file1 (FB/80)
//IN2 DD DSN=... input file2 (FB/80)
//SORTOUT DD DSN=... output file (FB/18)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,7,A,81,8,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(1,7,A,81,8,A),SORTED
  REFORMAT FIELDS=(F1:1,8,F2:1,10)
  OPTION COPY
/*
//JNF1CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,BI,RESTART=(1,7))
/*
//JNF2CNTL DD *
  INREC OVERLAY=(81:SEQNUM,8,BI,RESTART=(1,7))
/*
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Fri Sep 10, 2010 10:24 pm    Post subject: Sort with Joinkeys
Reply with quote

Thanks Frank,

Can i use above code for files having different LRECL.
The first file (F1) is of 137 where the key starts at position 1 and length 7 and for File F2 the key starts at 3rd position of length 7 and output is of 150 record length (1,137 from file F1 + at 141 position 10 char field from file F2)
for the matched records i'm adding the 10 digit/char key at last.

Thanks
Trinadh
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 Sep 10, 2010 10:43 pm    Post subject:
Reply with quote

Quote:
Can i use above code for files having different LRECL.


Yes.

Quote:
The first file (F1) is of 137 where the key starts at position 1 and length 7 and for File F2 the key starts at 3rd position of length 7 and output is of 150 record length (1,137 from file F1 + at 141 position 10 char field from file F2)
for the matched records i'm adding the 10 digit/char key at last.


I don't understand this description. What is the LRECL of each file?
What is the starting position and length of the key in each file?
What do you want for the output record exactly?

If you want to attempt to figure it out yourself, complete details on DFSORT's JONKEYS function (with examples) can be found at:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Sun Sep 12, 2010 11:53 am    Post subject: Sort with Joinkeys
Reply with quote

Apologize for the confusion caused, there is a typo error in my previous post..

Please find the file description:-
File-1 (F1) - the record length is 137 and the key starts at position 1 of length 7

File-1 (F2) - the record length is 496 and the key starts at position 3 of length 10 but we required first 7 char for matching.

The output file is of 150 record length = (137 from file F1 + at 141 position 10 char field from file F2)

Thanks
Trinadh
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: Tue Sep 14, 2010 2:31 am    Post subject:
Reply with quote

If I understand what your data looks like and what you want to do (and I'm not sure I do), then this DFSORT job will do it:

Code:

//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/137)
//IN2 DD DSN=...  input file2 (FB/496)
//OUT DD DSN=...  output file (FB/150)
//SYSIN DD *
  JOINKEYS F1=IN1,FIELDS=(1,7,A,497,8,A),SORTED
  JOINKEYS F2=IN2,FIELDS=(3,7,A,497,8,A),SORTED
  REFORMAT FIELDS=(F1:1,137,F2:3,10)
  OPTION COPY
  OUTFIL FNAMES=OUT,BUILD=(1,137,141:138,10)
/*
//JNF1CNTL DD *
  INREC OVERLAY=(497:SEQNUM,8,BI,RESTART=(1,7))
/*
//JNF2CNTL DD *
  INREC OVERLAY=(497:SEQNUM,8,BI,RESTART=(3,7))
/*
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm


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