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

Joinkeys Match/NoMatch not working


IBM Mainframe Forums -> SYNCSORT
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Fri Feb 07, 2014 8:14 pm
Reply with quote

When I run the code below... I get the matched and unmatched records correctly...

However, I only want to have specific columns in the match and unmatch files... So i want the reformat step to be like this..

REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)

Not this.. which is what is below.

REFORMAT FIELDS=(F1:1,600,F2:1,600)


When I do use this reformat record in the code below...
REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)

I get this error :

JOINKEYS REFORMAT RECORD LENGTH= 106, TYPE = F

So Then, I change the the output records to this...

OUTFIL FILES=02,INCLUDE=(107,1,CH,EQ,X'40'),BUILD=(1,106)
OUTFIL FILES=01,INCLUDE=(107,1,CH,NE,X'40'),BUILD=(1,106)

But then get this error:

SORTOF02 : RECFM=FB ; LRECL= 106; BLKSIZE= 27984
SORTOF01 : RECFM=FB ; LRECL= 106; BLKSIZE= 27984
SORTOF02 : DSNAME=W951BFS.SORTJN1.DRV.UNMATCH
SORTOF01 : DSNAME=W951BFS.SORTJN1.DRV.MATCH
SORTOF02 INCLUDE/OMIT FIELD BEYOND RECORD


How Do I get the matched/unmatched records to populate with the reformat fields that I want?

FYI, Im using SYNCSORT 1.4.1.0R


Code:


//SORTUM1 EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//SORTJNF1 DD DSN=TSBVN.TEST.B960.BFS.D131122,DISP=SHR         
//SORTJNF2 DD DSN=TSBVN.TEST.B960.BFS.D131122.PAD,DISP=SHR     
//SORTOF01 DD DSN=W951BFS.SORTJN1.MATCH,DISP=(,CATLG,DELETE), 
//            DCB=(RECFM=FB,LRECL=600,BLKSIZE=0),             
//            UNIT=DISK,SPACE=(TRK,(75,75),RLSE),VOL=SER=STM539
//SORTOF02 DD DSN=W951BFS.SORTJN1.UNMATCH,DISP=(,CATLG,DELETE),
//            DCB=(RECFM=FB,LRECL=600,BLKSIZE=0),             
//            UNIT=DISK,SPACE=(TRK,(75,75),RLSE),VOL=SER=STM539
//SORTUM1.SYSIN    DD *                                       
 JOINKEYS FILES=F1,FIELDS=(1,3,A)                             
 JOINKEYS FILES=F2,FIELDS=(4,3,A)                             
 REFORMAT FIELDS=(F1:1,600,F2:1,600)                           
 SORT FIELDS=COPY                                             
 JOIN UNPAIRED,F1                                             
 OUTFIL FILES=02,INCLUDE=(601,1,CH,EQ,X'40'),BUILD=(1,600)     
 OUTFIL FILES=01,INCLUDE=(601,1,CH,NE,X'40'),BUILD=(1,600)     
[/Search]
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Fri Feb 07, 2014 8:24 pm
Reply with quote

Can you please paste the full sysout from the step, including the message references.
Back to top
View user's profile Send private message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Fri Feb 07, 2014 8:36 pm
Reply with quote

Bill Woodger wrote:
Can you please paste the full sysout from the step, including the message references.


I get this error, but the imput files are 600 RECORD length..

Code:

