Oppolzer - Informatik / Blog

A.M. wrote:

Hi,

db2 v9 in z/OS.

I have a large query that joins several tables and views. It works fine
generally finishing in less than 30 seconds.

But only for some data conditions in the WHERE clause it slows down a lot and
finishes in 8 minutes.

e.g.

....
AND ((ROLLNUM LIKE 'P15%) OR
(ROLLNUM LIKE 'P16%) OR
(ROLLNUM LIKE 'P17%))
....

In this case it takes 8 minutes to finish. But for any other values of ROLLNUM, e.g.

alone (ROLLNUM LIKE 'P15%)
or
alone (ROLLNUM LIKE 'P16%)
or
alone (ROLLNUM LIKE 'P17%)

or both (ROLLNUM LIKE 'P16%) OR (ROLLNUM LIKE 'P17%)

or (ROLLNUM LIKE 'P33%) OR (ROLLNUM LIKE 'P34%) OR (ROLLNUM LIKE 'P35%)
it finishes in seconds.

I have no idea why for those set of three data conditions P15, P16 and P17, it
takes so long.

I have checked the number of rows in the table for these conditions and there
are other data values that have far more rows in table than these three. But
they finish in seconds too but not these three.

I have checked the access path and its using indexes on the table where column
ROLLNUM exists and has good cardinality although ROLLNUM itself is not part of
the index.

Any idea what should I be looking at to resolve this issue?

Apologies if the problem description was not very clear.

Thanks in advance!
A.