|
View previous topic :: View next topic
|
| Author |
Message |
souji
New User
Joined: 28 Aug 2008
Posts: 9
Location: Bangalore
|
|
|
|
| Hi I have not worked on ICE TOOL. I need to compare 2 files with 2 fields and write the unmatched record in the 3 rd files. |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
There are multiple examples in the forum. Pick one that is most similar to your requirement and go from there. |
|
| Back to top |
|
|
souji
New User
Joined: 28 Aug 2008
Posts: 9
Location: Bangalore
|
|
|
|
Hi d.sch.
This is for a RI process(reference integrity)
I have 2 files of differnt LRECL say FIN File and REF file and this needs to be compared and the missing data needs to be written to the 3 file. This would happen for the Current month. similarly everymonth FIN data gets loaded to the prior month table.Now the all the prior month data needs to be compared with REF file.
for example the CM, for the RI compare it with the reference data via a ICETOOL solution .Before you do that you can merge the CM data with the PM/PY RI records to see if there are any new ones. If there aren´t you don´t need to process the RI again.
Is it possible to merget the CM data with PM records and see the new records. |
|
| Back to top |
|
|
nelson.pandian
Active User
.jpg)
Joined: 09 Apr 2008
Posts: 133
Location: Phoenix, AZ
|
|
|
|
Hi Soujanya,
Please give some Sample input data and the expected output date. Specify the RECFM, LRECL, Position and length of the Key field and give the rules. |
|
| Back to top |
|
|
souji
New User
Joined: 28 Aug 2008
Posts: 9
Location: Bangalore
|
|
|
|
FILE A has LRECL =31 with following fields
X1 CHAR(003)
X2 CHAR(012)
X3 CHAR(008)
X4 CHAR(008)
This is compared with FILE B with LRECL = 15 with the following fields
Y1 CHAR(003)
Y2 CHAR(012)
The comparing is done on X1 of A file with Y1 of file B and X3 of FILE A with Y2 of file B. Here X3 is 8 char while we compare with Y2 which is 12 char..only the first 8 should be matched... The missing records in FILE B should be written to output file C.
Can you please provide the JCL for thsi using ICETOOL.
Thanks in advance... |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
| Quote: |
| Can you please provide the JCL for thsi using ICETOOL. |
Not until you provide the needed information.
You showed the layout of the records, but you didn't give an example of the records in the input file and what you expect for output. Show such an example with values for the relevant fields. Give the RECFM and LRECL of the input file. Explain the "rules" for getting from input to output with reference to the example. If input file1 can have duplicates within it, show that in your example. If input file2 can have duplicates within it, show that in your example. |
|
| Back to top |
|
|
souji
New User
Joined: 28 Aug 2008
Posts: 9
Location: Bangalore
|
|
|
|
Hello Frank,
Input file A
X1 X2 X3 X4
=============================================
111PPPPPPPPPPPPBBBBBBBBCCCCCCCC
222GGGGGGGGGGGGRRRRRRRRSSSSSSSS
333AAAAAAAAAAAAPPPPPPPPQQQQQQQQ
444XXXXXXXXXXXXYYYYYYYYZZZZZZZZ
FILE A will not have duplicate records,FILE B can have duplicate records or other records which not not present in FILE A.FILE B is like a reference file it can have any records. But FILE A should not contain any records that are missing in FILE B. The missing records should be copied to FILE C.
Input file B
Y1 Y2
===============================================
111BBBBBBBBBBBB
222PPPPPPPPPPPP
Compare FILE A and FILE B. on the KEYS X1 VS Y1 and X3 vs Y2. Note that X3 is 8 char while Y2 is 12 char so campare only the first 8 char,The missing records in FILE C corresponding records from FILE A should be written in FILE C
FILE C
Z1 Z2
===============================================
222RRRRRRRR
333YYYYYYYY
Can you please provide the JCL withb IECTOOL.
This is the 1 st part .
The above example also has a 2 nd request also which is bit complex.Not sure if this is possible.
Part 2
-------
In the above example FILE A is compared with FILE B on following Keys
X1 VS Y1
X3 VS Y2
AND ALSO
X1 VS Y1
X4 Vs Y2
So the out put will be like this.The out put should not have the duplicates.
FILE C
Z1 Z2
===============================================
222RRRRRRRR
333YYYYYYYY
111CCCCCCCC
222SSSSSSSS
333QQQQQQQQ
444ZZZZZZZZ
Not sure if the 2 nd part is possible Please let me know if you need more information .Thanks |
|
| Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005
Posts: 7129
Location: San Jose, CA
|
|
|
|
Sigh. Your description does NOT seem to match your example.
For the output, you show:
222RRRRRRRR
333YYYYYYYY
The 1/B record appears in both files, so I see why that isn't in the output file. The 2/R record in fileA does not have a match in fileB, so I see why that is in the output file. But the 3/P and 4/Y records in fileA do not have matches in fileB, so I would expect to see them in the output file. Instead you have a 3/Y record which does not appear in fileA at all.
You need to explain where the 3/Y record comes from. |
|
| Back to top |
|
|
souji
New User
Joined: 28 Aug 2008
Posts: 9
Location: Bangalore
|
|
|
|
Thanks for correcting me Frank...i am sorry for the confusion, the Out put file should contain the 3/P and 4/Y record.. and not the 3/Y record.. sorry for this mess up..
So the O/p will be
FILE C
Z1 Z2
===============================================
222RRRRRRRR
333PPPPPPPPP
444YYYYYYYYY
IMP thing here is X3 is 8 char.. while its been compared with Y2 which is 12 char... so only the first 8 char should be matched...
If possible the out put file should not contain duplicates.... its ok if we are not able to remove the duplicates when we write the FILE C .. we can always do it in the next step...
Can you please provide the JCL for this using ICETOOL....Thanks in advance...
in the above example is the 2 nd part possible....
So the O/p for part 2 will be
FILE C
Z1 Z2
===============================================
222RRRRRRRR
333PPPPPPPP
444YYYYYYYY
111CCCCCCCC
222SSSSSSSS
333QQQQQQQQ
444ZZZZZZZZ |
|
| Back to top |
|
|
Skolusu
Senior Member
Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
|
|
|
|
souji,
The following DFSORT/ICETOOL JCl will give you the desired results. It does both the comparisions X1X3 = Y1Y2 & X1X4 = Y1Y2 and writes out the missing records from file1. The output lrecl is 11 bytes. Make sure you have disp=(mod,pass) on the temp dataset t1
| Code: |
//STEP0100 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD DSN=Your 31 byte file,
// DISP=SHR
//IN2 DD DSN=Your 15 byte file,
// DISP=SHR
//T1 DD DSN=&&T1,DISP=(MOD,PASS),SPACE=(CYL,(X,Y),RLSE)
//OUT DD SYSOUT=*
//TOOLIN DD *
SORT FROM(IN2) USING(CTL1)
COPY FROM(IN1) USING(CTL2)
SPLICE FROM(T1) TO(OUT) ON(1,11,CH) WITH(1,11) WITHALL -
KEEPNODUPS KEEPBASE USING(CTL3)
//CTL1CNTL DD *
INREC BUILD=(1,15)
SORT FIELDS=(1,11,CH,A)
SUM FIELDS=NONE
OUTFIL FNAMES=T1,BUILD=(1,11,12:C'Y')
//CTL2CNTL DD *
OUTFIL FNAMES=T1,BUILD=(1,3,16,8,X,/,1,3,24,8,X)
//CTL3CNTL DD *
OUTFIL FNAMES=OUT,OMIT=(12,1,CH,EQ,C'Y'),BUILD=(1,11)
/* |
The output from the job is sorted on the key sequence.
| Code: |
111CCCCCCCC
222RRRRRRRR
222SSSSSSSS
333PPPPPPPP
333QQQQQQQQ
444YYYYYYYY
444ZZZZZZZZ |
|
|
| Back to top |
|
|
souji
New User
Joined: 28 Aug 2008
Posts: 9
Location: Bangalore
|
|
|
|
Thanks so much Skolusu for the JCL..  |
|
| Back to top |
|
|
|
|
