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
 

 

Issue in Join keys
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Mon Jun 25, 2012 8:33 pm    Post subject: Issue in Join keys
Reply with quote

Dear All ,

I am facing an issue with Join keys mentioned below.
I have 2 files with me. RECFM of both the files is FB adn LRECL is 57.

File01 content :
Code:
10018KO0000156435670001000000000469
10012KO0000162667162001000000000647
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912883001000000000469
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10013KO0000280936031001000000000612
10040KO0000280936574001000000000035

File02 content :
Code:
10004KO0000131926500002000000000035
10021KO0000162667162002000000000094
10004KO0000239155685002000000000043
10016KO0000258758008002000000000043
10021KO0000279912883002000000000094
10021KO0000280935517004000000000094
10021KO0000280935517004000000000086
10006KO0000280936760002000000000094
10006KO0000280936760002000000000035
10006KO0000280936825002000000000035


My requirement is to create 3 output files.
1) File01 records which donot have match in File02
2) File02 records which donot have match in File01
3) Matched content based on joinkey (6,15,CH,A)


Sort Card which I used :
Code:

  JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)                           
  JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)                           
  JOIN UNPAIRED,F1,F2                                           
  REFORMAT FIELDS=(F1:1,57,F2:1,57)                             
  OPTION COPY                                                   
  OUTFIL FNAMES=SORTOF01,INCLUDE=(60,1,CH,EQ,C'1'),             
    BUILD=(1,57)                                                 
  OUTFIL FNAMES=SORTOF02,INCLUDE=(60,1,CH,EQ,C'2'),BUILD=(1,57)
  OUTFIL FNAMES=SORTOF03,BUILD=(1,57,/,1,57)   



However I'm getting incorrect output.


Regards,
Shweta.
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 25, 2012 8:49 pm    Post subject:
Reply with quote

Shewta,

A quick question

Are you using SYNCSORT or DFSORT??
Back to top
View user's profile Send private message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Mon Jun 25, 2012 9:03 pm    Post subject: Reply to: Issue in Join keys
Reply with quote

Hello ,

I am using Syncsort .

Regards,
Shweta
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Mon Jun 25, 2012 9:03 pm    Post subject:
Reply with quote

Suggest you look HERE for 1-byte indicator.

Hope that helps.
Back to top
View user's profile Send private message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Mon Jun 25, 2012 9:07 pm    Post subject: Reply to: Issue in Join keys
Reply with quote

Hi Naish ,

I did refer this document however couldn't solve this issue.

Regards,
Shweta.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Jun 25, 2012 9:15 pm    Post subject: Reply to: Issue in Join keys
Reply with quote

For Syncsort (please post in the correct forum next time) you have to rely on the absence of data to identify the mismatches.

So, pick a field which can't have the default FILL value, test for that value.

Code:
FILL DATA
DATA FILL
DATA DATA


If field in first file is FILL, output second file.
If field in second file is FILL, output first file.
If neither of the above, output two records to third file.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 25, 2012 9:16 pm    Post subject:
Reply with quote

First output file

Check first 57 ne Space and next 57 bytes Eq Space

Second output file

check opposite of previous check

Third file

Add SAVE before build in your outfil
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Mon Jun 25, 2012 9:23 pm    Post subject:
Reply with quote

Shweta,

Apologies for the confusion. I was oblivious of SYNCSORT features until Bill's post. I hope you flush out what I suggested.

Thanks Bill.
Back to top
View user's profile Send private message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Mon Jun 25, 2012 9:25 pm    Post subject:
Reply with quote

Hello ,

Could you please elaborate on what is wrong in sort card.
I am a new user on joinkeys hence little more information would be of great help.


Regards,
Shweta.
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: Mon Jun 25, 2012 9:30 pm    Post subject:
Reply with quote

Hello,

It will probably help if you post the complete jobstep (JCL and sort control statements).
Back to top
View user's profile Send private message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Mon Jun 25, 2012 9:46 pm    Post subject:
Reply with quote

Hi Dick ,

Please find the JCL below :

Code:


//STEP01   EXEC PGM=SORT                       
//SORTJNF1 DD  DSN=XB99.PRIM.MATCH.OUT.NEW1,   
//             DISP=SHR                         
//SORTJNF2 DD  DSN=XB99.SEC.MATCH.OUT.NEW1,     
//             DISP=SHR                         
//SORTOF01 DD  DSN=XB99.SORT01.SS036.OUT(+1),   
//             DISP=(NEW,CATLG,DELETE),         
//             DATACLAS=DCALNXL                 
//SORTOF02 DD  DSN=XB99.SORT02.SS036.OUT(+1),   
//             DISP=(NEW,CATLG,DELETE),         
//             DATACLAS=DCALNXL                 
//SORTOF03 DD  DSN=XB99.SORT03.SS036.OUT(+1),   
//             DISP=(NEW,CATLG,DELETE),         
//             DATACLAS=DCALNXL                 
//SYSIN    DD  DSN=XB99.CTNPCD.PGMCCD(SSS36I), 
//             DISP=SHR                         





