DB2 equivalent to Oracle:Trace or SQL Profiler?

[GDCII]

Does anyone know what the DB2 equivalent is for Oracle:Trace or SQl Server
Profiler ?

Thanks
Gary

[Blair Adamache]

What do they do? there is a db2trc command, but it's for problem
determination. Performance data can be gathered with the snaphot and
event monitors. DB2 v8 also ships with a memory visualizer. DB2 Query
Patroller can trap SQL from users (to schedule it later, or just if you
want to know what ad hoc SQL is sent).

GDCII wrote:

Does anyone know what the DB2 equivalent is for Oracle:Trace or SQl Server
Profiler ?

Thanks
Gary

[Serge Rielau]

Gary,

Take a look at the statement event monitor and tell me whether that's
what you are looking for. If you need to profile SQL Procedures I can help.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[dba2adm]

In oracle, one can set sql_trace for a particular session identified
by sid. There is option flag to show the bind variables/values, waits
etc. Is there any mechanism of doing that using DB2 selectively for an
applid or agentid?

Thanks,
DA.

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c0**********@hanover.torolab.ibm.com>...

Gary,

Take a look at the statement event monitor and tell me whether that's
what you are looking for. If you need to profile SQL Procedures I can help.

Cheers
Serge

[Serge Rielau]

Take a look at event monitors.
E.g the statement event monitor. It won't tell you the bind values, but
it will tell you all kinds of other interesting stuff such as CPU time,
I/O, sorts, etc...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Erik Hendrix]

Hey Serge,

I think a nice addition to DB2 would be the capabaility to do some kind of
tracing on a statement and then have it show like a explain plan.
But this time instead with estimated values, the true values.

We sometimes noticed that the explain plan and estimates there is not what
the we have in actuality. By having functionality like this
one could then look at the explain plan with true values to determine what
one might need to do to help the optimizer choose a better plan or where
something else might need to be done to further optimize it.

Thanks.

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:c1**********@hanover.torolab.ibm.com...

Take a look at event monitors.
E.g the statement event monitor. It won't tell you the bind values, but
it will tell you all kinds of other interesting stuff such as CPU time,
I/O, sorts, etc...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Erik,

Indeed. Such a facility is part of the LEO project (after all a learning
optimizer must do the bean-counting before it can learn).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[dba2adm]

Thanks Serge,

The information is indeed very helpful. I am able to set the event for
a particular applid and trace the stements issued.

Will there be an enhancement (or is it already there) to show the path
the optimizer really took executing the statements. (I am looking for
something similar to sql_trace, "set events 10046 .., level n" in
oracle).

Note: I can extract the SQLs from dbevmon output and do the explain.
But this doesn't gaurantee that the plan generated when doing explain
is same as the one while executing the dynamic statement (something
might have changed by then).

----------------------------------------------------------------------------

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c1**********@hanover.torolab.ibm.com>...

Erik,

Indeed. Such a facility is part of the LEO project (after all a learning
optimizer must do the bean-counting before it can learn).

Cheers
Serge

[Erik Hendrix]

Hey Serge,

Great, so when will this go into a version? :-)

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:c1**********@hanover.torolab.ibm.com...

Erik,

Indeed. Such a facility is part of the LEO project (after all a learning
optimizer must do the bean-counting before it can learn).

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

If you monitored a dynamic statement you get its text delivered in the
event monitor. That can be passed into either db2expln or, via EXPLAIN
PLAN FOR to db2exfmt. As long as you haven't run runstats in between you
should be fine.

If you want to capture dynamic sql statements as they get executed take
a look at:
SET CURRENT EXPLAIN MODE = YES

You can then use db2exfmt to get the plans and they will be guranteed to
be the ones executed.

Cheers
Serge

PS: I can't speak for others, but my knowledge of Oracle is limited.
Best explain the feature you are referring to. While I enjoy perusing
competitor's docs I often can't afford the time :-(

PPS: To answer Erik's question: I don't know. And if I knew I could only
tell you it were in Stinger.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab