Tracing agent execution and activity

[Michel Esber]

Environment:

DB2 v8 LUW FP 15 running on Linux.

For some reason that I can´t explain, a simple insert statement on a
table may run very quickly, or may take forever (20-30mins) to finish.
There is a trigger (AFTER INSERT for each row) on this table.

I have studied the execution plan, and it seems normal to me. The
total query cost is below 2000. There are no huge tablescans as well.
Why does the same code have different and random execution times ?
This can be very fast or VERY slow.

I tried to look at db2bp to understand what exactly this agent is
doing, but either I did not find any appropriate switches for this
problem, or they do not exist.

Is there any tool that will let me trace agent activity ? I guess I
need something beyond the output of event monitors or statement
snapshots.

Thanks in advance.

[Michel Esber]

Is there any counter that will tell me how many lines got inserted/
updated on my previous UOW ?

I know I can code my application to log this info, but is there any
snapshot or table function for that info?

Thanks,

Environment:

DB2 v8 LUW FP 15 running on Linux.

For some reason that I can´t explain, a simple insert statement on a
table may run very quickly, or may take forever (20-30mins) to finish.
There is a trigger (AFTER INSERT for each row) on this table.

I have studied the execution plan, and it seems normal to me. The
total query cost is below 2000. There are no huge tablescans as well.
Why does the same code have different and random execution times ?
This can be very fast or VERY slow.

I tried to look at db2bp to understand what exactly this agent is
doing, but either I did not find any appropriate switches for this
problem, or they do not exist.

Is there any tool that will let me trace agent activity ? I guess I
need something beyond the output of event monitors or statement
snapshots.

Thanks in advance.

[netzorro]

i think you should look for locks
"Michel Esber" <mi****@us.auto matos.comwrote in message
news:8c******** *************** ***********@u10 g2000prn.google groups.com...
Environment:

DB2 v8 LUW FP 15 running on Linux.

For some reason that I can´t explain, a simple insert statement on a
table may run very quickly, or may take forever (20-30mins) to finish.
There is a trigger (AFTER INSERT for each row) on this table.

I have studied the execution plan, and it seems normal to me. The
total query cost is below 2000. There are no huge tablescans as well.
Why does the same code have different and random execution times ?
This can be very fast or VERY slow.

I tried to look at db2bp to understand what exactly this agent is
doing, but either I did not find any appropriate switches for this
problem, or they do not exist.

Is there any tool that will let me trace agent activity ? I guess I
need something beyond the output of event monitors or statement
snapshots.

Thanks in advance.

[Pierre StJ]

On Apr 7, 3:27*pm, Michel Esber <[e-mail] matos.comwrote:

Environment:

DB2 v8 LUW FP 15 running on Linux.

For some reason that I can´t explain, a simple insert statement on a
table may run very quickly, or may take forever (20-30mins) to finish.
There is a trigger (AFTER INSERT for each row) on this table.

I have studied the execution plan, and it seems normal to me. The
total query cost is below 2000. There are no huge tablescans as well.
Why does the same code have different and random execution times ?
This can be very fast or VERY slow.

I tried to look at db2bp to understand what exactly this agent is
doing, but either I did not find any appropriate switches for this
problem, or they do not exist.

Is there any tool that will let me trace agent activity ? I guess I
need something beyond the output of event monitors or statement
snapshots.

Thanks in advance.

There are tools.
The Activity Monitor GUI will allow you to print a report showing the
the top aplls. running that have the most rows handled or take the
most cpu time.
There are snapshot table functions. Searh the Info. Center for
SNAPSHOT_TABLE.
When you research the ROWS_WRITTEN element, you'll see that you can
obtain that value for one or more tables with either the snapshot
command or from an event monitor.
Regards, Pierre.

[Pierre StJ]

On Apr 13, 10:53*pm, "netzorro" <netzo...@blueb ottle.comwrote:

i think you should look for locks

"Michel Esber" <[e-mail] matos.comwrote in message

news:8c******** *************** ***********@u10 g2000prn.google groups.com...
Environment:

DB2 v8 LUW FP 15 running on Linux.

For some reason that I can´t explain, a simple insert statement on a
table may run very quickly, or may take forever (20-30mins) to finish.
There is a trigger (AFTER INSERT for each row) on this table.

I have studied the execution plan, and it seems normal to me. The
total query cost is below 2000. There are no huge tablescans as well.
Why does the same code have different and random execution times ?
This can be very fast or VERY slow.

I tried to look at db2bp to understand what exactly this agent is
doing, but either I did not find any appropriate switches for this
problem, or they do not exist.

Is there any tool that will let me trace agent activity ? I guess I
need something beyond the output of event monitors or statement
snapshots.

Thanks in advance.

db2bp is the name of the application that an agent runs. That is the
name the server sees when either a command line or command editor
application is running on the client. There's not much that the code
would show you as the statement is running with PREP, EXECUTE and
COMMIT automatically.
It is dynamic SQL so you need to see what is happening at execution.
Snapshot on the table inserted or event monitor will bring back to you
that info.
Regards, pierre.