The DB2 optimizer is a very complex piece of code. There are *many*
things that can influence her decision to use or not use an index.
Here a few for example:
> if there are not many rows in the table, the optimizer might choose to
just read the data instead of mucking around with an index.
> if using the index will result in reading most of the rows anyway, she
might decide to just read the data
> if the index cannot be used directly, the optimizer might choose to skip
the index
> use of functions in the where clause
> enough space for the RID pool

The point here is that if you have done RUNSTATS and BIND (after creating
the index),
and you have enough memory allocated properly (zpARMS),
then you can generally (almost always) trust the OPTIMIZER to make the
right choice.

Why do you want to force DB2 to use the index? Do you think you know
something that the optimizer doesn't?

There are many, many factors that the optimizer uses when she makes her
decisions besides just the number of rows; she also considers size of
machine, buffer pools, processors dedicated or available, and on and on.
That is one reason that the acess path chosen in test may well not match
the access path chosen in production.

Actually, the Runstats needs to be run again after the index was created otherwise dynamic SQL will not know about it's existence. If the SQL is static, meaning is part of a package/ collection / plan, the packages need to be rebound.

What I try to convey (here and in other places) is that DB2 (especially on z) is not a trivial piece of software. It is vast; it has many different components; there is a lot to learn. Now, I've been a DB2 for MVS & OS/390 & z/OS for 25 years. And I've grown up with DB2. Part of what I try to pass on is that there isn't always a short answer to this type of question. (Well, maybe there is a short answer but it most likely is wrong ;-0 )

I wish the people I work with appreciated my answers like you do. (They usually seem to want the short (and wrong) answers). That's one reason I won't miss them too much when I leave here (which may be soon, sooner even than I had planned. But that is another story.)

DB2 optimizer has calculated that you will be returning a lot of data hence the index is of no use. You can try Optimize for 1 row if you are sure you will nor be returning a lot of data or else try REOPT VARS if you will be returning a lot of data sometimes and other times more. Good Luck!

In all honesty, I've been watching this thread for a while and hoping things get better - not a snowflake chance in? Therefore being a scholar of Terry Purcell and Bonnie Baker, I will have to side with the optimizer and strongly disagree with you. I'll give my reasons why this predicate (FINAL_YEAR_DATE > CURRENT DATE - 15 DAYS ) is not indexable. Please bear with me for a moment. I'll also make recommendations on how to make it sargable and indexable. Yes - this query can be optimized for performance.

The optimizer per default operates on a set of cost base rules. Contrary to popular beliefs, the optimizer is deterministic - meaning it is always predictive. Yes - the optimizer will even allow you to provide guidance on choice of its access paths - refer to as HINT. Therefore this notion that the db2 optimizer is some sort of magical 'black box' is an embellishment that has been passed down from one DBA to another - meaning its far from the truth.

The result set of every query is obtain via a filtering factor approach. The filtering factor of every predicate in a query is evaluated and based on the number of records they can return. Ones that the optimizer cannot determine number of records return are assigned large filtering factor. The aggregate value of predicates filtering factors determines the its choice of access path for that query. Yes -- I know there are several thoughts in motion -- so bear with me as I attempt to make myself brief and comprehendible. First -- the optimizer will not be able to determine number of records return if the table or index has no STATS. Therefore RUNSTAT is always required. Next - assuming STATS are available, the optimizer may choose not to use the index either because column in the predicate has a poor CARDINALTY or the predicate is not a stage-1 predicate. Finally - what is stage-1 and stage-2 and which predicates are stage-1 or stage-2 predicates? There's a published db2 documentation that describes stage-1 and stage-2 predicates - I'd encourage you to read it.

Now, to answer the question of why this query (FINAL_YEAR_DATE > CURRENT DATE - 15 DAYS ) is not indexable. Simply because of the "( - 15 DAYS)" if you remove the "( -15 DAYS)" db2 will be able to resolve in stage-1 and use the index. All stage-1 predicates are indexable.

Here's the description of the query:
The query is a non-column expression that contains some form of arithmetic operator i.e. ( -,+,*,/) . Such queries are not INDEXable.

Recommendation on how to make db2 use the index:
Resolve the arithmetic operation outside the query see example below

I ran test in V9 NFM , table with 750K rows
There is an index (non cluster) on timestamp
And the DB2 choose matching index scan!
So the issue is not the optimizer but something else in your application

My apologies - I unintentionally left out the (-15 days) and also my "SELECT INTO" syntax was incorrect.
Here is the final solution.

SELECT (CURRENT DATE - 15 DAYS )INTO :WS_VAR1 FROM SYSIBM.SYSDUMMY1;

SELECT FINAL_YEAR_DATE FROM TABLE_A
WHERE FINAL_YEAR_DATE = :WS_VAR1;

I agree with you about stage 1. The problem is stage would skip predicates that are complex or require some form of arithmetic computations. You've guessed it -- Stage 1 would "pass the buck" to stage 2. Stage 2 uses no index. It has no need for indexing because It would leverage sequential pre-fetching to go get data in massive bulk. The optimizer is fun to watch -- It never ceases to amaze me.

I agree with you the optimizer is fine and matching Index scan is not a bad thing. If the column cardinality has a problem - then try composite key. Matching Index simply means that because the index is not unique, the leaf pages were scanned in search of values presented in the where clause. Value that was found in your where clause did not directly point to a leaf-page of the index therefore it scanned the index tree. Db2 will do this whenever a column has a poor cardinality ( number of unique values) . db2 will scan the index from the root to the leaf page and then sequentially examine page-by-page until all matching values as in this case greater-than values have been retrieved.

There are two types on index scanning namely: matching index and non-matching index. If db2 does matching-index on a none primary key - be happy because it could've been worst. For a non-matching index scan, db2 will read the entire index sequentially.

Long term solution:
Each entity must have a unique identity. Data in each entity must be retrieved via its unique identity. Db2 performs its best when we adhere to some of these trivial relational rules.

All things considered, one would have to agree that db2/z is by far more technically advanced than its peers. Take for instance the ability to create index on expressions. You're essentially creating an index on abstract data - meaning data that is not actually in the table. For me that's over the edge - taking it to another level. Yet it can do it.

I have a need to do this for a data column in order to get indexing to
take place. It works extremely well and something we had waited on a long
time. it boosted performance a lot and we have seen no problems in
updates/ inserts or the like with this index on expression on the table.

For us, we've been anxiously waiting for Temporal Data, a neat feature available only in db2/z V10. This Temporary Data feature will enable our warehousing architecture and get rid of all our history tables. In all honesty, for searching far and wide, no other DBMS has similar offering. Some of our ambitious programmers have already begun coding Temporal Data, with the anticipation of us migrating to V10 this fall. Mind you, we're still on V9 with the hope of migrating to V10 this fall. db2/ is a breed apart. Give this product a prize.