Tracing agent execution and activity

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michel Esber - 07 Apr 2008 20:27 GMT

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.

Reply to this Message

Michel Esber - 08 Apr 2008 03:09 GMT

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:
>
[quoted text clipped - 18 lines]
>
> Thanks in advance.

Reply to this Message

netzorro - 14 Apr 2008 03:53 GMT

i think you should look for locks

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.

Reply to this Message

Pierre StJ - 14 Apr 2008 19:49 GMT

> i think you should look for locks
>
[quoted text clipped - 20 lines]
>
> 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.

Reply to this Message

Pierre StJ - 14 Apr 2008 19:45 GMT

> Environment:
>
[quoted text clipped - 18 lines]
>
> 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.

Reply to this Message