"73blazer" <yoyo@ma.com> wrote in message
news:LuadnWXiKe6aT8PfRVn-pQ@centurytel.net...
> We are migrating a customer from Version 7.1 FP3, to Version 8.2 (8.1
> FP8).
> For the most part, things are faster, but there is one query that is much
> much slower, and it is a query that is used all the time.
>
> select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
> where S_PART_NUMBER like '%KJS%'
>
> The widlcard before and after seems to be hosing it, but for this
> particular piece of the application, this type of query is neccessary.
>
> On Version 7.1, this query takes about 1.5 seconds (for 36 returns out of
> 120,000 things in the table)
> On Version 8.2, this query takes 13.8 seconds. (A copy of the production
> database)
> Most other queries are the same or faster.
> I've improved my query by using
>
> select ATTR1,ATTR2,ATTR3,ATTR4 from physical.part_list
> where posstr(S_PART_NUMBER,'KJS')>0
>
> This query is around 3.5 seconds. Much better, but still slower than
> version 7.1 at 1.5 seconds
>
> Is there anything I can do to improve that time to get it back to 1.5
> seconds? What happened in DB2 8? I was reading one article that was saying
> the optimization engine was changed such that 8 out of 10 queries will be
> faster. Is the double wildcard one of the 2 that is slower?
>
> DB2 8.1FP8 (8.2) on AIX 5.2 ML04 with fixes
> Optimization value of the default 5
>
> Ken

First, do a reorg of the table and all its indexes.

Then make sure you execute runstats and get distribution stats on key
columns and S_PART_NUMBER if it is not an indexed column.

If that does not help, you should evaluate the bufferpools and make sure the
index is in a similar buffer pool as V7 (with same objects as before). It is
possible that in your old system, the index was more likely to be resident
in the bufferpool and not flushed out by other pages.

Also, check the tablespace attributes (extent size, prefetch, etc) of the
index (assuming that S_PART_NUMBER is indexed).