Have a file with keys appearing at 25th position. Have another file with key appearing at different positions. Need to join these two files and write matched records from both the files to output but in sorted order and unmatched records to another output file. Is this possible with Syncsort?
Joined: 08 May 2006 Posts: 1205 Location: Dublin, Ireland
The FIELDS= parameter of each JOINKEYS is used to specify the field location and length in each of the files to be joined. Search this forum for JOINKEYS for examples.
********************************* TOP OF DATA **********************************
SYNCSORT FOR Z/OS 1.3.2.2R U.S. PATENTS: 9999999, 9999999 (C) 2007 SYNCSO
XXXXXXXXXX XXXX XXXXXXX z/OS 1.13.0
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 999Z9, MODEL 9999 Z99 LICEN
SYSIN :
OPTION COPY 0000610
JOINKEYS FILES=F1,FIELDS=(1,1,A) 0000620
JOINKEYS FILES=F2,FIELDS=(1,1,A) 0000630
REFORMAT FIELDS=(F1:1,80) 0000640
WER276B SYSDIAG= 83170, 716950, 716950, 1262850
WER164B 30,352K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 0 BYTES RESERVE REQUESTED, 1,138,608 BYTES USED
WER146B 12K BYTES OF EMERGENCY SPACE ALLOCATED
WER481I JOINKEYS REFORMAT RECORD LENGTH= 80, TYPE = F
WER110I SORTOUT : RECFM=FB ; LRECL= 80; BLKSIZE= 80
WER410B 29,324K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER410B 0 BYTES RESERVE REQUESTED, 100K BYTES USED
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER416B BSAM WAS USED FOR SORTOUT
WER054I RCD IN 1, OUT 1
WER072I NOEQUALS, BALANCE IN EFFECT
WER169I RELEASE 1.3 BATCH 0506 TPF LEVEL 2.2
WER482I JNF1 STATISTICS
WER483B 14,856K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 14,080K BYTES USED
WER108I SORTJNF1 : RECFM=FB ; LRECL= 80; BLKSIZE= 80
WER483B 13,824K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER483B 0 BYTES RESERVE REQUESTED, 13,824K BYTES USED
WER483B G=82225
WER484I SORTJNF1 : RCD IN= 1,OMITTED= 0,PAIRED= 1,UNPAIRED= 0
WER416B BSAM WAS USED FOR SORTJNF1
WER487I FILESIZE 80 BYTES
WER482I JNF2 STATISTICS
WER483B 14,856K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 14,080K BYTES USED
WER108I SORTJNF2 : RECFM=FB ; LRECL= 80; BLKSIZE= 80
WER483B 13,824K BYTES OF VIRTUAL STORAGE AVAILABLE ABOVE THE 16MEG LINE,
WER483B 0 BYTES RESERVE REQUESTED, 13,824K BYTES USED
WER483B G=82225
WER484I SORTJNF2 : RCD IN= 1,OMITTED= 0,PAIRED= 1,UNPAIRED= 0
WER416B BSAM WAS USED FOR SORTJNF2
WER487I FILESIZE 80 BYTES
WER052I END SYNCSORT - JOBSORTT,SORT01,,DIAG=8C00,4248,A008,006C,C4F6,68C3,260
WER052I DIAG1=AE00,E045,8218,A85F,E7F6,48EB,0600,A664
WER052I DIAG2=CC00,C24B,E018,A4FD,85F6,68C3,2600,8C64
******************************** BOTTOM OF DATA ********************************
The data will be 1.5 and 4 million records in SORTJNF1 and SORTJNF2 respectively. So, don't know how many steps the job should have but a successful and expected output will solve it.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
OK. Gives us a "control". Now run it with 1: instead of 10:
This will tell us whether the JOINKEYS is using the JNF1CNTL file, because if it is, you will no longer get a match (because the A on F1 will have been overwritten with blank).
If the JNF1CNTL is supported, you will be able to do it in one step, as the processing required can be done there. If not, you will need more than one step.
Either way, you will need to "extend" your record to put the "floating" key into a fixed position. Then you will be able to the the JOINKEYS with that new key.
If the floating key is surrounded by space, either PARSE or SQZ/JFY will be able to get the key into position. If it is instead surrounded by data, you'll have to use IFTHENs for identifying which of the fixed-but-different positions it is in.
So, a full description will be useful. REFFMs, LRECLs, and an exact description, with sample input, of the positions that can be occupied by the floating key and whether or not there is data in the other positions, or whether it is "floating" in an area of blanks (or something else).
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
OK. You are correct, the JNFnCNTL is not supported with what you have. You may want to get your technical people to contact SyncSort support, as there is a "patch" available to give you the support.
In the mean time, or if your site does not want to install the patch, you'll have to have a separate step.
Exactly what that step does, I don't know, as you still haven't answered those bits, but in general terms it is "find the 'floating key' and make a copy of it in a fixed position in an extension to the record".
Then you do the JOINKEYS matching two single fixed keys. Then with the REFORMAT you cut off the extension and work out whether or not you matched, and use OUTFIL as appropriate.
Hi Arun, no duplicates in 1st file as it is a db2 table dump and a PK of it is our key for join operation. 2nd file is a IMS/DB dump (twin segments may cause duplicity).
Joined: 17 Oct 2006 Posts: 2481 Location: @my desk
Bill Woodger wrote:
a full description will be useful. REFFMs, LRECLs, and an exact description, with sample input, of the positions that can be occupied by the floating key and whether or not there is data in the other positions, or whether it is "floating" in an area of blanks (or something else).
Ramsri,
Bill has already directed you in the right path, if you post the above requested info, it might help us understand your problem better
Arun, here is the sample data....first two bytes is the segment number in both the files File-1 and File-2. File-1 has the key of 6 bytes appearing at 40th column. File-2 has the key appearing at 10th, 20th, 30th, 50th and 60th positions (based on segments present). Since File-2 is missing 4th segment, it should have it got from File-1 if match key found and in sorted order.
Joined: 09 Mar 2011 Posts: 7309 Location: Inside the Matrix
ramsri,
Is it:
You have "Segment 04 records" from a DB2 table dump (F1); you have "Segment 01 through 06 records" from an IMS dump, where at least Segments 02 and 04 are optional; the "key" is in a different location depending on "Segment id".
You want to match F1 to F2 on the "key".
Key match, no 04 on F2 - what do you want output?
Key match, 04 already present on F2 - what do you want output?
You have "Segment 04 records" from a DB2 table dump (F1); you have "Segment 01 through 06 records" from an IMS dump, where at least Segments 02 and 04 are optional; the "key" is in a different location depending on "Segment id".
You want to match F1 to F2 on the "key".
Key match, no 04 on F2 - what do you want output?
Key match, 04 already present on F2 - what do you want output?
Key do not match - what do you want output?
Yes......Segment 04 records are read from a DB2 table and prepared in a format suits IMS/DB.
Yes.......F1 and F2 should match on floating key.
F2 never has 04. It will be taken from F1 and should appear in sequence of segment number.
Key do not match........write them to separate files.
Do you have blank lines in input (and expected output) after each "key-group" as shown in the example?
In file2, will a particular "group" always have the same key value for each record (at different positions according to the segment number)?
Is there any segment number (say for eg, seg - 01) which will always be present for each key in file2.
No blank lines......To show sets of records I put it
Yes.....same key for a set of records appears at diff. positions.
Segment 01 always appears in file-2.
.jpg)
