Index in Qtemp

[sxr62guy]

Hi, I would like to know if an index created in qtemp is shared by other jobs? My understanding is it wouldn't but couldn't find any literature to support this.

And, if I execute the create index statement on a huge file should I expect the statement to run longer? Or does it run behind the scene and release my interactive session. We created some indexes as advised by DB2 but didn't seem to have any effect on the query. The index showed up in open files but no IO operations there. After few minutes we reran the query and it ran faster this time. Index advisor did mention that the estimated index creation time is 10 minutes. Does that mean that the first time the query was run the index was not used by DB2 as it is still being built?

Is there any no cost tool that I can use for performance analysis. No visual explain.

TIA

Regards

[B.Hauser]

Creating any index in any library is time consuming. There is no difference whether an index is created in any permanent library or the QTEMP library or whether the SQL Optimizer decides to create an MTI (maintained temporary index).

You need to know how the optimizer works. In this way you should read the following white paper first:

Currently the strategic products for analyzing SQL Performance are located within Client Access.
SQL Plan Cache
SQL Monitors
But all those tools link to Visual Explain for detailed analysis.
Not sure what's wrong with Visual Explain.

The first time a query runs an access plan is built and the ODP (Open Data Path) opened. Opening an ODP is the most time consuming process in query optimization. After the first execution the ODP is closed.
With the second run the access plan is double checked and the ODP opened again. After the second execution, if the ODP is reusable it stays open, i.e. with the next run only the data in the ODP are updated.

There are only 2 ways to affect query performance:
1. SQL Syntax: Depending on the syntax used (for complex) queries, the optimizer can use an index or not
2. Indexes (Binary Radix Tree Indexes and Encoded Vector Indexes)

Birgitta