Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Flush Package Cache Dynamic - Does not seem to work

    Hi All,

    I was trying to flush package cache to purge statements in it. To see if one particular is seen back tomorrow.

    But out of 3 statement related to the one table, two were gone whereas the last one did stay.

    Why, and is it podssible to to accomplish this.

    I am doing following. Did I do something wrong ?

    db2 flush package cache dynamic
    and

    db2 select substr(stmt_text,1,800) from sysibmadm where ucase(varchar(stmt_text)) like '% CLIENT %'

    Regards

    DBFinder

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You cannot flush statements that belong to active connections, I think.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I have noticed that after flushing package cache, some statements still show up in a Snapshot for Dynamic SQL. However, that does not mean the actual package is still valid, or that it will not require a fresh compile the next time it is executed.
    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
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Straight from the manual:

    Code:
    Any cached dynamic SQL statement currently in use will be allowed to continue to exist in the package cache until it is no longer needed by the its current user; the next new user of the same statement will force an implicit prepare of the statement by DB2, and the new user will execute the new version of the cached dynamic SQL statement.

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    But,
    I had double checked this before posting. I didnot find any connection related to the statement.

    I had this issue long ago, asked on this forum, same replies as now. Ther might me some reason, because in both databases I wanted remove all statements related to CLIENT table to check back tomorrow.

    Still curious to find why. Our databases use connection pooling. Maybe something like connections or connection pool.

    Regards

    DBFinder

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    We at our company do believe that CLIENT table is not used any more. This was to double check before we drop this table. To find if any application refers to this table or not.

    Any other way to find this ??

    Regards

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are on V9.7 (latest fixpack), you can look at syscat.tables.lastused. There are some caveats though. See the manual:

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    Andy

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    I am on DB2V9.5.5 on Win 2k3

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can use the sysibmadm.snaptab to help determine it. You will need to turn on the tables monitor switch and then wait for a while to see what tables are being used.

    Andy

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by DBFinder View Post
    We at our company do believe that CLIENT table is not used any more. This was to double check before we drop this table. To find if any application refers to this table or not.

    Any other way to find this ??

    Regards

    Rename this table and then wait for a week or so and see if an appl team starts complaining. Also, unload the data just in case. I once dropped a table that I thought no one was using and then had to recover it a few months later with no backup available.

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    And you may want to save the DDL for this table as well

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Alright Bella,
    I will make sure to follow these steps. Currently, my mamager has asked me to check, I found 3 Sqls using table. These were the sqls that I wanted to erase from dynamic sql cache.

    I just noticed that we have table monitoring switch is on. So as ARWinner said I may be able to use snaptab view.

    Thanks

    DBFinder

  13. #13
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by ARWinner View Post
    You can use the sysibmadm.snaptab to help determine it. You will need to turn on the tables monitor switch and then wait for a while to see what tables are being used.

    Andy

    Good thing, I never thought of. Actually I did not believe that I have table monitoring on.

    I will try this.
    Thanks

    DBFinder

  14. #14
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by ARWinner View Post
    You can use the sysibmadm.snaptab to help determine it. You will need to turn on the tables monitor switch and then wait for a while to see what tables are being used.

    Andy
    sysibmadm.snaptab provides us rows_read and rows_written for a table.

    regards

Posting Permissions

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