What is the best way to identify which statements in the package cache
that reads from a certain table? I'm not to happy with:

select ...
from sysibmadm.snapdyn_sql
where ucase(cast(stmt_text as varchar(10000))) like '%<tablename> %'

so I'm wondering if there is a better way?


/Lennart

On 22 nov, 22:18, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Don't forget the views on that table...

For the rest of your question, I don't think there's much choice.

You sound like you're trying to implement some performance course best
practices ;-)
--
Frederik Engelen

On 2010-11-23 00:30, Frederik Engelen wrote:
My guess to, I was kinda hoping that I've missed something obvious. It
would have been pretty cool if one could retrieve info from the
compilation of the stmt (such as operands and operators in the access
plan). I guess I just have to refine the where clause one way or another.

:-) We haven't found any 2.2 million dollar queries yet, but I'm pretty
sure that we earned the conference fee back.


/Lennart

Which version of DB2 are you on?
9.7 has a bunch of new and much more lightweight APIs

Cheers
Serge


--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

On 2010-11-23 13:06, Serge Rielau wrote:
9.5 and AFAIK there are no plans of upgrading in a nearby future.


/Lennart

On Nov 23, 2:40*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Theoretically, db2advis can explain all tables currently in the
package cache and you could retrieve the table names from the explain
tables...