Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    canada
    Posts
    230

    Unanswered: with DFT_QUERYOPT = 1 and 5, do we have two different accessplan?

    Hi All,
    I am using db2v7.2 aix 5.2
    I compiled an application with DFT_QUERYOPT=1 and transfer .bnd file to production that has been set up with DFT_QUERYOPT = 5 and bound it, I want to know the access plan is regarding to DFT_QUERYOPT = 1 or DFT_QUERYOPT = 5? please advise

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: with DFT_QUERYOPT = 1 and 5, do we have two different accessplan?

    Originally posted by M_RAS
    Hi All,
    I am using db2v7.2 aix 5.2
    I compiled an application with DFT_QUERYOPT=1 and transfer .bnd file to production that has been set up with DFT_QUERYOPT = 5 and bound it, I want to know the access plan is regarding to DFT_QUERYOPT = 1 or DFT_QUERYOPT = 5? please advise

    Thanks
    I assume the access plan will be that of the instance against which you run the BIND command. I think you can even specify it when you run your BIND:

    BIND ... QUERYOPT ...

  3. #3
    Join Date
    Feb 2003
    Posts
    20

    Re: with DFT_QUERYOPT = 1 and 5, do we have two different accessplan?

    If you bind the application again in your new environment, it will use the optimization class specified in the BIND command.

    If the optimization class is not specified in BIND, then the value of DFT_QUERYOPT db config parameter will be used. You can see the QUERYOPT column in syscat.packages to determine the optimization class used for the package.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you have static SQL, you should probably use at least optimization level 5. Even if you have dynamic SQL I would probably use level 2 or 3 at a minimum, especially if the statements are frequently repeated and are stored in dynamic package cache.

    The value of dft_queryopt is used unless the SET CURRENT QUERY OPTIMIZATION statement or the QUERYOPT option on the bind command is used.
    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
    Sep 2003
    Location
    canada
    Posts
    230
    Originally posted by Marcus_A
    If you have static SQL, you should probably use at least optimization level 5. Even if you have dynamic SQL I would probably use level 2 or 3 at a minimum, especially if the statements are frequently repeated and are stored in dynamic package cache.

    The value of dft_queryopt is used unless the SET CURRENT QUERY OPTIMIZATION statement or the QUERYOPT option on the bind command is used.

    THANK YOU ALL for your help.

Posting Permissions

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