Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007

    Unanswered: Flushing Dynamic sql cache

    I want to flush Dynamic Sql CACHE completely in db2 9.1.2
    I used command "db2 FLUSH PACKAGE CACHE DYNAMIC".
    But it lefts some queries in cache. These are fixed queries.

    It creats problem for me to find out top 10 queries with high hit ratio, using table function snap_get_dyn_sql_v91().
    These queries come in top 10 most of the time.

    Is there any way to flush dynamic sql cache completely.

    Lekh Raj

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    My guess is why the package cache did not flush totally is that those statements were active at the time of the flush command. Things you can do to try to fix this situation:

    A) quiesce the db/instance, then flush when db is consistant
    B) db2stop and db2start

    I am not sure about A, but B is guaranteed to work.


  3. #3
    Join Date
    May 2003
    Try doing:
    db2 reset monitor all

    Then only select the SQL statements with number of executions greater than zero.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2007
    Thanks ARWinner and Marcus_A for reply.
    I cannot restart db/instance everytime. "reset monitor all" sounds good, but I want to reset dynamic sql counters only, not all.
    Will check if "reset monitor all" doesn't affect other counters.

    Lekh Raj

Posting Permissions

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