|
View previous topic :: View next topic
|
| Author |
Message |
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Hi,
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?
| Code: |
//SORTJNF1 DD *
----+----1----+----2----+----3----+----4----+----5
ABC123
BCD234
CDE345
DEF456
//SORTJNF2 DD *
----+----1----+----2----+----3----+----4----+----5
FOT098
ABC123
ORP999
BCD234
CDE345
MXI019
DEF456
RFP890
|
Expected Output:
Matched Records:
| Code: |
----+----1----+----2----+----3----+----4----+----5
ABC123
ABC123
BCD234
BCD234
CDE345
CDE345
DEF456
DEF456
|
Unmatched Records:
----+----1----+----2----+----3----+----4----+----5
| Code: |
FOT098
MXI019
ORP999
RFP890
|
Please help.
Thanks. |
|
| Back to top |
|
 |
Garry Carroll
Senior Member
Joined: 08 May 2006
Posts: 1207
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.
Garry. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Does your version of SYNCSORT support SQZ? |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
| We are at v1.3.2 but how do I get them back at the positions shown in output after SQZ !? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Code: |
//SORTJNF1 DD *
A
//SORTJNF2 DD *
A
//JNF1CNTL DD *
INREC OVERLAY=(1:C' ') |
Put that into a simple JOINKEYS with key at position 1, length of 1.
Show the output.
This is just so we know whether you have the JNFnCNTL support from SyncSort.
Tells us how many steps you'll need to get your result. |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Bill, this is what I tried........
| Code: |
//SORT0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SORTJNF1 DD *
A
//SORTJNF2 DD *
A
//SYSIN DD *
OPTION COPY
JOINKEYS FILES=F1,FIELDS=(1,1,A)
JOINKEYS FILES=F2,FIELDS=(1,1,A)
REFORMAT FIELDS=(F1:1,80)
//JNF1CNTL DD *
INREC OVERLAY=(10:C' ')
|
Output:
SYSOUT:
| Code: |
********************************* 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.
Thanks. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
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).
| Code: |
//SORT0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SORTJNF1 DD *
A
//SORTJNF2 DD *
A
//SYSIN DD *
OPTION COPY
JOINKEYS FILES=F1,FIELDS=(1,1,A)
JOINKEYS FILES=F2,FIELDS=(1,1,A)
REFORMAT FIELDS=(F1:1,80)
//JNF1CNTL DD *
INREC OVERLAY=(1:C' ') |
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). |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
sorry for the typo........in fact I tried what you said and got the same results which tells JNF1CNTL does not work with Syncsort v1.3.2.2
| Code: |
//JNF1CNTL DD *
INREC OVERLAY=(1:C' ')
|
I tried below code on sample data I put in my first post and no SORTOUT is produced though job completes with MAXCC=0000 
| Code: |
//SORT0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SORTJNF1 DD *
ABC123
BCD234
CDE345
DEF456
//SORTJNF2 DD *
FOT098
ABC123
ORP999
BCD234
CDE345
MXI019
DEF456
RFP890
//SYSIN DD *
OPTION COPY
JOINKEYS FILES=F1,FIELDS=(25,6,A,25,6,A,25,6,A,25,6,A)
JOINKEYS FILES=F2,FIELDS=(10,6,A,15,6,A,20,6,A,30,6,A)
REFORMAT FIELDS=(F2:1,80)
|
SYSOUT:
| Code: |
********************************* 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 99999, MODEL 9999 999 LICEN
SYSIN :
OPTION COPY
JOINKEYS FILES=F1,FIELDS=(25,6,A,25,6,A,25,6,A,25,6,A)
JOINKEYS FILES=F2,FIELDS=(10,6,A,15,6,A,20,6,A,30,6,A)
REFORMAT FIELDS=(F2:1,80)
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
WER416B BSAM WAS USED FOR SORTOUT
WER054I RCD IN 0, OUT 0
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=67820
WER484I SORTJNF1 : RCD IN= 4,OMITTED= 0,PAIRED= 0,UNPAIRED= 4
WER416B BSAM WAS USED FOR SORTJNF1
WER487I FILESIZE 320 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=80239
WER484I SORTJNF2 : RCD IN= 8,OMITTED= 0,PAIRED= 0,UNPAIRED= 8
WER416B BSAM WAS USED FOR SORTJNF2
WER487I FILESIZE 640 BYTES
WER052I END SYNCSORT - JOBSORTT,SORT0001,,DIAG=CE00,40C0,E28E,00EE,8662,68CB,A
WER052I DIAG1=A600,E0CF,8A9E,2057,EF62,48C3,0600,0C62
WER052I DIAG2=EE00,E0C3,C29E,2CDF,A762,48CB,0600,0462
******************************** BOTTOM OF DATA ********************************
|
Thanks. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
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. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| Will there be duplicate keys within each input file? |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
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).
Thanks. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
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 |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
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.
File-1: (LRECL: 80, RECFM=FB)
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
04 TELVTOTAL 0123456789 XYZ012 MALL FINISH
04 VOLVTOTAL 0023456789 TAN668 MALZ FINISH @PO3FI
|
File-2: (LRECL: 80, RECFM=FB)
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
01 XYZ012 XTRCT PGM00490 2009-07-12-21.21.06.03947
02 48ALBTIA20XYZ012000001166324800001000001166324800001XRERX VR14526000000
06 SEVE129108001012009ERRS01218.35.21.997000VERMART11XYZ012 20130
05 01980 XYZ012
03 WIER 9550 XYZ012 $@^#CIU
01 VIZ913 XTRCT PGM00490 2009-07-12-21.21.06.03947
06 SEV5129108001012009ERRS01918.36.22.997000PAPMAER11VIZ913 29130
03 WIER 9550 VIZ913 %@##CMU
05 01980 VIZ913
05 01960 TAN668
01 TAN668 XTRCT PGM00490 2009-07-12-21.21.06.03947
03 WIER 9550 TAN668 @@@@TCU
06 SEV5129108001012009ERRS01918.36.22.997000OZMAITR11TAN668 29630
|
Expected Output:
| Code: |
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
01 TAN668 XTRCT PGM00490 2009-07-12-21.21.06.03947
03 WIER 9550 TAN668 @@@@TCU
04 VOLVTOTAL 0023456789 TAN668 MALZ FINISH @PO3FI
05 01960 TAN668
06 SEV5129108001012009ERRS01918.36.22.997000OZMAITR11TAN668 29630
01 XYZ012 XTRCT PGM00490 2009-07-12-21.21.06.03947
02 48ALBTIA20XYZ012000001166324800001000001166324800001XRERX VR14526000000
03 WIER 9550 XYZ012 $@^#CIU
04 TELVTOTAL 0123456789 XYZ012 MALL FINISH
05 01980 XYZ012
06 SEVE129108001012009ERRS01218.35.21.997000VERMART11XYZ012 20130
|
Thanks. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
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?
Key do not match - what do you want output? |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Hi,
| Quote: |
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.
Thanks. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
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. |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
| Quote: |
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.
Thanks. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
Ramsri,
You might want to try the below SYNCTOOL step.
| Code: |
//STEP01 EXEC PGM=SYNCTOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN DD DISP=SHR,DSN= Input file1 (FB/80)
// DD DISP=SHR,DSN= Input file2 (FB/80)
//T1 DD DISP=(NEW,PASS),UNIT=SYSDA
//OUT DD SYSOUT=*
//TOOLIN DD *
SPLICE FROM(IN) TO(T1) ON(81,6,CH) WITH(1,80) WITHALL KEEPBASE -
USING(CTL1)
SORT FROM(T1) TO(OUT) USING(CTL2)
//CTL1CNTL DD *
INREC IFTHEN=(WHEN=(1,2,ZD,EQ,1),OVERLAY=(81:10,6,X)),
IFTHEN=(WHEN=(1,2,ZD,EQ,2),OVERLAY=(81:20,6,X)),
IFTHEN=(WHEN=(1,2,ZD,EQ,3),OVERLAY=(81:30,6,X)),
IFTHEN=(WHEN=(1,2,ZD,EQ,4),OVERLAY=(81:40,6,C'$')),
IFTHEN=(WHEN=(1,2,ZD,EQ,5),OVERLAY=(81:50,6,X)),
IFTHEN=(WHEN=(1,2,ZD,EQ,6),OVERLAY=(81:60,6,X))
OUTFIL FNAMES=T1,INCLUDE=(87,1,CH,EQ,C'$')
//CTL2CNTL DD *
SORT FIELDS=(81,6,CH,A,1,2,CH,A)
OUTFIL BUILD=(1,80) |
OUT had
| Code: |
01 TAN668 XTRCT PGM00490 2009-07-12-21.21.06.03947
03 WIER 9550 TAN668 @@@@TCU
04 VOLVTOTAL 0023456789 TAN668 MALZ FINISH @PO3FI
05 01960 TAN668
06 SEV5129108001012009ERRS01918.36.22.997000OZMAITR11TAN668 29630
01 XYZ012 XTRCT PGM00490 2009-07-12-21.21.06.03947
02 48ALBTIA20XYZ012000001166324800001000001166324800001XRERX VR14526000000
03 WIER 9550 XYZ012 $@ #CIU
04 TELVTOTAL 0123456789 XYZ012 MALL FINISH
05 01980 XYZ012
06 SEVE129108001012009ERRS01218.35.21.997000VERMART11XYZ012 20130 |
|
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Arun, its giving me results as expected......thank you very much 
How to get unmatched records into another output file?
Thanks. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| ramsri wrote: |
Arun, its giving me results as expected......thank you very much
How to get unmatched records into another output file?
Thanks. |
You're welcome :-)
Unmatched records from file1 or file2? |
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Unmatched records from File-2 into another output file.
Thanks. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
Could you try modifying the sort to add an error file(ERR DD) as shown below?
| Code: |
....
...
//T1 DD DISP=(NEW,PASS),UNIT=SYSDA
//OUT DD SYSOUT=*
//ERR DD SYSOUT=*
...
...
//CTL1CNTL DD *
...
...
OUTFIL FNAMES=T1,INCLUDE=(87,1,CH,EQ,C'$')
OUTFIL FNAMES=ERR,SAVE,BUILD=(1,80) |
|
|
| Back to top |
|
|
ramsri
Active User
Joined: 18 Oct 2008
Posts: 380
Location: India
|
|
|
|
Hi,
Adding ERR has worked and writes unmatched from File-2.
| Code: |
01 VIZ913 XTRCT PGM00490 2009-07-12-21.21.06.03947
06 SEV5129108001012009ERRS01918.36.22.997000PAPMAER11VIZ913 29130
03 WIER 9550 VIZ913 %@##CMU
05 01980 VIZ913
|
Thanks. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
Thanks for letting know.  |
|
| Back to top |
|
|
|
|
|
