Results 1 to 7 of 7

Thread: Bind or not?

  1. #1
    Join Date
    Aug 2004
    Posts
    15

    Unanswered: Bind or not?

    We have runstats performed every night on DB2 V8.1 (AIX). I was running some snapshots earlier and I noticed that there were 32,000 dynamic sql statements executed and 53,000 static sql run. The way I understand it is that the dynamic sql always gets the most recent runstat data, but the static sql will only get the most recent data if a bind is performed after the runstats. Do we need to add a step of doing a bind after the runstats to improve the run time of the 53,000 static sql statements? If so, how much will this help?

    Thanks
    Anthony

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If your stats don't significantly change or you are satisfied with the access path of the packages, then don't rebind.

    Dynamic plans may not make use of the new stats if they are still in package cache. Reuse of packages in cache depends on several factors.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    Yep!

    Marcus has it right!

    In our shop we have noticed, when talking about a new application, that during first 1/2 year runstats and rebind shoud run quite often. After that once in a year is about right. Exept when a significant changes in data material will happen (big inserts, deletes or updates).

    Cheers, Bill

  4. #4
    Join Date
    Aug 2004
    Posts
    15
    Marcus wrote:
    Dynamic plans may not make use of the new stats if they are still in package cache.

    Is there anything you can do to flush the package out of the cache so the dynamic plans pick up the new stats? Re-bind won't do this?

    Thanks
    Anthony

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    FLUSH PACKAGE CACHE DYNAMIC. See the SQL Reference for details.
    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
    Aug 2004
    Posts
    15
    Wouldn't the package cache get flushed at instance stop/start time? We stop and start our instance before our tests. If it does get flushed, we wouldn't need to do the "flush package cache dynamic", or am I wrong?

  7. #7
    Join Date
    Aug 2004
    Posts
    15
    Thanks Marcus. The FLUSH PACKAGE CACHE DYNAMIC really made a positive difference with our testing!

    Anthony

Posting Permissions

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