Hi all,

we have a requirement where in we have to join hold file with Oracle table with multiple keys. is it possible to achieve this?

Thanks
Aparna

This should be possible.

To join to the Oracle table use the syntax:

JOIN
field1 AND field2 AND field3 IN HOLDFILE
TO field1 AND field2 AND field3 in oracletable
END

To join from the Oracle table use the syntax:

JOIN
field1 AND field2 AND field3 in oracletable
TO field1 AND field2 AND field3 IN HOLDFILE
END

the main difference is when you join to a HOLD file the fields you join to have to be in the sequence of the output from the Oracle request, that is, used as sort fields. Otherwise you will get an OUT OF SEQUENCE error.

I agree with Alan, of course depending on where your hold file resides and where your oracle table resides may cause some performance issues. I join hold files back to DB2 tables all the time, but if the hold file is large number of records, I will often pull the DB2 data from the table with a separate request, create a hold file then join the two hold files.

Very true Leah, a lot of records will give performance issues and dragging data back with a seperate request can be worthwhile.

You can join multiple field from hold to hold, sequence again permitting.