Join this group to access featured content and participate in discussions related to all Syncsort Mainframe Products.

98 Members
Join Us!

JOINKEYS & Var length fields

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 add it?

job output shows

17.25.48 JOB54633 +WER235A UBADS471,JS0030 , -SORTOF2 OUTREC RDW NOT INCLUDED
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 ... my apologies, I didn't understand how to add it properly
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')
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')
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

Read more: http://ibmmainframes.com/viewtopic.php?t=65187#ixzz4BnWXUaiY

You need to be a member of Syncsort Community to add comments!

Join Syncsort Community

Email me when people reply –

Replies

  • Hello, Miguel,

    In your post, you refer to using Example 7 as the method to gain expected results.  However, Example 2 should also be used because it shows how to code the REFORMAT Control Statement when both input files contain variable-length records.  During a “join” application where both input files (SORTJNF1 & SORTJNF2) contain variable-length records, the REFORMAT Control Statement must be coded as follows:

      -  The RDW (from either file) must appear only one time - and it must appear first - at the very beginning of the REFORMAT Statement.

      -  Followed by all fixed-length fields from the 2 input files
      - 
    Followed by variable-length fields from the 2 input files
     
     
     You should also be aware that the JOIN feature was originally designed to “join” on fixed-length fields, such as an Invoice number, a customer number, an employee ID number, a Social Security number, etc.
      It was not designed to “join” records based upon variable-length data fields with unlimited lengths and free-flowing data.

    • Hello Gillian,

      Thank you for taking the time to answer to my comment. I hadn't considered Example 2 before, so I will take a look.

      Let me say that will the help from Bill Woodger I was able to solve this one. Actually was his idea, so he solved for me. The solution was to include both files as variable, and fake the Reformat to be a fix length. So, in Reformat the variable fields were assigned the max length. the trick came in the OUTFIL to use make the fields variable using the FTOV, & VLTRIM.

      your last statement is the one that makes me think JOIN/JOINKEY won't solve my next issue. I have the precise scenario for your. This time I have a file with multiple variable fields, and the one I need the join done upon is the second. So, it is a variable length field starting at a variable position. your comment makes me think this can't be handle by JOINKEYs...

      Assuming you are working for Syncsort, is there any plans to enhance Joinkeys to handle this scenario?

      Thanks again :)

    • To answer your follow-up question, one must really start at the beginning.  Plain and simple, all sort applications require "input" data.  When a sort application uses a SORTIN DD Statement, the input data is already well-defined by looking at option 3.4 in ISPF.  Here you will find the file attributes, that is, the LRECL, the BLKSIZE, and most importantly the type of file, e.g.,  FB, FBA, VB, VBA, etc.

      A J
      OIN application is an alternative method that actually creates "input" data that will be processed in a sort step.  Since JOIN occurs early on during processing (referring to the Flow of the Sort), the newly created "input" data becomes eligible for all features that occur after SORTIN.  So whatever the programmer wants to do during a "normal" sort, he can also do with a JOIN application.

      T
      he big issue here is your comment:   "fake the Reformat to be a fix length"

      I
      t is important to know that the programmer controls the file attributes of his newly-created "input" data with the use of his REFORMAT Control Statement.  There is no "faking".  At the conclusion of JOIN processing, the data will either be fixed or variable.  ONLY when both SORTJNFx datasets contain fixed-length records will the output from JOIN always create fixed-length records.

      H
      owever, when either or both of the SORTJNFx datasets contain variable-length records, then the output from JOIN can be EITHER fixed or variable.  Here, this totally depends upon the data fields chosen by the programmer in his REFORMAT statement.  When at least one variable-length field is selected, then the output from JOIN will contain variable-length records.  If NONE of the variable-length fields from either SORTJNFx datasets are selected, then the output from JOIN will contain fixed-length records.  It's as simple as that!

      You further state:  
      "the trick came in the OUTFIL to use make the fields variable using the FTOV, & VLTRIM".   This statement is true whether you are executing a "normal" sort, or a JOIN application.

      Regarding your final comment:  
      "I have a file with multiple variable fields, and the one I need the join done upon is the second. So, it is a variable length field starting at a variable position. your comment makes me think this can't be handle by JOINKEYs..."

      As was stated earlier, 
      The JOIN feature was originally designed to “join” on fixed-length fields, such as an Invoice number, a customer number, an employee ID number, a Social Security number, etc, and was not designed to “join” records based upon variable-length data fields with unlimited lengths and free-flowing non-structured data fields.  You can use INCLUDE/OMIT to perform these types of "compare" functions.  At this time, Syncsort does not have any plans to change this basic premise.

       

    • Thank you for the responses, Philip.

    • Miguel,

      Not knowing the exact contents of your data files, I would suggest this as a possible method.  IF YOU DO HAVE a stable fixed-length value that can be used to Join 2 records that exist in 2 different files. then I suggest that you first "join" these records; then compare the contents of the 2 variable-length fields to see if the contents are the same.  I hope this information is helpful.  Phil

    • Hi Philip,
      I already gave up of that idea.
      That is sort of what I did.
      I went a couple of steps back and created the files as fixed length and joined them by the laaaarge field.
      I just thought comparing (joinkey) with variable length fields would be faster than long fields in Syncsort. Specially if the key most times is less than 1/5 of the max length
      It was worth the try.
      Thanks for your suggestion

    • forgot to say that one of the files I am comparing is way over 100m. So, I was trying to save some time..
      thanks

This reply was deleted.

To access Syncsort Knowledge Base, visit:

My Support