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

JOINKEYs and Variable files


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

New User


Joined: 08 Aug 2014
Posts: 44
Location: USA

PostPosted: Fri Jun 17, 2016 3:16 am
Reply with quote

Hello guys,
I am trying now to compare previous & current for the same file. Trying to catch the records being added, deleted and changed...
But the field I want to compare is a variable one. can be up to 255. Due to limitations on Syncsort Joinkeys, I am setting the field comparison to 64 bytes only....
Example 7 on "Exploiting Syncsort MFX: JOIN" manual is almost the one I need... except that I need the Join keys to be variable, also the input and output files...
so, according to the manual I should use REFORMAT from Section 2.234 of Programmer's Guide....

According to it, because I want the output files to be variable length to, I should add the RDW at the front of the record and the variable fields at the end without a length. I also put both variable fields at the end.
I want to exclude the identical records.
I am including RDWs from both input files, but apparently I need another RDW for each output file.
How can I solve this?

job output shows

Code:

17.25.48 JOB54633  +WER235A UBADS471,JS0030  ,       [b] -SORTOF2  OUTREC RDW NOT INCLUDED     
[/b]17.25.48 JOB54633  IEF450I UBADS471 JS0030 - ABEND=S000 U0016 REASON=00000000  117         
   117                     TIME=17.25.48                                                   
17.25.49 JOB54633  U11-656 JOBNAME=UBADS471,STPROC=        ,STSTEP=        ,AUTO SETUP PARMS                     


here is the sort

Code:
  JOIN  UNPAIRED,F1,F2                                                         
  JOINKEYS FILE=F1,FIELDS=(19,64,A)                                             
  JOINKEYS FILE=F2,FIELDS=(19,64,A)                                             
  REFORMAT FIELDS=(F1:1,004,        RDW F1                                     
                   F2:1,004,        RDW F2                                     
                   F1:05,013,       PREVIOUS TES                               
                   F2:05,013,       CURRENT TES                                 
                   F1:19,           PREVIOUS ESRIDNM                           
                   F2:19),          CURRENT ESRIDNM                           
                   FILL=X'FF'                                                   
*                  TYPE=V                                                       
  OMIT COND=(9,13,CH,EQ,22,13,CH,AND,35,255,CH,EQ,290,255,CH)                   
  SORT FIELDS=COPY                                                             
  OUTFIL FILES=1,                * DELETED ESRIDNMS                           
  INCLUDE=(9,1,BI,EQ,X'FF'),                                                   
  BUILD=(RDWD,DTES,PIP,PESR),                                                   
  HEADER2=(10:' DELETED ESRIDNMS',/,                                           
  01:'TES #',                                                                   
  15:'ESRIDNM DESCRIPTION')                                                   
  OUTFIL FILES=2,                * ADDED  ESRIDNMS                             
  INCLUDE=(022,1,BI,EQ,X'FF'),                                                 
  BUILD=(RDWA,ATES,PIP,FESR),                                                   
  HEADER2=(10:' ADDED ESRIDNMS',/,                                             
  01:'TES #',                                                                   
  15:'ESRIDNM DESCRIPTION')                                                   
* OUTFIL FILES=3,                * CHANGED ESRIDNMS                           
* INCLUDE=(022,1,BI,EQ,X'FF'),                                                 
* BUILD=(RDWDTES,PIP,DESR,PIP,ATES,PIP,FESR),                                   
* HEADER2=(10:' CHANGED ESRIDNMS',/,                                           
* 01:'TES #',                                                                   
* 15:'ESRIDNM DESCRIPTION')                                                   


Code:

DATA DICTIONARY SYMBOLS SUBSTITUTED :                                           
JOIN UNPAIRED,F1,F2                                                             
JOINKEYS FILE=F1,FIELDS=(19,64,A)                                               
JOINKEYS FILE=F2,FIELDS=(19,64,A)                                               
REFORMAT FIELDS=(F1:1,004,F2:1,004,F1:05,013,F2:05,013,F1:19,F2:19),FILL=X'FF' 
OMIT COND=(9,13,CH,EQ,22,13,CH,AND,35,255,CH,EQ,290,255,CH)                     
SORT FIELDS=COPY                                                               
OUTFIL FILES=1,INCLUDE=(9,1,BI,EQ,X'FF'),BUILD=(1,4,9,13,C'|',35,64),HEADER2=(10
:' DELETED ESRIDNMS',/,01:'TES #',15:'ESRIDNM DESCRIPTION')                   
OUTFIL FILES=2,INCLUDE=(022,1,BI,EQ,X'FF'),BUILD=(5,4,22,13,C'|',290,64),HEADER2
=(10:' ADDED ESRIDNMS',/,01:'TES #',15:'ESRIDNM DESCRIPTION')                 