WER110I  SORTOF01 : RECFM=FB   ; LRECL=   600; BLKSIZE= 27600         
WER074I  SORTOUT  : DSNAME=W951BFS.ANY1.SORTOUT                       
WER074I  SORTOF02 : DSNAME=W951BFS.SORTKEY.UNMATCH                     
WER074I  SORTOF01 : DSNAME=W951BFS.SORTKEY.MATCH                       
WER247A  SORTOUT  HAS INCOMPATIBLE LRECL                               
WER247A  SORTOF02 HAS INCOMPATIBLE LRECL                               
WER247A  SORTOF01 HAS INCOMPATIBLE LRECL                               
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                         
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                         
WER482I  JNF1 STATISTICS                                               
WER483B  2,556K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,     
WER483B     0 BYTES RESERVE REQUESTED, 1,008K BYTES USED               
WER108I  SORTJNF1 : RECFM=FB   ; LRECL=   600; BLKSIZE= 27600         
WER073I  SORTJNF1 : DSNAME=TSBVN.TEST.B960.BFS.D131122.Q21CUST.RFMT   
WER482I  JNF2 STATISTICS                                               
WER483B  2,556K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,     
WER483B     0 BYTES RESERVE REQUESTED, 1,008K BYTES USED               
WER108I  SORTJNF2 : RECFM=FB   ; LRECL=   600; BLKSIZE= 27600         
WER073I  SORTJNF2 : DSNAME=TSBVN.TEST.B960.BFS.D131122.Q21USER.RFMT.PAD


So then I change the imput files to 106 FB and get this message..

WER276B SYSDIAG= 294870, 1205824, 1205824, 2299800
WER164B 91M BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,008K BYTES USED
WER146B 12K BYTES OF EMERGENCY SPACE ALLOCATED
WER230A REFORMAT FIELD OUTSIDE RANGE
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER482I JNF1 STATISTICS
WER483B 6,908K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 1,008K BYTES USED
WER108I SORTJNF1 : RECFM=FB ; LRECL= 106; BLKSIZE= 27878
WER073I SORTJNF1 : DSNAME=W951BFS.ANY1.CUST.FB106
WER482I JNF2 STATISTICS
WER483B 6,908K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 1,008K BYTES USED
WER108I SORTJNF2 : RECFM=FB ; LRECL= 106; BLKSIZE= 27878
WER073I SORTJNF2 : DSNAME=W951BFS.ANY1.USER.FB106
[/code]

Bottom line is that I just want to use my original code in the first post that works.. but I want to write out specific columns... spefically this:

Code:

REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)
[/quote]
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Fri Feb 07, 2014 9:06 pm
Reply with quote

Quote:
So then I change the imput files to 106 FB and get this message..


Why would you change the input files to 106?

You need to leave your input files as they are, and ensure that you output files either don't specify DCB info (better) or have DCB info reflecting the length of the output records.
Back to top
View user's profile Send private message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Fri Feb 07, 2014 9:17 pm
Reply with quote

Bill Woodger wrote:
Quote:
So then I change the imput files to 106 FB and get this message..


Why would you change the input files to 106?

You need to leave your input files as they are, and ensure that you output files either don't specify DCB info (better) or have DCB info reflecting the length of the output records.


I changed it to 106 just because im not sure why its not working.. so im trying anthying I can think of..

How to I take my original code, and reformat teh colums I want?
I can only get the match/unmatch dataset to populate data if I have my reformat do all the colums.

Code:

REFORMAT FIELDS=(F1:1,600,F2:1,600)



But I don't want to have all the columns.. I want this:
Code:

REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)


But I get errors when i try this..[/code]
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Fri Feb 07, 2014 9:57 pm
Reply with quote

Re-read my previous post.

Your error is occurring, I expect, because your output files are defined, either in the JCL or in the catalog, as 600 bytes, FB. When you try to write 106 bytes FB from SORT, SORT is clever enough to notice, so fails.

Your input files need to be 600. Your output files need to be 106.

Your output files will be 106 if you remove LRECL information from the JCL (if that is where you are specifying it) or if you are using pre-allocated output files, change the LRECL for the allocation to 106 (and remember to do that every time you have to change the REFORMAT statement).

Your REFORMAT statement is working. It is just the way you have the JCL and/or catalog that is getting in the way.
Back to top
View user's profile Send private message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Fri Feb 07, 2014 10:09 pm
Reply with quote

