Hi
I want to see how often individual stored procedures are being called.
Does anybody have any ideas how I could achieve this? (Running 7.2 on
Solaris)
Thanks
Tim

The first thing I can think of would be a Statement Event Monitor.
--
Larry Menard
IBM Workstation Database (DB2) Performance Team
Defender of Geese and of All Things Natural


"Tim Jacobs" <tim.jacobs@t-systems.co.uk> wrote in message
news:62f6e8bf.0307010548.36b7d4b4@posting.google.c om...
> Hi
> I want to see how often individual stored procedures are being called.
> Does anybody have any ideas how I could achieve this? (Running 7.2 on
> Solaris)
> Thanks
> Tim

Having done (or at least valiantly attempted to do) this several
times, there are a couple things you need to know about. The statement
monitor will provide you access to all of the SQL calls going into the
database. If you're using SQL stored procedures you will not see the
stored procedure name, but rather the package associated with that
name in the event monitor output. You'll have to massage the output
and correlate the pkgnames with the actual SP name to get an idea of
what's being called when.

If you're using Java stored procs as we are, you may be out of luck.
(If someone knows any different, please speak up!) Since there is no
package associated with a Java stored proc, the only thing that you'll
see in the event monitor output is the raw SQL statements executed by
the Java SPs. You'll manually have to correlate those SQL statements
back to the Java source code to get an idea of executions of the
stored procs. So far this has proved to be pretty tedious. If your SQL
is unique and readily identifiable and only called from one SP, then
this might not be so bad.

Hope this helps!

Evan


"Larry Menard" <lmenard@ca.ibm.com> wrote in message news:<bds6jt$99c$1@hanover.torolab.ibm.com>...
> The first thing I can think of would be a Statement Event Monitor.
> --
> Larry Menard
> IBM Workstation Database (DB2) Performance Team
> Defender of Geese and of All Things Natural
>
>
> "Tim Jacobs" <tim.jacobs@t-systems.co.uk> wrote in message
> news:62f6e8bf.0307010548.36b7d4b4@posting.google.c om...
> > Hi
> > I want to see how often individual stored procedures are being called.
> > Does anybody have any ideas how I could achieve this? (Running 7.2 on
> > Solaris)
> > Thanks
> > Tim

Thanks guys, that's useful.
Tim

Not helpful on v7...but for anyone interested in v8, call is a compiled
statement, so with the statement evmon you'll actually get the call
statement (complete with proc name) in the evmon output.

Tim Jacobs wrote:

> Hi
> I want to see how often individual stored procedures are being called.
> Does anybody have any ideas how I could achieve this? (Running 7.2 on
> Solaris)
> Thanks
> Tim

Sean McKeough <mckeough@nospam.ca.ibm.com> wrote in message news:<bdump5$5uk$2@hanover.torolab.ibm.com>...
> Not helpful on v7...but for anyone interested in v8, call is a compiled
> statement, so with the statement evmon you'll actually get the call
> statement (complete with proc name) in the evmon output.

Even on Java SPs?

Yep...in v7 call wasn't compled, so the call sql was never recorded by
the compiler, but now in the dynamic sql case it should be there...

Evan Smith wrote:

> Sean McKeough <mckeough@nospam.ca.ibm.com> wrote in message news:<bdump5$5uk$2@hanover.torolab.ibm.com>...
>
>>Not helpful on v7...but for anyone interested in v8, call is a compiled
>>statement, so with the statement evmon you'll actually get the call
>>statement (complete with proc name) in the evmon output.
>
>
> Even on Java SPs?