Code:
WER813I  INSTALLATION OPTIONS IN MFX LOAD LIBRARY WILL BE USED                 
WER276B  SYSDIAG= 5009393, 12351294, 12351294, 9737185                         
WER164B  46,016K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,             
WER164B     0 BYTES ESRERVE REQUESTED, 1,000K BYTES USED                       
WER146B  20K BYTES OF EMERGENCY SPACE ALLOCATED                                 
WER481I  JOINKEYS REFORMAT RECORD LENGTH=  548, TYPE = V                       
WER235A  SORTOF2  OUTREC RDW NOT INCLUDED                                       
WER211B  SYNCSMF  CALLED BY SYNCSORT; RC=0000                                   
WER449I  SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE                                   
WER482I  JNF1 STATISTICS                                                       
WER483B  22,776K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,             
WER483B     0 BYTES ESRERVE REQUESTED, 1,000K BYTES USED                       
WER108I  SORTJNF1 : RECFM=VB   ; LRECL=   275; BLKSIZE= 27998                     


Code'd
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Jun 18, 2016 12:08 am
Reply with quote

looks like you have not included RDW , look at BUILD.
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 Jun 18, 2016 12:34 am
Reply with quote

The REFORMAT statement, like any record, only has "one" RDW, which is at position 1, for a length of 4. 5,4, although it was sourced from an RDW, is not an RDW, it is just data, so doesn't count.

You have to use the only RDW possible. SyncSORT will work out the rest.
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 Jun 18, 2016 12:43 am
Reply with quote

On top of that, REFORMAT records with two variable parts are... exciting.

You have two, more useful, possibilities: use a fixed-length REFORMAT record, and FTOV/CONVERT with VLTRIM on the OUTFILs; use one fixed-length source and one variable-length, and only one FTOV/CONVERT in OUTFIL.

It's also not clear what limitation you came across in the JOINKEYS which means you've stuck to 64 bytes.

Presuming that your data is in key order, use SORTED on each of the JOINKEYS statements. If not in key order, then you may be representing an artificial picture.
Back to top
View user's profile Send private message
migusd

New User


Joined: 08 Aug 2014
Posts: 44
Location: USA

PostPosted: Sun Jun 19, 2016 7:18 am
Reply with quote

this is the execution with the first part of your suggestions, Rohit & Bill

the msg I got is this
UBADS383,JS0030 , -SORTOF1 OUTREC RDW NOT INCLUDED

and the sort parms

