Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Issue in Join keys
Goto page Previous  1, 2
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7314

PostPosted: Tue Jun 26, 2012 4:49 pm    Post subject: Reply to: Issue in Join keys
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    Post subject:
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    Post subject:
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    Post subject: Reply to: Issue in Join keys
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Goto page Previous  1, 2
Page 2 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts GTT issue - help ! jzhardy DB2 7 Sat Mar 23, 2019 4:17 am
No new posts Tivoli job scheduler issue John Poulakos IBM Tools 12 Sat Mar 23, 2019 12:32 am
No new posts SORT INNER JOIN [F1] OUT OF SEQ ERROR balaji81_k DFSORT/ICETOOL 8 Wed Jan 30, 2019 3:21 am
No new posts ACS Routines Management class issue upendrasri All Other Mainframe Topics 5 Mon Jan 28, 2019 4:51 am
No new posts Dates compare on specific dates using... balaji81_k DFSORT/ICETOOL 2 Thu Jan 24, 2019 12:03 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us