An issue I have with JOINKEYS is the lack of (real) positions ( in the REFORMAT statement. The reason being I would like to use symbols to place the data exactly the same place in the output file - no matter if the input file(s) changes. So, in addition to state where in F1/F2 the data comes from, I would like to be able to state where in the reformatted record the data should be placed. It's NOT good enough to be able to control the output record with OUTFIL, since if you are using symbols in the REFORMAT statement, you can't know if they were changed (Because, here we use dynamically generated symbols as masks for record layouts).
In addition, it would be great to be able to position the ? using the ':' as well.
I hope you'll consider these issues I have with JOINKEYS, because I really love JOINKEYS and use it a lot. I will be happy to explain further and/or supply with examples, if need be.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
Hi Claes,
If possible, can you post the sysout for the sort prior to the joinkeys step and the sysout from the joinkeys step.
There might be an "old school" way to ascertain which record(s) are the problem:
If you do a JOINKEYS with F1 being the file you have a problem with, without the SORTED, use
Code:
JOIN UNPAIRED,F1,ONLY
and a REFORMAT for the entire F1 record.
You will then have a file in sorted JOINKEYS order which you can use in a comparison product to match to your original "pre-sorted" file and thus identify the out-of-sequence records.
Thanks for your reply. It's a good idea you present, and I already have a job to do the match-stuff (using SEQNUM). I will try this on Monday (weekend now ) and also post the SYSOUT if nessesary.
In the mean time, I invite you all to comment on my 2nd issue.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
I'm not sure I understand your second part well enough.
You can specify, byte-by-byte, exactly where you want to put data on the REFORMAT record, only not by specifying a column (with the colon) but by specifying all the data up to that point. The same with the ?. You can tell DFSORT exactly where to put it, by specifying what goes in front of it, if anything.
So, you mention symbols and their generation. Are you saying that, in circumstances where you want to include an entire record on the REFORMAT, you'd like to be able to use the same symbol names?
That could be done for one of the files, directly, but not for both, as the most relevant part of the symbols are the position and length. The first part of the REFORMAT record could always be identical to one of the input records if you want, but the second part of the REFORMAT record cannot be because of the increased displacement of the first part of the REFORMAT record.
I could be completely misunderstanding this, but...
What I do is define the record with symbols in such a way that only one actual start position is specified: all other symbols for the record are relative to that first one. Then I can easily "change the position" of a record whilst using symbols without having to re-code everything. Of course, I have to give the symbol a new name, so I have a "prefix" which changes as to the location being used.
If you are generating symbols, it should be possible to include a prefix and a starting point for the symbols, so that standard and equivalent symbols can be used in the REFORMAT statement where you have the whole record.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Claes Norreen,
1.) Unfortunately there is no direct way to know the out of sequence key. However you can use this sample to find the out of sequence key
Code:
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
A
B
C
A
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC OVERLAY=(10:SEQNUM,8,ZD)
SORT FIELDS=(1,1,CH,A),EQUALS
OUTREC OVERLAY=(20:SEQNUM,8,ZD)
OUTFIL INCLUDE=(10,8,ZD,NE,20,8,ZD),ACCEPT=1
//*
2. I am with bill on this. Can you show us some examples of what you want? Remember that you can use INREC IFTHEN right after REFORMAT to the main task which you can format the way you want.
Thanks both for your time First, I will try to illustrate my issue of 2):
At my installation, we make heavily use of full DB2 UNLOADs - thus, every day we unload every single DB2 table to a flat dataset. Using the output PUNCH cards of the DB2 UNLOAD, I automatically generate SYMNAMES for each unload dataset. Now, these data goes into a lot of processing jobs, which merge the data in all kinds of ways (ie, the business).
To avoid having to recode a lot of jobs in the event of a new column to one of the DB2 tables, I make use of the DFSORT symbols to position fields on exact positions. So, even if a DB2 column is added to a table (and the UNLOAD dataset extended), it will not affect the running jobs at all.
Example:
UNLOAD of Customers must be joined with UNLOAD of orders:
First, I need symbols for the output record. This is done by automatically generating them from what is equivalent to a COBOL copybook. So, when I need new data in the output, I just change the "copybook", and regenerate the symbols.
Now, if anything changes in either SYMNAMES DD, it still works without any intervention. This is doable when using the “old fashion way” using COPY, COPY, SPLICE and OUTFIL.
I hope this makes it more clear? I’ll get back to 1) later today.
This would give you the data as you want it, so I'm assuming by what you suggest you'd like a method to be able to "name" the fields in the REFORMAT record, or, to put it another, to define positions for that data to occupy on the REFORMAT record using columns (the colon)).
However, what if CUST_CUSTOMERNAME were at position 5000 and ORDER_ORDERNO were at 4999? It's lots of extra space, plus the fields overlap.
What you want to get out of it, is the subsequent code works even when the positions of the symbols for the input fields change due to changes in the data.
Correct, this is what I want - IF we assume the output record is Customerno, Customername and Orderno. Now, suppose I added a field to the output record in between Customerno and Customername. Now, I would need to alter the REFORMAT code to adjust for this. But if I was able to use names (positions, really) for the REFORMAT statement, then I wouldn't have to worry about it at all, as the output would still comply with the specs given for it without any changes to the code.
In short, I would like to be able to use positions ( : ) in the REFORMAT statement, just as I do in the OUTFIL keyword.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
Claes Norreen,
We will add your suggestion to the long list of enhancements. However did you know that you can use INREC right after REFORMAT which lets you format the input the way you wanted?
You just need to generate the symbols considering the reformat record.
something like this
in the REFORMAT statement. The reason being I would like to use symbols to place the data exactly the same place in the output file - no matter if the input file(s) changes. So, in addition to state where in F1/F2 the data comes from, I would like to be able to state where in the reformatted record the data should be placed. It's NOT good enough to be able to control the output record with OUTFIL, since if you are using symbols in the REFORMAT statement, you can't know if they were changed (Because, here we use dynamically generated symbols as masks for record layouts).
) and also post the SYSOUT if nessesary.
