In DB v7 OS390 it's possible force the use of an index? For example, in SQL Server, the next query we force to use the index IX1:

SELECT f1 FROM t1 WITH (INDEX= IX1)

Thanks in advance

You can bind the SQL statement using an optimization hint. This will require that you have the appropriate rows in the PLAN_TABLE to reflect the usable access path that you wish to use.

Well, sorry but I don't know what is exactly the PLAN_TABLE, and I suppose that it's difficult to explain in a post, isn't it?

It would probably be easier for me to have you go to the manuals and look for "OPTHINT" or "Optimization Hint". However, if you do not know what the PLAN_TABLE is, it might be helpful to learn more about EXPLAIN in DB2. In most cases, if the table statistics are up-to-date and the table has the appropriate indexes, the DB2 optimizer will make the correct choice of whether or not to use an index.

And, sometimes the technicque used to force the index usage is to use 'OPTIMIZE for n rows' .... I wouldn't recommend it unless you have a reasonable estimation for n

Cheers
sathyaram

That's true, the problem is that I don't have a good manual for DB2 (in fact, I don't have any manual of DB2). It's complicated to explain here, I'm looking for resources about DB2: books, white papers, tutorials, courses (do you know a good one of IBM?)...


Ok, if the statistics are up-to-date, the engine normally will choose the right way, but I need to run a battery of test in different scenarios (different index), so a clause that force to use one or another index to compare plans could be very useful, don't you think the same?

Craig Mullin's 'db2 developers guide' is a very good book, if you wish to purchase one ...

The manuals are here
http://www-306.ibm.com/software/data...s/v7books.html

No, I don't agree. DB2 uses a cost based optimizer that chooses the fastest access path, taking into consideration the size of the table, the indexes, the cardinality of the columns, the expected number of rows to be returned, etc. So assuming you have executed runstats properly (capturing full stats), and tell DB2 how many rows you expect to fetch when it is less than the entire result set (OPTIMIZE FOR n ROWS), then DB2 will chose the best access path 99.9% of the time.

Of course "best" means best of the available access paths depending on which index(es) you have defined, what the clustering index is, whether the table has been reorged, etc. If performance is a problem, then examine the explain and determine whether you need to change the SQL or the parameters mentioned above.

You can download most of the manuals here:
http://www-306.ibm.com/software/data...s/v7books.html
I recommend the acrobat versions, which can searched with the acrobat search function.

Ok, I try to explain my situation, though my poor english. I've got a table (clients) with 10mill rows and 60 fields. The table have a clustered index (IX1) on surname1, surname2, name and nif and different index: IX2 in name, surname1 and pk and IX3 in surname2, name and pk.

We search on this table by the typical fields: name, surname 1 and surname 2, and the resulset (paging 10 rows) must be ordered by surname 1, surname 2, name and pk. When we search on surname1 there's no problem: the engine use IX1 because is good for search coincidences and later for the order. But, when we only search for surname 2 or name, IX3 or IX2 are good for search and bad for the order (the engine must order the coincidences).

If we drop IX2 and IX3, searchs on surname2 or name will be slower but we improve the order. Ok, if surname2 or name is not so usual or not exists, IX1 will be completely scan, this is the inconvenient.

I need to confirm this ideas with facts, that's because I'm asking about force index.

Well, sorry for the sermon and thanks

Assuming your have rerorged the tables/indexes, and then executed full runstats, and then a rebind (assuming statis SQL), then I would need the following:

- Tablespace page size
- Table DDL
- Index DDL
- SQL statements used in search