CODEWITHSUNDEEP
db2db2-luwtransaction-log
Ross Armstrong
Ross Armstrong

Reputation: 11

NUM_LOG_SPAN usage on a DB2 database (10.1)

Is there any way to see how many transaction logs a process (agent_id) currently spans? Or list the transaction logs it's currently using/spanning? I.e. is it possible to check if NUM_LOG_SPAN is about to be reached?

We've had an issue recently whereby a long running transaction breached NUM_LOG_SPAN. This was set to 70, when we had 105 logs. We've increased this now but potentially it may still not be enough. We could set NUM_LOG_SPAN to 0, but that's a last resort... what we'd like to be able to do is at least monitor the situation (and not just wait until it hits and causes issues) - to be able to run a command to see if, for example, a process was now using/spanning, say, 90 of the logs? And then we could decide whether to cancel it or not.

We're after something similar to the following statement where you can see the percentage of transaction log usage:

select log_utilization_percent,dbpartitionnum from sysibmadm.log_utilization

-is there anything similar for monitoring processes to ensure they don't cross the NUM_LOG_SPAN threshold?

NB: This is in a SAP system (NW7.3)... perhaps there's something in DBACOCKPIT to view this too?

Upvotes: 0

Views: 1294

Answers (1)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11032

As far as I can tell you can't calculate this from the monitor functions only, because none of the monitoring functions expose the Start LSN for a unit of work.

You can do this with db2pd, though. Use db2pd -db <dbname> -logs to find the Current LSN, and use db2pd -db <dbname> -transactions to find Firstlsn for the particular unit of work.

With these two numbers, you can use the formula 

                      (currentLSN - firstLSN) 
Logs Files Spanned = -------------------------
                         logfilsiz * 4096

(You should convert the hex values for current LSN and firstLSN returned by db2pd to decimal values).

Upvotes: 1

Related Questions