Hello,

I have a view created using outer join. I have created the same view on oracle as well as sqlserver. When I

select from the view on DB2, the performance is much slower than on oracle or sqlserver i.e. when I select a value based on index match on DB2 it takes 1767 msecs whereas the same select on oracle takes 16 msecs. So, could someone help me with whether my view definition is the right way to go on DB2 or whether there is a better way.

Here is my DB2 view definition.

create view z_workorder as
select
W.WONUM,
COALESCE(wl.description, w.description) description
from workorder w left outer join (select wonum, lang, description from wo_lang where lang = get_lang() ) as wl

on w.wonum = wl.wonum
@

The get_lang() function returns a language value. I have tried by eliminating the get_lang() function and

hard coding a value, which did not make much difference, just a few msecs.

Here is the oracle definition

create view Z_workorder as
select
W.WONUM,
nvl(wl.description, w.description) description
from workorder w left outer join wo_lang wl
on w.wonum = wl.wonum AND wl.lang = SYS_CONTEXT('MXLANG', 'language');

Could someone help me with the outer join so I get a good performance out of it.

Thanks in advance.

Manju

I'm not experienced with Oracle, but I'm sure you can have the same performance (or almost the same) with DB2. The first thing you must check, is your configuration. Are you comparing apples with apples? Do both databases reside on machines with same processor, same memory allocation (not in the machine, I'm talking from bufferpool allocation for DB2), same OS? Bonnie Baker (www.bonniebaker.com) uses to say "if you by a pet and you don't by food for your pet, your pet won't be happy"...

For the query:
Did you run Explain on it? Which access path did DB2 choose?
Do you have the same indexes defined on both databases?

And, by the way, you can code your query as you did in Oracle:

create view z_workorder as
select
W.WONUM,
COALESCE(wl.description, w.description) description
from workorder w left outer join wo_lang wl
on w.wonum = wl.wonum
where wl.lang = get_lang()

If this query runs in a programm, I would first make a call for get_lang(), put the value on a host variable and change the where clause to "where wl.lang = :host_variable".

HTH.