Bill Woodger wrote:
Re-read my previous post.

Your error is occurring, I expect, because your output files are defined, either in the JCL or in the catalog, as 600 bytes, FB. When you try to write 106 bytes FB from SORT, SORT is clever enough to notice, so fails.

Your input files need to be 600. Your output files need to be 106.

Your output files will be 106 if you remove LRECL information from the JCL (if that is where you are specifying it) or if you are using pre-allocated output files, change the LRECL for the allocation to 106 (and remember to do that every time you have to change the REFORMAT statement).

Your REFORMAT statement is working. It is just the way you have the JCL and/or catalog that is getting in the way.



I changed my code to what you have specified. Here is the code I am now running.

Code:

//SORTJN1 EXEC PGM=SORT                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SORTJNF1 DD DSN=TSBVN.TEST.B960.BFS.D131122.Q21CUST.RFMT,DISP=SHR     
//SORTJNF2 DD DSN=TSBVN.TEST.B960.BFS.D131122.Q21USER.RFMT.PAD,DISP=SHR
//SORTOF01 DD DSN=W951BFS.SORTKEY.MATCH,DISP=(,CATLG,DELETE),           
//            UNIT=DISK,SPACE=(TRK,(75,75),RLSE),VOL=SER=STM539         
//SORTOF02 DD DSN=W951BFS.SORTKEY.UNMATCH,DISP=(,CATLG,DELETE),         
//            UNIT=DISK,SPACE=(TRK,(75,75),RLSE),VOL=SER=STM539         
//SORTOUT  DD DSN=W951BFS.ANY1.SORTOUT,                                 
//            DISP=(,CATLG,DELETE),                                     
//            UNIT=DISK,SPACE=(CYL,(25,25),RLSE)                       
//SORTJN1.SYSIN    DD *                                                 
 SORT FIELDS=COPY                                                       
 JOINKEYS FILES=F1,FIELDS=(1,3,A)                                       
 JOINKEYS FILES=F2,FIELDS=(4,3,A)                                       
 JOIN UNPAIRED,F1                                                       
 REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)           
 OUTFIL FILES=02,INCLUDE=(601,1,CH,EQ,X'40'),BUILD=(1,600)             
 OUTFIL FILES=01,INCLUDE=(601,1,CH,NE,X'40'),BUILD=(1,600)             
//       


And here is the error message I am now getting
Code:

WER276B  SYSDIAG= 194507, 1116296, 1116296, 2299875                   
WER164B  5,632K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,     
WER164B     0 BYTES RESERVE REQUESTED, 1,008K BYTES USED               
WER146B  12K BYTES OF EMERGENCY SPACE ALLOCATED                       
WER481I  JOINKEYS REFORMAT RECORD LENGTH=  106, TYPE = F               
WER230A  SORTOF02 OUTREC FIELD OUTSIDE RANGE                           
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                         
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                         
WER482I  JNF1 STATISTICS                                               
WER483B  2,556K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,     
WER483B     0 BYTES RESERVE REQUESTED, 1,008K BYTES USED               
WER108I  SORTJNF1 : RECFM=FB   ; LRECL=   600; BLKSIZE= 27600         
WER073I  SORTJNF1 : DSNAME=TSBVN.TEST.B960.BFS.D131122.Q21CUST.RFMT   
WER482I  JNF2 STATISTICS                                               
WER483B  2,556K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,     
WER483B     0 BYTES RESERVE REQUESTED, 1,008K BYTES USED               
WER108I  SORTJNF2 : RECFM=FB   ; LRECL=   600; BLKSIZE= 27600         
WER073I  SORTJNF2 : DSNAME=TSBVN.TEST.B960.BFS.D131122.Q21USER.RFMT.PAD
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Fri Feb 07, 2014 10:19 pm
Reply with quote

Code:
 OUTFIL FILES=02,INCLUDE=(601,1,CH,EQ,X'40'),BUILD=(1,600)             
 OUTFIL FILES=01,INCLUDE=(601,1,CH,NE,X'40'),BUILD=(1,600) 


