Hi All,

1)Can anyone please suggest the best methods to improve SELECT and UPDATE Query Performance.
The follwoing steps have been taken,
Index Created,Runstats performed and Rebind performed.

2)Query 1 uses a set of Indexes in QA and does not use the same set of Indexes in Prod which is an exact copy of the QA.Is there a way to force the query to use the set of Indexes used in QA which gives better performance.

3)Does creating multiple containers in a single tablespace help improving data access.The tablespaces lie on the same filesystem.

Thanks in Advance,
Venkat

2) What's the difference between your test system and the production system? (There are differences - if there were none, DB2 would come up with the same access plan.)

3) How many physical devices are in the file system?

Thanks for your reply.

2) What's the difference between your test system and the production system? (There are differences - if there were none, DB2 would come up with the same access plan.)

-- There are no differences between both the systems.I am surprised why
DB2 is coming up with different access plans.

3) How many physical devices are in the file system?
There are 8 physical devices in the filesystem.Does increaing the number of containers in a tablespace help in improving the performance.

Thanks again,

2) Sorry if I have to say that, but you do have differences in your system configuration (hardware/software) and/or DB2 configuration and data. The DB2 optimizer works deterministic, so you would get the same results in the same environment and data. You should investigate why you believe that there are no differences, find the differences and figure out if they may result in different access plans. If you can't find any difference, search again...

3) Are those physical devices real physical discs or just partitions on the same disc? If you have multiple discs, it makes sense to have multiple containers on the file system because you can benefit from parallel I/O at the DB2 level (not only at OS level).

That also depends on how your physical volume is organized. For example, if the volume already stripes over physical volumes and you have mostly sequential access, then multiple containers may not give you much advantage because you already have multi-page reads (due to prefetching), and the OS uses parallel I/O.

Thanks for making it clear about the tablespace containers.I finally found my answer about the multiple containers.