| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
08-25-10, 14:41
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
|
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 ?
Quote:
|
db2 flush package cache dynamic
|
and
Quote:
|
db2 select substr(stmt_text,1,800) from sysibmadm where ucase(varchar(stmt_text)) like '% CLIENT %'
|
Regards
DBFinder
|
|
08-25-10, 15:09
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
You cannot flush statements that belong to active connections, I think.
|
|
08-25-10, 15:50
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
|
|
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
|
|
08-25-10, 15:51
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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.
|
|
08-25-10, 16:18
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
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
|
|
08-25-10, 16:20
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
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
|
|
08-25-10, 16:45
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
|
|
08-25-10, 16:49
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
I am on DB2V9.5.5 on Win 2k3
|
|
08-25-10, 16:57
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
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
|
|
08-25-10, 19:14
|
|
∞∞∞∞∞∞
|
|
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
|
|
Quote:
Originally Posted by DBFinder
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.
|
|
08-25-10, 19:15
|
|
∞∞∞∞∞∞
|
|
Join Date: Aug 2008
Location: Toronto, Canada
Posts: 1,816
|
|
And you may want to save the DDL for this table as well
|
|
08-25-10, 19:30
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
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
|
|
08-25-10, 19:31
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
Quote:
Originally Posted by ARWinner
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
|
|
08-25-10, 20:27
|
|
Registered User
|
|
Join Date: Sep 2008
Location: Toronto,Canada
Posts: 606
|
|
Quote:
Originally Posted by ARWinner
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Flush Package Cache Dynamic - Does not seem to work
