FLUSH PACKAGE CACHE DYNAMIC

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Damir
    #1

    FLUSH PACKAGE CACHE DYNAMIC

    Hello!
    I have noticed that after (sucessfully) executing the command:
    FLUSH PACKAGE CACHE DYNAMIC
    the dynamic SQL statement cache is not completely cleared (some of the
    dynamic SQL statement elements are not reset).

    For example, looking at the dynamic SQL snapshot I see a certain SQL
    statement that has been executed a number of times and has read a (big)
    number of rows.
    Then I execute the "FLUSH PACKAGE CACHE DYNAMIC" command.
    Then I execute the same SQL statement one more time.
    Now, when I issue another dynamic SQL snapshot I expect for this SQL
    statement the number of executions to be one, and the number of rows read to
    be equal to the number of rows read by *only* that last statement
    invocation.
    But this doesn't happen. Instead, the number of executions remains as before
    the FLUSH command, increased by one, and the number of rows read is
    increased by the number of rows read by the last invocation of the
    statement.

    Is this the expected result of the FLUSH command?
    If so, how can I reset the NUM_EXECUTIONS and ROWS_READ (and to that matter
    all other) elements of the dynamic SQL cache?

    Regards,
    Damir


    Tags: None
    • Patrick Finnegan
      #2
      Re: FLUSH PACKAGE CACHE DYNAMIC

      On Nov 11, 10:56 am, "Damir" <damirwil...@ya hoo.comwrote:
          Hello!
      I have noticed that after (sucessfully) executing the command:
          FLUSH PACKAGE CACHE DYNAMIC
      the dynamic SQL statement cache is not completely cleared (some of the
      dynamic SQL statement elements are not reset).
      >
      For example, looking at the dynamic SQL snapshot I see a certain SQL
      statement that has been executed a number of times and has read a (big)
      number of rows.
      Then I execute the "FLUSH PACKAGE CACHE DYNAMIC" command.
      Then I execute the same SQL statement one more time.
      Now, when I issue another dynamic SQL snapshot I expect for this SQL
      statement the number of executions to be one, and the number of rows readto
      be equal to the number of rows read by *only* that last statement
      invocation.
      But this doesn't happen. Instead, the number of executions remains as before
      the FLUSH command, increased by one, and the number of rows read is
      increased by the number of rows read by the last invocation of the
      statement.
      >
      Is this the expected result of the FLUSH command?
      If so, how can I reset the NUM_EXECUTIONS and ROWS_READ (and to that matter
      all other) elements of the dynamic SQL cache?
      >
      Regards,
          Damir


      db2 reset monitor all
      db2 connect to yourdb user youruser using password
      db2 flush package cache dynamic
      db2 connect reset

      db2 get snapshot ............... ......








        Comment

        • Damir
          #3
          Re: FLUSH PACKAGE CACHE DYNAMIC

          You are missing the "db2 connect reset" statement that I specified in
          the example.
          Hmm... supposing that I have a WebSphere Application Server that holds open
          (many) connections to the database (that keep executing the aforementioned
          queries), and I cannot reset these connections (because it is a production
          site), in that case it is impossible to reset the counters?
          (in spite of all said, I can reset - for example - the table counters (i.e.
          rows read, overflows, etc.) ... but not the dynSQL ones ...)

          Regards,
          Damir


            Comment

            Previous template Next
            Working...