0

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?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Ross Armstrong
  • 11
  • 6
  • This: https://stackoverflow.com/questions/30385577/quantity-of-transaction-logs-used-per-application-connection-in-db2 ? – mustaccio Nov 01 '16 at 17:09
  • I did a bit more digging but still don't quite have the answer. I can check the agent using the most space and then divide by log size - that will give me the MAXIMUM number of logs it's spanning - but it could be spanning more though and only using a small amount on each, so not really that useful: "select agent_id, INT(uow_log_space_used/131072) from sysibmadm.snapappl order by uow_log_space_used" – Ross Armstrong Nov 02 '16 at 16:51
  • Similarly I could also check the current logs being used (i.e. between first and current is how many being spanned): Select first_active_log, CURRENT_ACTIVE_LOG, last_active_log, APPLID_HOLDING_OLDEST_XACT from table(mon_get_transaction_log(-1)) Not quite what I was trying to achieve though... no-one has any ideas? – Ross Armstrong Nov 02 '16 at 16:52

1 Answers1

1

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).

Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • That looks really promising, will give that a go... thanks! If it works well I can probably put it into a script and run it for the longest running agents/units of work to see how many logs they span. – Ross Armstrong Nov 03 '16 at 10:23
  • Hmm not sure this works so well; the number coming back is always tiny. I just found a process running for over 8 mins; based on those values/calcs it's only spanning 0.026 logs... you'd think a long running process would be spanning several; although I suppose that's not necessarily true if it's not doing much but just waiting on something....? – Ross Armstrong Nov 03 '16 at 14:19
  • Hang on... just checked one that'd been running for 21 minutes - the firstLSN number for that is 0x0000000000000000! And it seems quite a few have this value... – Ross Armstrong Nov 03 '16 at 14:20
  • I realised I'd been looking at the 10th field i.e. Lastlsn previously - as I'd ignored the zero value for the 9th field, Firstlsn. ALL the values in the Lastlsn from the db2pd command are 0x0000000000000000!?! – Ross Armstrong Nov 03 '16 at 14:40
  • Unless there is an active transaction (i.e. a log record has been written to the transaction log, and there hasn't been a `COMMIT` or `ROLLBACK`), Firstlsn will always show zero. – Ian Bjorhovde Nov 03 '16 at 16:34
  • Firstlsn seems to be ALWAYS showing zero, even when the system is busy/lots going on - it seems db2pd isn't picking this information up in our systems?! – Ross Armstrong Nov 07 '16 at 15:09
  • @RossArmstrong That's odd. What version of DB2 are you using? Are you running `db2pd` using an ID that has SYSADM authority? FYI you can test this by doing something simple that holds a transaction open, like `db2 +c "create table t1 (c1 int)"` and then looking to see if Firstlsn is not 0 anymore for that particular connection. – Ian Bjorhovde Nov 07 '16 at 17:23