|
View previous topic :: View next topic
|
| Author |
Message |
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
Hi,
I have 2 files
FILE-1 : VB 133. KEY : 1st 4 position.
0001 ABCD 2013-05-01
0005 SDSA 2013-05-01
0004 SDAD 2013-05-01
0002 DASD 2013-05-01
FILE-2 : VB 140. KEY : 1st 4 position.
0006 ADAS 2013-05-02
0001 ABCD 2013-05-02
0008 DSDA 2013-05-02
0007 SDAW 2013-05-02
0002 DASD 2013-05-01
Need to compare the Keys in the FILE-1 and FILE-2 and created the 3rd file which will have all non matching records and if we have matching key records then copy the record from FILE-B to the output file.
FILE-OUT may look like this:
0001 ABCD 2013-05-02
0002 DASD 2013-05-01
0004 SDAD 2013-05-01
0005 SDSA 2013-05-01
0006 ADAS 2013-05-02
0007 SDAW 2013-05-02
0008 DSDA 2013-05-02
Please help to know if this can be achieved using ICETOOL and how ?
Thanks !!! |
|
| Back to top |
|
 |
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Hi,
If I understand your requirement
You need matching records from file 2 and non matched from both files correct?
Also do you expect the output in the order given above? |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
Yes, That would be correct. Matching records from FILE-2 and non matching from both the files.
If its possible to have it in the sorted order that would be preferred otherwise can always add a SORT.
Thanks ! |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Does it have to be ICETOOL? A plain SORT with JOINKEYS will do it. |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
| No. I am just looking for a solution to achieve this also the inputs are VB with different lengths |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
I never used JOINKEYS or ICETOOL.
Can anyone help with the solution ? Or let know if its not possible to implement in 1 or 2 steps ?
Thanks ! |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
| ranjitbhingare wrote: |
I never used JOINKEYS or ICETOOL.
Can anyone help with the solution ? Or let know if its not possible to implement in 1 or 2 steps ?
Thanks ! |
Go to page 3 of this section and look up the topic "JOINKEYS isssue with RECFM" and look up the solution in page 2 of that topic. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Well, there are lots of examples here, and there are some good manuals (link in the forum, and at the top of the page).
It is a JOINKEYS you need, you'll need the ? (which tells you whether the current REFORMAT record has data from File "1" or File "2" or "B"oth files) on a REFORMAT statement (along with the data you need from the two files).
It is a simple JOINKEYS process.
The only "complicated" thing is that both inputs are VB. So, leave that aside for the moment, and do your processing with "DD *" for your input (Fixed, LRECL 80). Get the process working, if you get stuck post back here with what you have tried, and the problem you have encountered.
Once working with Fixed Length Records, sort it out for Variable Length. Here it is more likely to be problematic, but if you get stuck, there'll be someone here to assist. |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
Hi,
By going through the manual I came up with following card :
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(5,15,A)
JOINKEYS FILE=F2,FIELDS=(5,15,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:5,15,F2:5,15),FILL=C'$'
SORT FIELDS=COPY
OUTFIL INCLUDE=(21,1,CH,NE,C'$',AND,1,1,CH,NE,C'$'),
BUILD=(16,15)
/*
1. This card gives me only matching keys from F2
2. When I try to add the entire records from F1 and F2 in reformat (both are 133 byte VB) then the REFORMAT goes out of bounds.
3. How to add Non matching records from F1 and F2 in the same output ? |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
That was a SyncSort manual, wasn't it? SyncSort questions live in the JCL forum.
If 16,1 (in your example) is $, you want to BUILD the data from F1. Otherwise, you want to build the data from F2 (which will be for F2 only or for a match, where you want the F2 record only).
Then you need to make a variable-length REFORMAT record, with F1 temporarily fixed-length and F2 variable, then when you BUILD the output records, use the RDW from the REFORMAT record, and the fixed F1 data, variable F2 data, and try VLTRIM for the OUTFIL dataset. |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
The both input files F1 & F2 are VB 133 bytes.
When I mentioned the length of 133 byte in reformat, sort does not accept it and get error that its out of bounds.
Will it be possible to compare to VB 133 byte files on the key and copy matching records from F2 and non matching from both the files.
Help would be appreciated as I am not familiar either with ICETOOL or JONKEYS .
Thanks ! |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Code: |
| REFORMAT FIELDS=(F1:1,4,5,129,F2,1,4,5) |
OK, there's your variable-length REFORMAT statement.
You have to make one of the records "fixed" (since they are the same LRECL, I have arbitrarily chosen to do it with F1).
The REFORMAT record needs an RDW, either from F1 or F2. The correct length will appear there once the REFORMAT record is complete.
Now, I've guessed you have SyncSort, so continue to the "character which can't appear" approach. I'd suggest in bytes 5 for absence of 136 for absence of F2.
You'll need two OUTFILs, and one one you'll need VLTRIM to get rid of the trailing characters for unmatched records which have come from F1.
| Code: |
OUTFIL FNAMES=MISMATCH,VLTRIM=C'$',
INCLUDE=(5,1,CH,EQ,C'$',
OR,136,1,CH,EQ,C'$'),
IFTHEN=(WHEN=(5,1,CH,EQ,C'$',
BUILD=(1,4,136)),
IFTHEN=(WHEN=NONE,
BUILD=(1,4,5,129))
OUTFIL FNAMES=MATCH,SAVE |
Something like that.
This is untested, so get to it. |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
Thanks for the reply !
I have tried REFORMAT this way but it throws the error 'WER230A REFORMAT FIELD OUTSIDE RANGE' |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Show the full sysout from the step which failed. Psychic day this week is... er.. not sure, so it can't be today. |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
| Code: |
REFORMAT FIELDS=(F1:1,4,5,129,F2:1,4,5),FILL=C'$'
SORT FIELDS=COPY
OUTFIL VLTRIM=C'$',
IFTHEN=(WHEN=(1,1,CH,NE,C'$',AND,134,1,CH,NE,C'$'),
BUILD=(1,4,135)),
IFTHEN=(WHEN=NONE,BUILD=(1,4,5,129))
WER276B SYSDIAG= 1083168, 1636367, 1636367, 3515100
WER164B 102,444K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 40K BYTES RESERVE REQUESTED, 1,004K BYTES USED
WER146B 20K BYTES OF EMERGENCY SPACE ALLOCATED
WER230A REFORMAT FIELD OUTSIDE RANGE
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE |
Code'd |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Please use the Code tags. Please also respond fully to requests. Post the full sysout from the step please, as you have missed some of the Control Cards. |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
| Code: |
SYNCSORT LICENSED FOR CPU SERIAL NUMBER 6EDFD, MODEL 2096 P04
SYSIN :
JOINKEYS FILE=F1,FIELDS=(5,15,A)
JOINKEYS FILE=F2,FIELDS=(5,15,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,4,5,129,F2:1,4,5),FILL=C'$'
SORT FIELDS=COPY
OUTFIL VLTRIM=C'$',
IFTHEN=(WHEN=(1,1,CH,NE,C'$',AND,134,1,CH,NE,C'$'),
BUILD=(1,4,134)),
IFTHEN=(WHEN=NONE,BUILD=(1,4,5,129))
WER276B SYSDIAG= 1083168, 1636367, 1636367, 3515100
WER164B 102,444K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER164B 40K BYTES RESERVE REQUESTED, 1,004K BYTES USED
WER146B 20K BYTES OF EMERGENCY SPACE ALLOCATED
WER230A REFORMAT FIELD OUTSIDE RANGE
WER211B SYNCSMF CALLED BY SYNCSORT; RC=0000
WER449I SYNCSORT GLOBAL DSM SUBSYSTEM ACTIVE
WER482I JNF1 STATISTICS
WER483B 6,900K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 1,000K BYTES USED
WER108I SORTJNF1 : RECFM=VB ; LRECL= 92; BLKSIZE= 27993
WER073I SORTJNF1 : DSNAME=MNV.TESTS.QA.D36PSP01.S36PS01.TSTUNL.T0521G1
WER482I JNF2 STATISTICS
WER483B 6,900K BYTES OF VIRTUAL STORAGE AVAILABLE, MAX REQUESTED,
WER483B 0 BYTES RESERVE REQUESTED, 1,000K BYTES USED
WER108I SORTJNF2 : RECFM=VB ; LRECL= 92; BLKSIZE= 27993
WER073I SORTJNF2 : DSNAME=MNV.TESTS.QA.D36PSP01.S36PS01.TSTUNL.T0521G2 |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
There we go, much easier now we can see everything. You have LRECL of 92 for both input files, not the 133 you claimed.
Either use the correct files, or change the lengths of the data you use in the Control Cards. |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
I got the output after using correct length input files.
| Code: |
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(5,15,A)
JOINKEYS FILE=F2,FIELDS=(5,15,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,4,5,129,F2:1,4,5),FILL=C'$'
SORT FIELDS=COPY
OUTFIL VLTRIM=C' ',
IFTHEN=(WHEN=(1,1,CH,NE,C'$',AND,134,1,CH,NE,C'$'),
BUILD=(1,4,138,129)),
IFTHEN=(WHEN=(134,1,CH,EQ,C'$'),BUILD=(1,4,9,129))
|
But the output contains only matching and non matching records from F2 and blank records in place of non matching records from F1.
What change is required so that non matching from F1 will be added to outout. with the added condition I am getting blank records instead
Output :
| Code: |
1111114|2002|1|.00|.00|0|2003-08-01-15.18.28.265416
1111114|2003|1|.00|.00|0|2003-08-01-15.18.28.265527
1111114|2007|2|.00|.00|2|2007-07-14-15.46.59.513033
1111114|2008|2|.00|.00|1|2008-03-29-17.49.24.833665 |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
You are already getting non-matching F1 records on your output.
| Code: |
OUTFIL VLTRIM=C'$',
IFTHEN=(WHEN=(1,1,CH,EQ,C'$'),BUILD=(1,4,C'F2 ONLY|',138)),
IFTHEN=(WHEN=(134,1,CH,EQ,C'$'),BUILD=(1,4,C'F1 ONLY|',9,129)),
IFTHEN=(WHEN=NONE,
BUILD=(1,4,C'F1 AND 2',138))
|
Try the simplification above. Why were you trimming blanks when you FILLed with $s? |
|
| Back to top |
|
|
ranjitbhingare
New User
Joined: 30 Nov 2005
Posts: 94
Location: PUNE
|
|
|
|
I got it !
Thanks a lot for your help Bill ! I really appreciate all suppport.
The issue was after using FILL=C'$', The '$' was filled only in F1 and not in F2 thus the conditions were not working correctly.
Thus I changed to following :-
| Code: |
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(5,15,A)
JOINKEYS FILE=F2,FIELDS=(5,15,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,4,5,129,F2:1,4,5),FILL=C'$'
SORT FIELDS=COPY
OUTFIL VLTRIM=C'$',
IFTHEN=(WHEN=(5,1,CH,NE,C'$',AND,138,1,CH,NE,C' '),
BUILD=(1,4,C'F1F2-',138,129)),
IFTHEN=(WHEN=(5,1,CH,NE,C'$',AND,138,1,CH,EQ,C' '),
BUILD=(1,4,C'F1-',5,129)),
IFTHEN=(WHEN=(5,1,CH,EQ,C'$',AND,138,1,CH,NE,C' '),
BUILD=(1,4,C'F2-',138,129))
/* |
and now I got the desired result.
Output -
| Code: |
F1F2-XXXXXXX|2002|1|.00|.00|0|2003-08-01-15.18.28.265416
F1F2-XXXXXXX|2003|1|.00|.00|0|2003-08-01-15.18.28.265527
F1-XXXXXXX|2007|1|.00|.00|2|2007-07-14-15.46.59.513033
F2-XXXXXXX|2007|2|.00|.00|2|2007-07-14-15.46.59.513033
F1-XXXXXXX|2008|1|.00|.00|1|2008-03-29-17.49.24.833665
F2-XXXXXXX|2008|2|.00|.00|1|2008-03-29-17.49.24.833665 |
Now the question is, why FILL=C'$' did not fill the F2 rec with '$' ? Is there any other parameter missing in the card ? |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| Syncsort manual wrote: |
| Binary zeros will be used instead of the FILL character for the first four bytes of a variable-length record requiring FILL processing |
Looks like it is enough to check the first 4 bytes of the Variable length records(RDW) of each file to know if there is a matching record or not.
I just ran a test for the OPs requirement and it works well!
| Code: |
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(5,15,A)
JOINKEYS FILE=F2,FIELDS=(5,15,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,4,1,133,F2:1,4,5)
SORT FIELDS=COPY
OUTFIL VLTRIM=C' ',
IFTHEN=(WHEN=(138,4,CH,EQ,X'00'),
BUILD=(1,4,C'F1-',9,129)),
IFTHEN=(WHEN=(005,4,CH,EQ,X'00'),
BUILD=(1,4,C'F2-',142,129)),
IFTHEN=(WHEN=NONE,
BUILD=(1,4,C'F1F2-',142,129)) |
| Quote: |
| Now the question is, why FILL=C'$' did not fill the F2 rec with '$' ? Is there any other parameter missing in the card ? |
I ran a test and noticed that, as the OP suggests the FILL option does not actually 'FILL' the missing F2 fields in case no match exists in file2, where as it does 'FILL' the F1 fields if file1 has no matching key record. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Arun, I think testing the record-length (or the whole RDW) is the best way with SyncSort. I suggested it here, but difficult to know from that particular TS whether it worked :-)
I don't know why the FILL should not apply to both records. If it is documented differently and the problem is repeatable, then it should be passed on to SyncSort. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2482
Location: @my desk
|
|
|
|
| Quote: |
| testing the record-length (or the whole RDW) is the best way with SyncSort |
Yes Bill, I have checked the RDW in my test above and looks like that is the only way out for Syncsort (at least for now until the '?' function gets included).
As for the 'FILL' the manual says two things.
- It will FILL the unused bytes of a variable length record.
- It will FILL the missing fields in a REFORMAT when there is no matching key.
But it does not mention anything about a variable length file JOIN when both the scenarios can occur simultaneously, but our tests show that it does not FILL the missing file2 fields for VB JOINs. May be Alissa could have a look at this or someone can send a note to Syncsort to bring this to their attention. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
Here is one from Alissa with two VB and JOINKEYS.
The difference is that the full length is specified on the REFORMAT for both files, rather than just one. Maybe with the "start-position-only" it does not "qualify" for the FILL? |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
