Hi experts! Riddle me this.....

My system is very, very small. It only has 17 tables and about 20 users.

I have a query that gets executed many times throughout the day. 98% of the time the query uses an index that I created to specifically make the query more efficient. The other 2% of the time the query uses a tablescan. The SQL is always executed dynamically.

My developer recently added FOR FETCH ONLY WITH UR to the query.

My system is such that it would be quite possible for one user to be updating a particular row while a different user is viewing that same row.

My suspicion is that because we are doing a dirty read with this query, if another user happens to be updating that row at the same time, the optimizer says "Hey, I need to update the index as well, so I'm not going to read the table using the index."

I'm new at this DBA stuff. Do you think that my suspicion is correct?

I would doubt that. If the query has literals in the predicate (WHERE clause) DB2 can check the value against the statistics to estimate how many of that value exist in the database and adjust the access plan accordingly. If you don't want DB2 to do that, then get rid of the distribution stats for the columns for the table that are stored in the catalog (and don't ask for distribution stats when you do runstats).

However, there is good chance that when DB2 is doing a table scan, that is the most efficient access path for that query (assuming you have accurate stats).

Hi Marcus. I do have literals in the WHERE clause and my stats should be accurate.

I don't know what "distribution stats" are. My RUNSTATS looks like this:

RUNSTATS TABLESPACE LIST PRDLIST3
TABLE (ALL)
INDEX (ALL)
SHRLEVEL CHANGE
REPORT YES
UPDATE ALL

Anyhow, off to read about distribution statistics now.

After reading about distribution statistics, it seems to me that it is best to collect them. (I'm still trying to figure out if we're collecting them or not.) However, I don't understand why, when executing the exact same query, just a few hours apart, one execution would use the index and one execution would not.

I didn't realize until now that you are using DB2 z/OS. Also, I didn't realize it was the exact same query (with exact same literals) each time. So I am not sure about why that is happening.

is this a dynamic query or something that is bound to the DB? How are you determining that it is not using the index, are you performing an explain or looking at some monitor?
Dave

This is a dynamic query, Dave. I am running an explain that tells me if the query uses any indexes or if it does a tablescan.

You don't mention which version of DB2 you are running or platform, but based off the runstats that you showed above, we determined you are on Z/OS. I would suggest you try collecting freqval values for your indexes. Also, You may want to ask your question on the DB2-L listserv on idug.org as you may get an accurate explanation from Pat Bossman or Terry Purcell over there.
I can't think of any reason the exact same query with the exact same literals being supplied would give you different access paths, unless runstats had been run in between the times you explained the query. If, however, it were the same query with different literals being used it could be explained that you have uneven distribution of values in that column(skewed data). For instance, 98 - 99% of the time a flag has a value of N and the remaining rows have a value of Y. The index could be chosen for the rows with a value of Y, but the optimizer would opt for a tablespace scan if the SQL was looking for the value N.
Dave Nance