Executed SQL-statements in a logfile?

[Kenneth]

Hi

Is it possible to see all kinds of executed SQL-statements in a logfile? If
yes, how do I do that and how much information does it log?

Thanks, Kenneth

[Serge Rielau]

Take a look at the statement event monitor

Cheers
Serge

[Kenneth]

> Take a look at the statement event monitor

Thanks. I have now studied a little bit of the event monitor. I found out
that the DB2 statement cache stores packages and statistics for frequently
used SQL statements.

I have found a Dynamic SQL Snapshot Result:

Database name = SAMPLE
Database path = /home/smith/smith/NODE0000/SQL00001/

Number of executions = 2
Number of compilations = 1
Worst preparation time (ms) = 126
Best preparation time (ms) = 126
Rows deleted = 0
Rows inserted = 0
Rows read = 24
Rows updated = 0
Rows written = 0
Statement sorts = 0
Total execution time (sec.ms) = 0.060226
Total system cpu time (sec.ms) = 0
Total user cpu time (sec.ms) = 0
Statement text = select * from org
But does it only show the last executed SQL-statement? How do I take a
snapshot of the cache? I think it's the SQLCACHE_SNAPSHOT, but what will I
have to type in the command line?

I hope someone can come up with an answer. :-)

Thanks, Kenneth

[Serge Rielau]

Take a look at the SNAPSHOT table functions.

Cheers
Serge

[Larry E]

You can also use the DB2 Recovery Expert to do Log Analysis if that is
what you want to do.

Larry Edelstein
Kenneth wrote:

Hi

Is it possible to see all kinds of executed SQL-statements in a logfile? If
yes, how do I do that and how much information does it log?

Thanks, Kenneth

[Susanne Englert]

> > Hi

Is it possible to see all kinds of executed SQL-statements in a logfile? If
yes, how do I do that and how much information does it log?

Thanks, Kenneth

Yes. There are two tools: First is the event monitor, which can be
used to log every single statement over some period of interest. It's
nice because you can have the information written to a SQL table.
Suppose you care about connections and statements. You could do:

connect to <database>
create event monitor susanne for statements
write to table connheader (table susanne.ch),
stmt (table susanne.stmt)
buffersize 32 nonblocked;
set event monitor susanne state 1;
-- now wait for stuff to happen (queries, connections...)
-- turn off the monitor
set event monitor susanne state 0;
-- see what you got
describe table susanne.stmt;
describe table susanne.ch;
-- query these. Example:
select
(case when stmt_operation = 1 then 'PREPARE'
when stmt_operation = 2 then 'EXECUTE'
when stmt_operation = 4 then 'OPEN'
when stmt_operation = 6 then 'CLOSE'
when stmt_operation = 7 then 'DESCRIBE'
else 'OTHER' end),
substr(stmt_text,1,60),
cast(timestampdiff(1, char(stop_time-start_time))/1000000.0 as
decimal(12,6)) as duration,
cast(system_cpu_time/1000000.0 as decimal(12,4)) as sys_cpu,
cast(user_cpu_time/1000000.0 as decimal(12,4)) as user_cpu
from susanne.stmt order by start_time fetch first 25 rows only;

The other tool is the snapshot monitor. Using a "dynamic SQL
statement snapshot", you can see all the statements in the statement
cache at the time you take the snapshot. Each statement is annotated
with a count of how many times it has been executed (by any user) and
how much total resources it has consumed since you last reset the
monitor. This is a different view in that it doesn't identify
individual executions, but aggregates over all of them.
Please refer to the manual for use. The snapshot monitor also has a
nice SQL interface.

Susanne Englert
DB2 Performance
IBM Silicon Valley Lab

[Spencer]

What about Query Patroller?

su*************@gmail.com (Susanne Englert) wrote in message news:<2b**************************@posting.google. com>...

Hi

Is it possible to see all kinds of executed SQL-statements in a logfile? If
yes, how do I do that and how much information does it log?

Thanks, Kenneth

Yes. There are two tools: First is the event monitor, which can be
used to log every single statement over some period of interest. It's
nice because you can have the information written to a SQL table.
Suppose you care about connections and statements. You could do:

connect to <database>
create event monitor susanne for statements
write to table connheader (table susanne.ch),
stmt (table susanne.stmt)
buffersize 32 nonblocked;
set event monitor susanne state 1;
-- now wait for stuff to happen (queries, connections...)
-- turn off the monitor
set event monitor susanne state 0;
-- see what you got
describe table susanne.stmt;
describe table susanne.ch;
-- query these. Example:
select
(case when stmt_operation = 1 then 'PREPARE'
when stmt_operation = 2 then 'EXECUTE'
when stmt_operation = 4 then 'OPEN'
when stmt_operation = 6 then 'CLOSE'
when stmt_operation = 7 then 'DESCRIBE'
else 'OTHER' end),
substr(stmt_text,1,60),
cast(timestampdiff(1, char(stop_time-start_time))/1000000.0 as
decimal(12,6)) as duration,
cast(system_cpu_time/1000000.0 as decimal(12,4)) as sys_cpu,
cast(user_cpu_time/1000000.0 as decimal(12,4)) as user_cpu
from susanne.stmt order by start_time fetch first 25 rows only;

The other tool is the snapshot monitor. Using a "dynamic SQL
statement snapshot", you can see all the statements in the statement
cache at the time you take the snapshot. Each statement is annotated
with a count of how many times it has been executed (by any user) and
how much total resources it has consumed since you last reset the
monitor. This is a different view in that it doesn't identify
individual executions, but aggregates over all of them.
Please refer to the manual for use. The snapshot monitor also has a
nice SQL interface.

Susanne Englert
DB2 Performance
IBM Silicon Valley Lab