Results 1 to 13 of 13

Thread: Db2 Optimizer

  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: Db2 Optimizer

    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 ?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2009
    Posts
    6

    DB2 Optimizer

    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 ?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    @ 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.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    [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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Feb 2009
    Posts
    6
    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 ?

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Feb 2009
    Posts
    6
    Can you point me to any documentation on the dbm and db config parameters and how they influence the performance.

  11. #11
    Join Date
    Feb 2009
    Posts
    6
    Thanks Marcus. I will try using the explain first. So the higher the number the better the optimization.

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by lksimbha
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •