It seems that using the coalesce funtion in my select statements are causing table scans. Does anyone know if this is true and if it is a work around for this? It doesn't makes sense that it does since it is part of the select statement. I want the ability if I get a null value in certain situations to change the column value.

Thanks,
Ted

No I don't think it's true; therefore a work-around is not necessary. A function, COALESCE or not, does not cause table scans by itself - but the way you use that function can.

What's wrong with table scans, anyway?

Here is an example of where it is happening for me:

Select BPPLNAME, BPFAMILY, BPMODEL, BPPRICE, BPNET, BPEFFI, BPEFFO, COALESCE(BPINCTXT,''), IDESC, IFLG02
FROM PRCBP LEFT OUTER JOIN IIML01 ON (BPITEM = IPROD) WHERE BPPLNAME = 'NECAD' AND '2005-02-01' BETWEEN BPEFFI AND BPEFFO;

If I run this it does a table scan on PRCBP if I remove the COALESCE function it uses the logical I have setup. I agree it shouldn't cause a table scan and don't understand why this function would cause this to happen.

Table Scans if possible should always be avoided, I don't want to read thru the entire table if I don't want to. In this scenario the query runs 3ms slower with COALESCE in the select now granted 3ms is not much but if you have bigger queries the impact is going to be greater.

This is makes no sense why this would be causing a table scan, I thought it might be how I have written my query but when I remove COALESCE it uses the logicals.

Any help or suggestions would be greatly appreciated!
Thanks,
Ted

So your are saying this runs much faster:

Select BPPLNAME, BPFAMILY, BPMODEL, BPPRICE, BPNET, BPEFFI, BPEFFO, BPINCTXT, IDESC, IFLG02
FROM PRCBP LEFT OUTER JOIN IIML01 ON (BPITEM = IPROD) WHERE BPPLNAME = 'NECAD' AND '2005-02-01' BETWEEN BPEFFI AND BPEFFO;

or did you remove the column BPINCTXT completely from the query when it ran faster?

I ran this:
Select BPPLNAME, BPFAMILY, BPMODEL, BPPRICE, BPNET, BPEFFI, BPEFFO, BPINCTXT, IDESC, IFLG02
FROM PRCBP LEFT OUTER JOIN IIML01 ON (BPITEM = IPROD) WHERE BPPLNAME = 'NECAD' AND '2005-02-01' BETWEEN BPEFFI AND BPEFFO;

just removing the COALESCE around BPINCTXT kept this column.

It isn't much faster 3ms but I am very confused/concerned by adding COALESCE around a column in my select why it won't use the logical. In my opinion this shouldn't affect how the query goes after the data, I am just saying if the value happens to be null change it to an empty space. A co-worker of mine just showed me how she can get it to do a table scan if you add a CAST to your select statement or change a column name by saying column AS NewName. This even makes less sense. I am using Ops Navigator to explain my sql and this is the tool that is showing me the table scans.

My concern is that this query is going to be used by a lot of users of over time and why am I taking a 3ms penatly in this situation just because I am using a function in my select.

Ops Navigator? Is that an IBM tool? Did you try the DB2 visual explain?

Also, need information on DB2 release/fixpack, OS, whether you ran extensive runstats (with distribution on key columns), cardinality of table, etc.

Table and index definitions would also be helpful...

It turns out those folks have got an iSeries - Operations Navigator is a system management tool for AS/400...

DB2/400? All bets are off.

Thanks for all the help and suggestions but I figured it out and I can't believe the solution instead of doing COALESCE(BPINCTXT,'') I had to do COALESCE(BPINCTXT,' '), I don't understand stand why an empty string would cause this but it did.

I would raise a PMR on this - or whatever is your method is of raising bug reports to IBM.

James Campbell