The critical consideration here is not just the amount of log space used, but how long the transaction holds that log space before a commit or a rollback.
The former feeds into your choice of MAX_LOG setting, the latter into NUM_LOG_SPAN. Both are zero by fault.
The Transaction Log Full shell from my website: http://www.sustainablesoftware.net/downloads.htm reports on log space utilisation and homes in on the transaction holding the oldest uncommitted transaction.
You need only adapt this, if you wish to, to report on log utilisation by agent id.
If you do download from my site, please respect copyleft conditions.

Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author.

Popular White Paper On This Topic

The critical consideration here is not just the amount of log space used, but how long the transaction holds that log space before a commit or a rollback.
The former feeds into your choice of MAX_LOG setting, the latter into NUM_LOG_SPAN. Both are zero by fault.
The Transaction Log Full shell from my website: http://www.sustainablesoftware.net/downloads.htm reports on log space utilisation and homes in on the transaction holding the oldest uncommitted transaction.
You need only adapt this, if you wish to, to report on log utilisation by agent id.
If you do download from my site, please respect copyleft conditions.

@Alex: Am I right then that element UOW_LOG_SPACE_USED in view "snapappl" can give me the Idea how much log space is used by my applications in general and in case when transaction log full error occur. Hence this element can give me the Idea on what should be my MAX_LOG /NUM_LOG_SPAN value. ?

Also how can it be found that how many insert/update/delete operations an application has performed since last commit? The snapshots metrics just show the total insert/update/delete since start.

You have to use a WAG and adjust accordingly, if your not up to the testing the
application scenarios to generate the logs... there are a lot of smart
people on this forum, but if they were that smart, then i would hire them
at 200k a year and it would be worth it. Predictions like that... FOREX and
NYSE here we come.

So, use db2batch and Alex's generous contribution of scripts on most of not
all of the queries in your app, then multiply by a peak factor according to
your max transactions in previous busiest time period, add 20% to that
value and voila! you have your values.

Narvariyadeevedra,
There are (at least) 3 questions you asked. The key thing you must bear in mind is that log space is only 'used' for uncommitted transactions.
Q1. "Am I right then that element UOW_LOG_SPACE_USED in view "snapappl" can give me the Idea how much log space is used by my applications in general ?"
A1. This monitor element is at application level. Therefore you would need to sum it to get a total for applications in general.
Q2. "Hence this element can give me the Idea on what should be my MAX_LOG /NUM_LOG_SPAN value. ?"
A2. No. You could in theory divide UOW_LOG_SPACE_USED by LOGFILSIZ to calculate how many logs a particular uncommitted/in-flight transaction uses and therefore the upper bounds for NUM_LOG_SPAN and MAX_LOG, but remember your application units of work are not uniform. What is critical is their commit or rollback behaviour. If an application updates just a single row but does not commit or rollback, eventually the transaction log will fill, even though the log space it uses is tiny.
Q3. "Also how can it be found that how many insert/update/delete operations an application has performed since last commit? The snapshots metrics just show the total insert/update/delete since start."
A3. Snapshots are exactly that - metrics at a point in time. Subtract them from each other to obtain incremental values. Or use db2top in either batch or graphical mode to calculate deltas for you.

I dislike those aspects of IT cookbooks which encourage lazy people to pursue easy answers; they lack rigor and seldom propose that truth is rich and varied. A good cookbook should at least encourage experiment. Consequently the readmes for most of the scripts at my site emphasize that it is assumed the user knows or can research what he is doing and has the skill to tailor for his needs. In general, there is a very good reason why MAX_LOG and NUM_LOG_SPAN are off by default; which is that there is no need for them if application logic is coded sensibly with concurrency in mind. That is, connect as late as possible; do as little work and spend as little time in the database as possible; commit and disconnect as soon as possible. You will have a much more rewarding life as a DBA if you can accomplish that.

Hey Fitz
Howdy buddy. In the UK WAGs are Wives and Girlfriends - usually the vacuous, bulimic fashion-victim airheads attached to sporting and television 'celebrities'.
Have now found the US version: wild ass guess. I use 'ROT' - rule of thumb.
Cheers
Alex

Further: MAX_LOGS and NUM_LOG_SPAN are universal at each database partition and do not distinguish between users or workloads. Using them to govern application behaviour is therefore indiscriminate. A customer order should occupy a fraction of a log; a REORG may well span several or even dozens. This is another reason to abandon the idea and concentrate instead on promoting good transaction design.