SSS36I here refers to below content :


Code:


JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)                             
JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)                             
JOIN UNPAIRED,F1,F2                                             
REFORMAT FIELDS=(F1:1,57,F2:1,57)                               
OPTION COPY                                                     
OUTFIL FNAMES=SORTOF01,INCLUDE=(60,1,CH,EQ,C'1'),               
 BUILD=(1,57)                                                   
OUTFIL FNAMES=SORTOF02,INCLUDE=(60,1,CH,EQ,C'2'),BUILD=(1,57)   
OUTFIL FNAMES=SORTOF03,BUILD=(1,57,/,1,57)                     




Regards,
Shweta.
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Mon Jun 25, 2012 9:57 pm    Post subject:
Reply with quote

Hello Shweta.

1. Did you try Bill's suggestion/solution?
2. You mentioned you got incorrect output. Please post what output you got. Also, what is inside SORTJNF1 and SORTJNF2?
3. Suggest (I hope not to mess up this time) you read the manual (since you mentioned you are new to joinkeys). You will get a clear understanding of the constructs/syntax/keywords/options used and I am sure you can then deduce what you want.
Back to top
View user's profile Send private message
Shweta12j

New User


Joined: 10 May 2010
Posts: 32
Location: Mumbai

PostPosted: Mon Jun 25, 2012 10:27 pm    Post subject:
Reply with quote

Dear Niash ,

I have read the manual however still I am getting an issue.
Below is a complete jcl with respective input and output.


Input File content :


File Name : XB99.PRIM.MATCH.OUT.NEW1

Code:



10018KO0000156435670001000000000469
10012KO0000162667162001000000000647
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912883001000000000469
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10013KO0000280936031001000000000612
10040KO0000280936574001000000000035



File Name : XB99.SEC.MATCH.OUT.NEW1

Code:


10004KO0000131926500002000000000035
10021KO0000162667162002000000000094
10004KO0000239155685002000000000043
10016KO0000258758008002000000000043
10021KO0000279912883002000000000094
10021KO0000280935517004000000000094
10021KO0000280935517004000000000086
10006KO0000280936760002000000000094
10006KO0000280936760002000000000035
10006KO0000280936825002000000000035



JCL :

Code:


//STEP01   EXEC PGM=SORT                       
//SORTJNF1 DD  DSN=XB99.PRIM.MATCH.OUT.NEW1,   
//             DISP=SHR                         
//SORTJNF2 DD  DSN=XB99.SEC.MATCH.OUT.NEW1,     
//             DISP=SHR                         
//SORTOF01 DD  DSN=XB99.SORT01.SS036.OUT(+1),   
//             DISP=(NEW,CATLG,DELETE),         
//             DATACLAS=DCALNXL                 
//SORTOF02 DD  DSN=XB99.SORT02.SS036.OUT(+1),   
//             DISP=(NEW,CATLG,DELETE),         
//             DATACLAS=DCALNXL                 
//SORTOF03 DD  DSN=XB99.SORT03.SS036.OUT(+1),   
//             DISP=(NEW,CATLG,DELETE),         
//             DATACLAS=DCALNXL                 
//SYSIN    DD  DSN=XB99.CTNPCD.PGMCCD(SSS36I), 
//             DISP=SHR                         
//SORTWK01 DD  DATACLAS=DCALNXL                 
//SORTWK02 DD  DATACLAS=DCALNXL                 
//SORTWK03 DD  DATACLAS=DCALNXL                 
//SORTWK04 DD  DATACLAS=DCALNXL                 
//SORTWK05 DD  DATACLAS=DCALNXL                 



Sort Card Content :

Code:


JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)                           
JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)                           
JOIN UNPAIRED,F1,F2                                           
REFORMAT FIELDS=(F1:1,57,F2:1,57)                             
OPTION COPY                                                   
OUTFIL FNAMES=SORTOF01,INCLUDE=(60,1,CH,EQ,C'1'),             
 BUILD=(1,57)                                                 
OUTFIL FNAMES=SORTOF02,INCLUDE=(60,1,CH,EQ,C'2'),BUILD=(1,57) 
OUTFIL FNAMES=SORTOF03,BUILD=(1,57,/,1,57)                     



Result :
XB99.SORT01.SS036.OUT(0) - Empty datasets

XB99.SORT02.SS036.OUT(0) - Empty datasets

File Name : XB99.SORT03.SS036.OUT(0)
Code:


                                   
                                   
10018KO0000156435670001000000000469
10018KO0000156435670001000000000469
10012KO0000162667162001000000000647
10012KO0000162667162001000000000647
                                   
                                   
                                   
                                   
10040KO0000279912830001000000000353
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912875001000000000450
10040KO0000279912883001000000000469
10040KO0000279912883001000000000469
10040KO0000279912903001000000000825
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10040KO0000279932440001000000000728