Quote:
JOIN UNPAIRED,F1,F2
JOINKEYS FILE=F1,FIELDS=(19,64,A)
JOINKEYS FILE=F2,FIELDS=(19,64,A)
REFORMAT FIELDS=(F1:1,004, RDW
F1:05,013, 005 - 017 PREVIOUS TES
F2:05,013, 018 - 030 CURRENT TES
F1:19, 031 - 094 PREVIOUS ESRIDNM
F2:19), 095 - 158 CURRENT ESRIDNM
FILL=X'FF'
OMIT COND=(5,13,CH,EQ,18,13,CH,AND,031,064,CH,EQ,095,064,CH)
SORT FIELDS=COPY
OUTFIL FILES=1, * DELETED ESRIDNMS
INCLUDE=(5,1,BI,EQ,X'FF'),
BUILD=(DTES,PIP,PESR),
HEADER2=(10:' DELETED ESRIDNMS',/,
01:'TES #',
15:'ESRIDNM DESCRIPTION')
OUTFIL FILES=2, * ADDED ESRIDNMS
INCLUDE=(018,1,BI,EQ,X'FF'),
BUILD=(ATES,PIP,FESR),
HEADER2=(10:' ADDED ESRIDNMS',/,
01:'TES #',
15:'ESRIDNM DESCRIPTION')
* OUTFIL FILES=3, * CHANGED ESRIDNMS
* INCLUDE=(018,1,BI,EQ,X'FF'),
* BUILD=(RDW0,DTES,PIP,PESR,PIP,ATES,PIP,FESR),
* HEADER2=(10:' CHANGED ESRIDNMS',/,
* 01:'TES #',
* 15:'ESRIDNM DESCRIPTION')
DATA DICTIONARY SYMBOLS SUBSTITUTED :
JOIN UNPAIRED,F1,F2
JOINKEYS FILE=F1,FIELDS=(19,64,A)
JOINKEYS FILE=F2,FIELDS=(19,64,A)
REFORMAT FIELDS=(F1:1,004,F1:05,013,F2:05,013,F1:19,F2:19),FILL=X'FF'
OMIT COND=(5,13,CH,EQ,18,13,CH,AND,031,064,CH,EQ,095,064,CH)
SORT FIELDS=COPY
OUTFIL FILES=1,INCLUDE=(5,1,BI,EQ,X'FF'),BUILD=(5,13,C'|',31,64),HEADER2=(10:' D
ELETED ESRIDNMS',/,01:'TES #',15:'ESRIDNM DESCRIPTION')
OUTFIL FILES=2,INCLUDE=(018,1,BI,EQ,X'FF'),BUILD=(18,13,C'|',95,64),HEADER2=(10:
' ADDED ESRIDNMS',/,01:'TES #',15:'ESRIDNM DESCRIPTION')
WER813I INSTALLATION OPTIONS IN MFX LOAD LIBRARY WILL BE USED
WER276B SYSDIAG= 4290742, 11632663, 11632663, 9737185
WER164B 46,016K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,000K BYTES USED
WER146B 20K BYTES OF EMERGENCY SPACE ALLOCATED
WER481I JOINKEYS REFORMAT RECORD LENGTH= 544, TYPE = V
WER235A SORTOF1 OUTREC RDW NOT INCLUDED
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
Back to top
View user's profile Send private message
migusd

New User


Joined: 08 Aug 2014
Posts: 44
Location: USA

PostPosted: Sun Jun 19, 2016 7:43 am
Reply with quote

Hi Rohit & Bill,
I made a second attempt and still got the same
Quote:

JOIN UNPAIRED,F1,F2
JOINKEYS FILE=F1,FIELDS=(19,64,A),SORTED,TYPE=V
JOINKEYS FILE=F2,FIELDS=(19,64,A),SORTED,TYPE=V


I misunderstood the manual... there is no max length for the field in JOINKEY... what it says (p 2.58) the max number of fields for a JOINKEY is 64... my mistake...

I will try your second suggestion, Bill
Back to top
View user's profile Send private message
migusd

New User


Joined: 08 Aug 2014
Posts: 44
Location: USA

PostPosted: Sun Jun 19, 2016 9:31 am
Reply with quote

Hi Bill,
I tried your suggestion and didn't worked
Apparently SYNCSORT didn't like the idea of using a fixed Reformat and variable length files.

here is an extract
Quote:
JOIN UNPAIRED,F1,F2,ONLY
JOINKEYS FILE=F1,FIELDS=(19,255,A),SORTED,TYPE=V
JOINKEYS FILE=F2,FIELDS=(19,255,A),SORTED,TYPE=V
REFORMAT FIELDS=(F1:05,013, 001 - 013 PREVIOUS TES
F2:05,013, 014 - 026 CURRENT TES
F1:19,255, 027 - 281 PREVIOUS ESRIDNM
F2:19,255), 282 - 536 CURRENT ESRIDNM
FILL=X'FF'
OUTFIL FILES=1, * DELETED ESRIDNMS
INCLUDE=(5,1,BI,EQ,X'FF'),
BUILD=(DTES,PIP,DESR),
FTOV,VLTRIM=X'40',
HEADER2=(10:' DELETED ESRIDNMS',/,
01:'TES #',
15:'ESRIDNM DESCRIPTION'),
TRAILER1=(20/,
10:'NUMBER OF DELETED ESR IDS:',
COUNT)

....
WER107A SORTOF1 RECFM INCOMPATIBLE WITH REPORT WRITING
WER110I SORTOF1 : RECFM=VB ; LRECL= 275; BLKSIZE= 27998
WER110I SORTOF2 : RECFM=VB ; LRECL= 275; BLKSIZE= 27998
WER107A SORTOF2 RECFM INCOMPATIBLE WITH REPORT WRITING
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: Sun Jun 19, 2016 1:31 pm
Reply with quote

It's an oblique way of telling you that if you use OUTFIL reporting features you need an A in your RECFM for the output, because a printer-control character is inserted before the first position of each record.

Now, you don't actually want that. You'd like those Control Characters to not be there. So on the OUTFIL, you specify REMOVECC, and they won't be there.

Please use the Code tags, not the Quote tags, for marking-up anything which needs to retain proportional spacing.
Back to top
View user's profile Send private message
migusd

New User


Joined: 08 Aug 2014
Posts: 44
Location: USA

PostPosted: Sun Jun 19, 2016 11:34 pm
Reply with quote

yes!!!
That made the difference, Bill... ...
Thank you so much!!!
I owe you one more....
Thanks also to Rohit
icon_smile.gif
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 Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Merge two VSAM KSDS files into third ... JCL & VSAM 6
No new posts Joinkeys - 5 output files DFSORT/ICETOOL 7
Search our Forums:

Back to Top