Well, here, you still want to output your 600-byte records.

You either need to change this to write out your 106-byte records (with 102 bytes of blanks for your unmatched records) or change them to 106 and 4 bytes...

Or go back to explain exactly what it is you want to do.

This may, or may not, be what you want.

Code:
 OUTFIL FILES=02,INCLUDE=(5,1,CH,EQ,C' '),BUILD=(1,4)             
 OUTFIL FILES=01,SAVE 
Back to top
View user's profile Send private message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Fri Feb 07, 2014 10:33 pm
Reply with quote

Bill Woodger wrote:
Code:
 OUTFIL FILES=02,INCLUDE=(601,1,CH,EQ,X'40'),BUILD=(1,600)             
 OUTFIL FILES=01,INCLUDE=(601,1,CH,NE,X'40'),BUILD=(1,600) 


Well, here, you still want to output your 600-byte records.

You either need to change this to write out your 106-byte records (with 102 bytes of blanks for your unmatched records) or change them to 106 and 4 bytes...

Or go back to explain exactly what it is you want to do.

This may, or may not, be what you want.

Code:
 OUTFIL FILES=02,INCLUDE=(5,1,CH,EQ,C' '),BUILD=(1,4)             
 OUTFIL FILES=01,SAVE 


that seems... to work.. but the UNMANTCH files is only 4 bytes.. I want the unmatched file to have the same fields and format as the MATCH file...
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Fri Feb 07, 2014 10:35 pm
Reply with quote

Well, since there is no match, you only have data on F1. You can BUILD for 106, if you want the 102 bytes of blanks.

Or, if in the case of no match you want the equivalent F1 fields, you have to include those in the REFORMAT for the F1 part, and then include those in the BUILD.

BUILD=(1,106) for the unmatched, BULD=(1,4,107,102) for the matched (assuming you have included all your F1 fields in the REFORMAT so it is now 208 bytes long).
Back to top
View user's profile Send private message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Fri Feb 07, 2014 10:53 pm
Reply with quote

Bill Woodger wrote:
Well, since there is no match, you only have data on F1. You can BUILD for 106, if you want the 102 bytes of blanks.

Or, if in the case of no match you want the equivalent F1 fields, you have to include those in the REFORMAT for the F1 part, and then include those in the BUILD.

BUILD=(1,106) for the unmatched, BULD=(1,4,107,102) for the matched (assuming you have included all your F1 fields in the REFORMAT so it is now 208 bytes long).



I guess im not following..

Right now i have the UNMATCHED file which just contains 2 records and just shows the first 4 bytes...

I want the format of the UMATCHED file to be be the same format of the MATCHED files, what would be the specific code to accomplish this?

I want the MATCHED and UNMATCHED dataset to contain this format.. right now.. only the matched file contains this format.

Code:

REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Fri Feb 07, 2014 11:24 pm
Reply with quote

In the REFORMAT statement, all the F1 fields come your your first JOINKEYS file.

You specify JOIN UNPAIRED,F1. That will get you matched records, and unmatched records from F1 only (unmatched records from F2 will be ignored).

For you matched records, this, REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9), gives you what you want.

However, what do you want when there is a record on F1 which does not match F2? All the fields following F2 on the REFORMAT statement will contain blanks in such a case.

You either want those blanks in your output, in which case also do BUILD=(1,106) for your unmatched records, or you want the data in the same positions from F1. There is no other choice. There is no F2 data that can go there, because you didn't match.

So:

Code:
REFORMAT FIELDS=(F1:1,3,64,1,1,3,7,30,37,30,67,30,125,9F2:1,3,7,30,37,30,67,30,125,9)


This will give you a REFORMAT record which contains all the fields from F1 in all cases, and fields from F2 in the case of matches.

Then you can use the BUILDs that I previously suggested.