Desired Output :

My requirement is to create 3 output files.
1) File01 records which donot have match in File02
2) File02 records which donot have match in File01
3) Matched content based on joinkey (6,15,CH,A)


Please let me know in case you need information from my end.

Regards,
Shweta.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Mon Jun 25, 2012 11:04 pm    Post subject:
Reply with quote

Shweta,

For the given input 60,1 is always 0

Two of output is empty because the Include condition for SORTOF01 & SORTOF02 doesnt seem to work

Also suggest you to revisit how reformattung works

To understand clearly what happens after Join unpaired please try to remove the statements after sort fieldd and execute by addin sortout

Then think about the data you need in three files :-)

Trust me this will help you well
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Mon Jun 25, 2012 11:12 pm    Post subject: Reply to: Issue in Join keys
Reply with quote

You are using one byte at position 60 in the REFORMAT record to decide on file 1/2, and you are using a particular value for that position.

If you have an unmatched file 1, then the whole 1,57 for file two will be space (or binary zeros, or whatever, depending on your installation).

If you have unmatched file 2, then the whole 1,57 for file one will be, as above.

If you have a match, both 1,57s will have data.

If you have a particular byte which cannot contain the FILL value used for unmatched records on the JOINKEYS then you can test that byte. At worst you can test the whole record as Pandora-box has already suggested.

There are examples in the JCL forum. Search for JOINKEYS and FILL.
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 32
Location: India

PostPosted: Tue Jun 26, 2012 3:37 pm    Post subject: Reply to: Issue in Join keys
Reply with quote

@Shweta12j:
I assume you had copied the DFSORT syntax to get the 3 outputs and you must have used similar card before :-

JOINKEYS FILE=F1,FIELDS=(6,15,CH,A)
JOINKEYS FILE=F2,FIELDS=(6,15,CH,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,57,F2:1,57,?)
OPTION COPY

Here you would have faced SYNTAX error, as "?" in the SORT card is not supported by SYNCSORT. Please understand all the DFSORT syntax wouldnt work for SYNCSORT
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 32
Location: India

PostPosted: Tue Jun 26, 2012 3:57 pm    Post subject:
Reply with quote

As per my experience in SYNCSORT I don't think you can get all the 3 outputs using 1 Sortcard. You will have to use 3 Sortcards (i.e. you will require 3 steps) 1 for each file.

To get F1 unmatched records
JOINKEYS FILES=F1,FIELDS=(6,15,A)
JOINKEYS FILES=F2,FIELDS=(6,15,A)
JOIN UNPAIRED,F1,ONLY

To get F2 unmatched records
JOINKEYS FILES=F1,FIELDS=(6,15,A)
JOINKEYS FILES=F2,FIELDS=(6,15,A)
JOIN UNPAIRED,F2,ONLY

And 3rd 1 without the UNPAIRED to get the match records.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Jun 26, 2012 4:12 pm    Post subject:
Reply with quote

Bhavin,

Appreciate your enthusiasm

But please revisit your understanding

FYI

JOIN UNPAIRED F1,F2 IS LIKE FULL OUTER JOIN of SQL

You dont need three sort cards
Back to top
View user's profile Send private message
Naish

New User


Joined: 07 Dec 2006
Posts: 82
Location: UK

PostPosted: Tue Jun 26, 2012 4:16 pm    Post subject:
Reply with quote

Hello bhavin.mehta,

Did you even read earlier replies/posts by other members?
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 32
Location: India

PostPosted: Tue Jun 26, 2012 4:46 pm    Post subject:
Reply with quote

Pandora, I agree ou have better understanding than me. But I'm suggesting as I had faced the same issue before.

I agree that JOIN UNPAIRED F1,F2 IS LIKE FULL OUTER JOIN of SQL and in DFSORT you have the ability to identify the
1. Unmatched F1 records
2. Unmatched F2 records and
3. F1 F2 matched records
with just 1 sortcard with the help of '?' in it, which would be the last byte in your REFORMAT record. But this kind of functionality is not available SYNCSORT. The DFSORT example is explained here

http://publib.boulder.ibm.com/infocenter/zos/v1r12/index.jsp?topic=%2Fcom.ibm.zos.r12.icea100%2Fice1ca50119.htm
Underthis goto -> JOINKEYS Application Examples -> Example5

And if you see, Shweta has NOT used '?' in her sortcard, while she is trying to replicate the same as DFSORT!!

@Naish: I did read the previos posts, but I didn't get anything which would backfire me. Can you direct me please.
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
No new posts Joinkeys with duplicated keys juares castro SYNCSORT 19 Fri Sep 23, 2016 5:58 am
No new posts Join key - Populate Zeros when Unpair... rexx77 DFSORT/ICETOOL 6 Thu May 12, 2016 12:22 am


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