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

Issue in Join keys


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Tue Jun 26, 2012 4:49 pm
Reply with quote

So, what you missed, is the possibility to do in Syncsort testing for the FILL character.
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:55 pm
Reply with quote

To quote a couple:

Pandora-Box wrote:
First output file

Check first 57 ne Space and next 57 bytes Eq Space

Second input file

check opposite of previous check

Third file

Add SAVE before build in your outfil


Bill Woodger wrote:
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.


bhavin.mehta,

Nobody is 'backfiring' you. But, the previous posts implied what you mentioned in your post. So as Bill mentioned you didn't test (possibly) for FILL character in Syncsort.
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India

PostPosted: Tue Jun 26, 2012 5:05 pm
Reply with quote

Apologies.
Back to top
View user's profile Send private message
bodatrinadh

Active User


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

PostPosted: Tue Jun 26, 2012 5:09 pm
Reply with quote

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.


@bhavin : It can be achived in single sort step.

Here is the JCL:-
Code:

//STEP1    EXEC PGM=SORT               
//SYSPRINT DD SYSOUT=*                 
//SYSOUT DD SYSOUT=*                   
//UNMATF3 DD SYSOUT=*                 
//UNMATF2 DD SYSOUT=*                 
//MATCHF1 DD SYSOUT=*                 
//SORTJNF1 DD *                       
10018KO0000156435670001000000000469   
10012KO0000162667162001000000000647   
10040KO0000279912830001000000000353   
10040KO0000279912875001000000000450   
10040KO0000279912883001000000000469   
10040KO0000279912903001000000000825   
10040KO0000279912911001000000000892   
10040KO0000279932440001000000000728   
10013KO0000280936031001000000000612   
10040KO0000280936574001000000000035   
//SORTJNF2 DD *                       
10004KO0000131926500002000000000035   
10021KO0000162667162002000000000094   
10004KO0000239155685002000000000043   
10016KO0000258758008002000000000043   
10021KO0000279912883002000000000094   
10021KO0000280935517004000000000094   
10021KO0000280935517004000000000086   
10006KO0000280936760002000000000094   
10006KO0000280936760002000000000035   
10006KO0000280936825002000000000035   
//SYSIN    DD *                       
  JOINKEYS FILES=F1,FIELDS=(6,15,A)   
  JOINKEYS FILES=F2,FIELDS=(6,15,A)   
  SORT FIELDS=COPY                   
  JOIN UNPAIRED                       
  REFORMAT FIELDS=(F1:1,57,F2:1,57)                                   
  OUTFIL FNAMES=MATCHF1,INCLUDE=(6,1,CH,NE,C' ',AND,63,1,CH,NE,C' '), 
                        OUTREC=(1,57)                                 
  OUTFIL FNAMES=UNMATF2,INCLUDE=(6,1,CH,NE,C' ',AND,63,1,CH,EQ,C' '), 
                        OUTREC=(1,57)                                 
  OUTFIL FNAMES=UNMATF3,SAVE,OUTREC=(58,57)


Output file contians:-

3) Matched content based on joinkey (6,15,CH,A).

Code:

10012KO0000162667162001000000000647
10040KO0000279912883001000000000469


2) File02 records which donot have match in File01

UNMATF3:-

Code:

10018KO0000156435670001000000000469
10040KO0000279912830001000000000353
10040KO0000279912875001000000000450
10040KO0000279912903001000000000825
10040KO0000279912911001000000000892
10040KO0000279932440001000000000728
10013KO0000280936031001000000000612
10040KO0000280936574001000000000035


1) File01 records which donot have match in File02
UNMATF2:-

Code:

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


Thanks
-3nadh
Back to top
View user's profile Send private message
bhavin.mehta

New User


Joined: 25 Jun 2012
Posts: 34
Location: India

PostPosted: Tue Jun 26, 2012 5:17 pm
Reply with quote

@bodatrinadh: I tried it too. It was successful. Had a lot of learning today.
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Use input file with OMIT rcd keys? DFSORT/ICETOOL 15
No new posts Join multiple records using splice DFSORT/ICETOOL 5
No new posts Join 2 files according to one key field. JCL & VSAM 3
No new posts Issue after ISPF copy to Linklist Lib... TSO/ISPF 1
Search our Forums:

Back to Top