If this is not what you want, you'd better state, clearly, what it is you do want.
Back to top
View user's profile Send private message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Fri Feb 07, 2014 11:45 pm
Reply with quote

Bill Woodger wrote:
In the REFORMAT statement, all the F1 fields come your your first JOINKEYS file.

You specify JOIN UNPAIRED,F1. That will get you matched records, and unmatched records from F1 only (unmatched records from F2 will be ignored).

For you matched records, this, REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9), gives you what you want.

However, what do you want when there is a record on F1 which does not match F2? All the fields following F2 on the REFORMAT statement will contain blanks in such a case.

You either want those blanks in your output, in which case also do BUILD=(1,106) for your unmatched records, or you want the data in the same positions from F1. There is no other choice. There is no F2 data that can go there, because you didn't match.

So:

Code:
REFORMAT FIELDS=(F1:1,3,64,1,1,3,7,30,37,30,67,30,125,9F2:1,3,7,30,37,30,67,30,125,9)


This will give you a REFORMAT record which contains all the fields from F1 in all cases, and fields from F2 in the case of matches.

Then you can use the BUILDs that I previously suggested.

If this is not what you want, you'd better state, clearly, what it is you do want.



Thanks!, its making sense to me now..

The reason I have only the first 4 bytes in the UNMATACHED records, it because my reformat step for F1 only has 4 bytes..

Code:

REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)


Is there a way to have the UNMATCHED Records show all the records in the F1 file? so ;ike 1.106 ? instead of just the first 3 bytes and the 1 byte in position 64?
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Sat Feb 08, 2014 12:38 am
Reply with quote

Still not clear what you want, but try the REFORMAT from my previous post.
Back to top
View user's profile Send private message
dan smith

New User


Joined: 07 Feb 2014
Posts: 8
Location: usa

PostPosted: Sat Feb 08, 2014 1:34 am
Reply with quote

Bill Woodger wrote:
Still not clear what you want, but try the REFORMAT from my previous post.


Thanks for your help bill. I think I understand it now, and have what I need.
One last question...

For the below code.

Code:


SORT FIELDS=COPY                                           
JOINKEYS FILES=F1,FIELDS=(1,3,A)                           
JOINKEYS FILES=F2,FIELDS=(4,3,A)                           
JOIN UNPAIRED F1                                           
REFORMAT FIELDS=(F1:1,3,64,1,F2:1,3,7,30,37,30,67,30,125,9)
OUTFIL FILES=02,INCLUDE=(5,1,CH,EQ,C' '),BUILD=(1,106)     
OUTFIL FILES=01,SAVE                                       


On this line

Code:


OUTFIL FILES=02,INCLUDE=(5,1,CH,EQ,C' '),BUILD=(1,106)


Why is it INCLUDE=(5,1 ???
Where did you get 5 from?
I noticed if i change ths to 4, i get totally different results in the UNMATCHED record..
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


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

PostPosted: Sat Feb 08, 2014 1:47 am
Reply with quote

The 5,1 is left over from earlier.

It now needs to be 107,1.

SyncSort does not have a "marker" to indicate a match. By default an the "other" record from a mismatch is set to blanks, so you get blanks in the REFORMAT record. This can be modified using FILL= on the REFORMAT.

If the first byte of your F2 on the REFORMAT record can never be blank, then you can test for blanks to know there is no F2 record. Else you find some other byte which can never be blank, or some byte which can never have a particular value and use FILL=.
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 -> SYNCSORT

 


Similar Topics
Topic Forum Replies
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
No new posts PD not working for unsigned packed JO... DFSORT/ICETOOL 5
No new posts Def PD not working for unsigned packe... JCL & VSAM 3
No new posts Sort with JOINKEYS using two VB files DFSORT/ICETOOL 1
No new posts ICETOOL with JOINKEY for Big record l... DFSORT/ICETOOL 12
Search our Forums:

Back to Top