We are on DB2- OS/390. Our application constitutes of queries with following pattern in the where clause

where
CAS_PART_ID = :h_part_id
OR
:h_part_id = 0

DB2 is not using indexes for the above mentioned patterns. And hence leading to tablespace scans. Any ideas to improve the access paths....

Thanks & Regards,
Nagesh

The query doesn't make a lot of sense to me... It basically means "select record(s) with the specified part id or, if part id is not specified (is 0) select all records from the table". Since, at the time of the query compilation it is not known whether the host variable is 0 or not the optimizer decides that it might be reading the entire table and for this reason chooses table scan. I'm not sure if you can affect this behaviour without re-writing the query itself...

Query functionally signifies..
if h_part_id is specified by the user, use the clause CAS_PART_ID = :h_part_id
else
show all the rows in the table...

We cannot rewrite the query as this is a functional requirement. I don't know if we can achieve this functional requirement in any other way.

Can we explore the options of "DB2 deciding the access path at run time". What should we do for this....

Thanks & Regards,
Nagesh

I don't see why you can't write 2 SQL statements and execute the appropriate one based on the value of the host-variable.

We have NOT less than 10columns in primary keys and the query with part_id where clause is a template....

In real scenario, query looks ....

where
(col1 = :h1
or
:h1 = 0)
and
(col2 = :h2
or
:h2 = 0)
....
....
....
(col10 = :h10
or
:h10 = 0)

and in real time scenario, any of above mentioned may/may not be passed...

Hence writing separate queries for each case isn't practical

Thanks & Regards,
Nagesh

But fact is that with "... OR :HOST = 0" you are going to have massive performance problems.

Better way (not a perfect sollution either) is to have many key-ranges like this:

where col1 between :h1_low and :h1_high
and col2 between :h2_low and :h2_high
and col3 between :h3_low and :h3_high
...

Now, when you need to have certain column, issue
the host value to both :hx_low and :hx_high variables.

Otherwise :hx_low and :hx_high have LOWVALUE and HIGHVALUE.

This way this query may use some indexes, but still it's a pain in the...

Cheers, Bill

...which translates into:

If all of the paramters are missing return the entire table; otherwise use whatever criteria are available

I guess you'll have to resort to dynamic sql and build the WHERE clause 'on the fly' from whatever host variables are not 0.