Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    18

    Unanswered: How to set the optimisation level for a single query in one statement ?

    Hi .

    I want to know how to set optimization level for any query in a single statement. I mean say I have query " select * from A", I want that for this query optimization setting should be 3. One way to do so is execute "set optimization level = 3 for current query " and execute that as a statement before it. But I want to do so in a single statement .(so that it can be automated)...what is the syntax for that?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    To my knowledge there is no single command to do what you want.

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    kunal, if you have a need for automating try thinking into changing db cfg parameter for all statements. The level 3 is better for OLTP applications. The default is 5. So lowering this parameter on db cfg level can eliminate the need of any optimization level by statement.

  4. #4
    Join Date
    May 2006
    Posts
    18
    Hi, The reason why I dont want to change my default setting to 3 is coz ours is basically a data warehouse application with 80% data warehouse OLAP queries which works fine but there are few OLTP queries fired for which Db2 gives good performance only if we lower OPT level..Thats why I am looking for a single statement?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You want to look at the following SQL command:

    SET CURRENT QUERY OPTIMIZATION

    I am not sure if this applies globably or only to the session that issues it.

    BTW, in my tests of suing DB2 with the TCP-H benchmarks, some queries run faster with level 7.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Marcus,
    It only applies to the session. the SET DB CFG FOR bdalias using DBF_QUERYOPT sets it globally. The original post stated that he was looking for a way around using SET CURRENT QUERY OPTIMIZATION.

    Andy

Posting Permissions

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