MVSFORUMS.com

[mfjin]

File A LRECL=80 RECFM=FB
Lookup file

[kolusu]

Mfjin,

I cannot relate your JOb to your output.You are missing a copy statement in the TOOLIN for formating the lookup(80 byte) file to 446 byte file. I don't understand as to why you need to temp datasets.

If I understand correctly , you have a 80 byte look up file and you want to take a couple of fields from this look up file and then update a 446 byte file with the values from the lookup files for all the matching keys. is that right?

Now the question is what do you want to do with the non matching keys in the 446 byte file?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[mfjin]

Kolusu
You have got my requirement right.
Regarding Non Matching keys:
Records in the 446 byte file whose key is not present on the lookup file
should remain as they are,i.e the 156th-161st bytes and 366th byte should
contain whatever they have.(in this case it will be spaces)

[mfjin]

Oh yes Kolusu I missed 2 stmts in my toolin
My toolin looks like this

//TOOLIN DD *
SORT FROM(IN1) TO(TEMP1) USING(CTL1)
SORT FROM(IN2) TO(TEMP2) USING(CTL2)
SPLICE FROM(CON) TO(OUT1) ON(1,9,CH) WITH(156,6) -
WITH(366,1) KEEPNODUPS
SORT FROM(OUT1) TO(OUT2) USING(CTL3)

[Frank Yaeger]

mfjin,

There's actually quite a lot wrong with your job. The main problems are (1) you need to splice using file1 as the base and file2 as the overlay, but you're doing it in reverse and (2) you're trying to use a seqnum to get the records back in their original order, but you forgot to insert the seqnum. Also, your first two operators are SORT operators, but they should be COPY operators.

Here's a DFSORT/ICETOOL job that will do what you want based on your description of what you want. You'll probably have to study it a bit to understand how it works vs your job.

[mfjin]

Thanks Frank. I will get SPLICE right one day.
While I understood your first two observations, you say that


"Also, your first two operators are SORT operators, but they should be COPY operators"

The purpose of the first two operators are to reformat the files to enable he splicing operation. Cant SORT be used to do this?

[Frank Yaeger]

Well, yes you could use SORT operators instead of COPY operators to reformat the files for the SPLICE, but it would be very inefficient to do that. COPY is much more efficient than SORT. SPLICE does the SORT on 1,9,CH,A so why would you want to SORT on 1,9,CH,A twice and then do it again with SPLICE? Using two copies (COPY operators) and a sort (SPLICE operator) is much more efficient than doing three sorts (two SORT operators and a SPLICE operator). For the best performance, you should always use COPY instead of SORT when you can.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

[mfjin]

Got it..Cheers Frank !!! Hopefully i shouldnt struggle for a similar job next time.

[mfjin]

Frank,
I tried this out today and I seem to be losing records after splicing.
For instance if my main file has two records with the same key that is not
present in the lookup file. ONLY ONE of them comes out in the output. Is this the result of using KEEPNODUPS ?

[Frank Yaeger]

mfjin,

I based my solution on the data you showed me which has a match in the lookup file for every record in the main file. If the data is actually more complicated than what you showed originally, then you need to show me a more complete example of the input records and what you expect the output to look like.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

[mfjin]

Ok Frank. here is the updated example

Lookup file
1-9 10-16
123456 IDSX
567015 IDSY
345678 IDSX
906615 IDSY



Main file

123456 xxa .....
30000 abc .....
30000 jhk
123456 def......
345678 fgh......
567015 nom......
345678 eti......
906615 lkg......
786345 eti.......

Output file

123456 xxa ..... IDSX I
30000 abc .....
30000 jhk......
123456 def...... IDSX I
345678 fgh...... IDSX I
567015 nom.....IDSY J
345678 eti........IDSX I
906615 lkg...... IDSY J
786345 eti.......


Now the previous job you gave me is certainly doing this.
But I get only record in the output with the non-match key 30000.The other record is getting dropped.
Please note that there are two records in the main file with key 30000.
I need both of them in my output.

[mfjin]

Frank,
Got some idea on why this is happening

If there are duplicates in the main file having an unmatched key, SPICE tries to splice the bottom record on the top one and thereby drops the bottom record from the output. It treats it as the overlay record and the first record as the base. Ideally these records should not be spliced as they are unmatched records. Is there a way out tp prevent the the non matched duplicate records from getting spliced ?

[Frank Yaeger]

The reason that you're losing one of the non-matched dups is that SPLICE doesn't know it's from file2, so it splices the two dups together to get one record. To keep the non-matched dups in file2, you have to protect them by inserting an extra base record for each non-matched dup, as in the DFSORT/ICETOOL job below:

[mfjin]

Thanks Frank. Worked like a dream. Hopefully I shouldnt bother you the next time I use splice. Thanks again

[Frank Yaeger]

It's no bother. I'm happy to help.
_________________
Frank Yaeger - DFSORT Development Team (IBM)
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
DFSORT is on the Web at:
www.ibm.com/storage/dfsort