sumeet wrote:

> insert into PS_HBC_CUSTR_LKUP_wk select * from PS_HBC_CUSTR_LKUP a where
> substr(a.FI_INSTRUMENT_ID,1,3) IN ('XIM','XAM','XST','XCL') and
> a.fi_instrument_id = ( select b.HBC_INSTRUMENT_FR from PS_HBC_INSTRUMENT b
> WHERE b.HBC_INSTRUMENT_FR = a.fi_instrument_id)
> SQL0437W Performance of this complex query may be sub-optimal. Reason
> code:
> "6". SQLSTATE=01602

Here's a few thoughts:
1. load from a cursor that includes this query. It won't help the
select time, but will help the insert time.
2. activate non-logging. Again, that'll only help on the insert side
3. perform runstats, include indexes, consider distribution options for
fi_instrument_id column
4. consider using a method that will allow you to just
insert/update/delete changes, rather than inserting entire result set
5. replace correlated subquery with join, for example:

insert into PS_HBC_CUSTR_LKUP_wk
SELECT a.*
FROM PS_HBC_CUSTR_LKUP a
INNER JOIN PS_HBC_INSTRUMENT b
ON b.HBC_INSTRUMENT_FR = a.fi_instrument_id
where substr(a.FI_INSTRUMENT_ID,1,3) IN ('XIM','XAM','XST','XCL')


ken

DB2 & UDB email list listserv db2-l LazyDBA home page