give the db2 a hint which index to use?

Question

moin-moin,

I have a join over some tables, and I want to give the db2-database a hint, which index i want her to use. I know, this may result in a slow query, but I have a production and a test database, and I want the same behaviour in both databases (even if in one db the amount of data is significantly different or what state the (index-)cache has.

Is this possible (and how)? I did not find anything in the online manual, which could mean, I had the wron searching criteria.

Thanks a million.

Answer 1

You can influence the optimizer via a Profile:

• http://www.ibm.com/developerworks/data/library/techarticle/dm-1202storedprocedure/index.html
• It was recently asked here: Is it possible to replace NL join with HS join in sql

However, I haven't heard about the selectivity clause, and I think you should try first this option, before create a profile. But you should do this just after having tried other options. Follow the steps as indicated in the DeveloperWorks tutorial before influence the optimizer:

• Experiment with different SQL optimization classes. The default optimization class is controlled by the DFT_QUERYOPT parameter in the database configuration file.

• Attempt to resolve any performance problems by ensuring that proper database statistics have been collected. The more detailed the statistics, the better the optimizer can perform. (See RUNSTATS in the DB2 Command Reference).

• If the poor access plan is the result of rapidly changing characteristics of the table (i.e. grows very quickly such that statistics get out of date quickly), try marking the table as VOLATILE using the ALTER TABLE command.

• Try explaining the query using literal values instead of parameter markers in your predicates. If you are getting different access plans when using parameter markers, it will help you understand the nature of the performance problem better. You may find that using literals in your application will yield a better plan (and therefore better performance) at the cost of SQL compilation overhead.

• Try using DB2’s index advisor (db2advis) to see if there are any useful indexes which you may have overlooked.

Answer 2

This is not something that is commonly done with DB2. However, you can use selectivity. It should still be around in present versions. Adding selectivity clauses to queries will affect the decisions made by the query optimizer.

Also what Gilbert Le Blanc noted above will work. You can UPDATE the syscat.tables colums and fool the DB2 to optimize the queries for non-existent amounts of data in the rows. Also the rest of your DB / DBM CFG should match (ie. the calculated disk and cpu speeds, memory usage related settings etc) because in some situations they might also matter to some degree.