Hello All,

1) Need to know general techniques for DB2 SQL query optimization?

2) What are the tools available in the IBM Mainframes OS/390 that supports in query optimization?

Thanks.

You have to use indexes.
You can check how your query works, using explain or visual explain.
Some tools available in Platinum, also.

Kara

You will have to use vendor products such as CA CDB2 tools or BMC PMT for query optimization in z/OS applications. If you don't want any of these vendor products, you may have create plan tables and execute explain statements through SPUFI or QMF.

First, joins are expensive. If we take two tables with 3 collumns each and perform a join, the system must generate a table with 9 rows and then rule out 6 rows. If we want to optimize, and it is a pain, then we select from the two tables first. As an example, if we select from the two tables first so that the two tables are 2 rows each, the system then generates 4 rows and filters out 2. This example is clear though. Instead of generating 9 rows, the system generated 4. Clearly more efficient. However, this gets ugly quick. Also, refrain from wildcards in queries where possible. This results in scanning rather than indexing.

jkuyken, I have to disagree with most everything you posted. Joins are not expensive. You can write inefficient joins just like you can write any inefficient SQL. It is more expensive to open 2 cursors and 'join' the rows in an application.

If you have values that will filter out rows, DB2 can do the filtering before doing the join.

In general, I would first try to:

Eliminate any table space scans (on tables with significant number of rows).
Eliminate or reduce any sorting.
Encourage index usage
(This could include adding one or more indexes, adding the first key index value to the query that is only using the second value of a composite index, determine if index keys can be in a different order, etc.)

Irrespective of table size, this is a good thing. If the small tables in question are accessed frequently, then becomes important.

Stealth DBA, the following would seem to support what you are saying:

When and why are database joins expensive? - Stack Overflow

But in practice, on our system:

is much faster than:

I imagine table structure and system setup greatly influence this, and it is not supposed to be true, but when I look at actual query execution times it seems that I can manually optimize better than whatever the standard optimization plan that occurs in our system. Perhaps the system is not setup properly. We all use what we have and it takes trying both to know. You should be right.

In truth I rarely bother with the efforts of manual tuning because the layered selects is ugly and unmanageable when changes are needed. Do they run faster? On my system yes.

This doesn't conflict with what I have said. And I would not advocate denormalizing the tables into one table. I would rather write clear code, take the performance hit and run the long querys on a schedule after typical business hours, but the truth is that an intelligent programmer can sometimes make better optimization decisions based on his data and system than a standard predetermined optimization routine performed by the system.

The following site suggests precisely what I am mentioning reducing the set size on which work is done:
http://www.sql-server-performance.co..._joins_p1.aspx

http://www.devx.com/dbzone/Article/26995/1954 states that "[f]or complex queries involving joins between eight different tables, the optimizer could spend as much as 30 minutes to find an effective execution path before the server actually executes the query." This may mean that my tuned query is not really any quicker, but it executes 30 minutes faster because the effective execution path is immediately clear.

sathyaram_s, I should have been more specific. What I was trying to indicate is that for tiny code tables with all the rows on 1 page, you will have a table space scan that does not need to be eliminated.

jkuyken, interesting example. Most of my experience is with the 'old sytle' join with the join predicate in the Where clause.

In your example, this might work as good as your 2 nested tables:
By moving the extra filter predicates out of the Where clause and putting with the ON clause, DB2 should be able to filter while joining instead of completely joining the tables first and then filtering the rows.

Also, to rid your self of the high prepare times you could look at going with SQLJ or moving your SQL into a stored procedure. In this way your statements are bound to the database and access path is preselected. I have seen many queries that are joining up to 40 tables and they run subsecond. As to your issues with the queries above I am really suprised that the optimizer did not rewrite them to where they were pretty much the same, you should be taking a look at your explain to see why the difference in execution times.
Dave