I am new to DB2. I want to use db2 optimizer to optimize the queries. Where can I install it from ? Can i access it from control center ?

DB2 Optimizer is part and parcel of DB2 .. It is installed when installing the software.
The EXPLAIN facility explains what the optimizer will do with a query.

You can access the explain facility from the control centre or the command editor.

HTH

Sathyaram

Thanks for the reply. So if i want to optimize the queries, what tool should I use in DB2.
Will db2 explain point to what improvements can be made to the query ?

The DB2 Explain will not tell you how to change a query to make it better. In most cases changing the query is not necessary unless the person who wrote the query has a very deviant mind (but I will admit there are some of those people out there writing SQL). DB2 will automatically optimize and "re-write" the query during runtime optimization and compilation. You can see the query re-write using the explain.

But you can improve query performance by changing the indexes, and the DB2 Index Adviser can help with that. Check the Command Reference manual.

There are some 3rd party tools that may help with re-writing a query, but I don't know if they do any good or what DB2 does automatically.

@ lksimbha, use DB2 Command Editor to get info about Visual Explain.

@ Marcus_A, I have seen 100s of badly written SQLs. Many users don't even join tables using full primary key columns. More complex is SQL more is room to improve badly written SQL.

[QUOTE=grofaty@ Marcus_A, I have seen 100s of badly written SQLs. Many users don't even join tables using full primary key columns. More complex is SQL more is room to improve badly written SQL.[/QUOTE]
I don't see how any product can fix an SQL statement that is logically incorrect. I was assuming that the SQL returns the correct answer and just needs to run faster. I agree that some SQL is too complex, the result of deviant minds.

Thanks all for your reply. So if i understand it right, i can use visual explain to see if the sql's are being processed and use db2 index adviser to help with indexes and improve performance.
So, there is no parameters that need to be set in the db2 optimizer to improve performance ? Is that right ?

You don't need to set any parameters "in" the optimizer ... But, there are a number of dbm , db parameters, etc that influence the optimizer decisions. eg. bufferpool size, tablespace layout, querty optimization level, parallelism etc.

Cheers
Sathyaram

When you run the explain, you will see the access path for the entire query and for each step and the "relative cost" of the query as measured in timerons. Once you start working with it you will get feel for what is acceptable or unacceptable for a relative cost.

Once you have identified problem queries, the most frequent solution would be to look at the indexes, but in some cases modifications of the SQL might help also.

Doing reorgs and runstats can also help the performance of SQL statements.

There is a parameter for the database which you can set called the default optimization level. The default is set to 5, but you could change it for the database if you wanted to, but I would recommend that you keep it at 5 until you become familiar with DB2.

If you have a data warehouse database with a lot of complex queries, you might consider changing the default optimization level to 7, but do not do this if you have any OLTP processing on that database.

Can you point me to any documentation on the dbm and db config parameters and how they influence the performance.

Thanks Marcus. I will try using the explain first. So the higher the number the better the optimization.

Not necessarily. The higher the number the more time that DB2 spends trying to optimize the SQL. The optimization time is part of the total response time of the SQL.

Go to the DB2 Information Centre for your platform and Version and search for database configuration and database manager configuration .

Here I am assuming you are using DB2 LUW.