Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Posts
    119

    Unanswered: Optimization classes

    Hi,
    IBM descibe various optimization classes which can be set to optimize db performance. For example:

    SET CURRENT QUERY OPTIMIZATION = 1

    will set the database to class optimization 1. However, my question is if I execute this in a java application, is this optimization class set only for the current use of the current JDBC connection, for any connection in the connection pool that the application uses or is it set for the entire database?

    Any help appreciated.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    this settings is wailed only for current connection, doesn't effect others connection in connection pool and also does not effect all applications on entire database.

    If you would like to set optimization for entire database then you must change db cfg parameter DFT_QUERYOPT like:
    db2 update db cfg for <database> using DFT_QUERYOPT 1

    Hope this helps,
    Grofaty

  3. #3
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by grofaty
    Hi,
    this settings is wailed only for current connection, doesn't effect others connection in connection pool and also does not effect all applications on entire database.

    If you would like to set optimization for entire database then you must change db cfg parameter DFT_QUERYOPT like:
    db2 update db cfg for <database> using DFT_QUERYOPT 1

    Hope this helps,
    Grofaty
    Thanks for that. I was just wondering though when you say current connection do you mean JDBC connection or database connection?

    i.e. just say I am using a connection pool which pools my JDBC connections.

    Do I have to issue SET CURRENT QUERY OPTIMIZATION = 1 once and only once for each connection in the pool

    or

    because I regularly close JDBC connections to return them to the pool, must actually issue this command before I use any connection from the pool?

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Connection pooling is irrelevant here. When you get a new connection (either pooled or not), all special registers are set to their default values. So the answer is: yes, you have to set the optimization level each time.

    p.s: Are you familiar enough with the optimization levels and the effect of changing them to reduce the optimization from 5 (default) to 1. I'm worrying that you may start "tuning" something without really understanding the impact. (I think it's fair to ask this, given the basic nature of your question.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2006
    Posts
    119
    Quote Originally Posted by stolze
    Connection pooling is irrelevant here. When you get a new connection (either pooled or not), all special registers are set to their default values. So the answer is: yes, you have to set the optimization level each time.

    p.s: Are you familiar enough with the optimization levels and the effect of changing them to reduce the optimization from 5 (default) to 1. I'm worrying that you may start "tuning" something without really understanding the impact. (I think it's fair to ask this, given the basic nature of your question.)
    Fair question. Requirement is from DBA. I'm just implementing it.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by stolze
    Connection pooling is irrelevant here. When you get a new connection (either pooled or not), all special registers are set to their default values.
    I don't think this is always the case: depending on the connection pool implementation, it may not reset all (or some) connection settings when the connection is returned to the pool.

    Still, it is a good practice to always explicitly set your database session environment when you obtain a connection, because you don't have control over what settings might be in effect at that time.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One time I had to ask the java programmer to change the optimization level for an SQL statement. Jsut to be safe I asked them to change it back to the database default level after submitting the SQL. The difference in performance was significant enough to more than justify the two exta SET statements to set and reset the optimization level.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by n_i
    I don't think this is always the case: depending on the connection pool implementation, it may not reset all (or some) connection settings when the connection is returned to the pool.

    Still, it is a good practice to always explicitly set your database session environment when you obtain a connection, because you don't have control over what settings might be in effect at that time.
    I agree. The question is where the pooling is done. If you use DB2's mechanisms, DB2 initializes connections properly. If you use on some other, possibly home-grown mechanism, you cannot rely on that.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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