Enhance MON_GET_TRANSACTION_LOG to return Transaction Log number of OLDEST_TX_LSN and the Transaction log number of the minbuflsn recovery information returned by db2pd -dirtypages

Enhance MON_GET_TRANSACTION_LOG to return Transaction Log number of OLDEST_TX_LSN and the Transaction log number of the minbuflsn recovery information returned by db2pd -dirtypages.

For example when trying to identify the reason for the First Active Log falling way behind the current log when using the ALTERNATE page cleaning method

DB2_USE_ALTERNATE_PAGE_CLEANING=YES

Target for oldest page in LBP (PAGE_AGE_TRGT_MCR) = 240

Currently the MON_GET_TRANSACTION_LOG returns the OLDEST_TX_LSN (210261059045), you then need to convert it to HEX 0x30F48925E5 and you then need to take this value and fed it through the command

db2flsn -db db_name 30F48925E5

Given LSN is in log file S0093705.LOG.

The MON_GET_TRANSACTION_LOG should be enhanced to return the transaction log file number of the OLDEST_TX_LSN.

This would be more useful in automated monitoring scripts to determine if the oldest transaction LSN is one holding the First Active log behind the current log.

Alternatively it could be the Recovery minbuffLSN that this holding the First Active Log behind the current active, again you have to jump through hoops to work this out.

db2pd -db csnxdb -dirtypages | egrep -i "Bufferpool|Recover|minbuflsn"

Bufferpool: 1

Bufferpool minbuflsn: 00000030F4682759

Bufferpool: 2

Bufferpool minbuflsn: 00000030F492529F

Bufferpool: 3

Bufferpool minbuflsn: 00000030F2312603

Bufferpool: 4

Bufferpool minbuflsn: 00000030F496C657

Bufferpool: 5

Bufferpool minbuflsn: 00000030F4897AF8

Bufferpool: 6

Bufferpool minbuflsn: 00000030F496BF54

Bufferpool: 7

Bufferpool minbuflsn: 00000030F493B763

Bufferpool: 8

Bufferpool: 9

Bufferpool minbuflsn: FFFFFFFFFFFFFFFF

Recovery information:

minbuflsn : 00000030F2312603

db2pd -db csnxdb -logs | egrep "Current"

Current Log Number 93705

Current LSO 48888835394706

Current LSN 0x00000030F4972BFD

db2flsn -db csnxdb 00000030F2312603

Given LSN is in log file S0093688.LOG

As shown above it takes 3 command to work out that the minbufflsn Recovery position is holding the first active log back by 17 logs, it is not a long transaction in the application.

The page cleaners do not seem to be keeping up and keeping in line with the target for oldest page in LBP (PAGE_AGE_TRGT_MCR) = 240 seconds (4mins).

Log S0093688.LOG started to be used at 14:35hrs

Log S0093688.LOG stopped been used at 14:52hrs

The current time on the system is

Mon 17 Jul 22:13:06 2023.

That is almost 8hrs ago, not the 4mins of PAGE_AGE_TRGT_MCR.

If I execute db2 "flush bufferpools all"

It will immediately junp forward the first active log.

Again it would be useful for automated monitoring scripts if the MON_GET_TRANSACTION_LOG dsiplayed the RECOVERY MINBUFFLSN returned by db2pd -dirtypages and the return the transaction log file number of the Recovery MINBUFFLSN.

Having both these transaction log file number in the MON_GET_TRANSACTION_LOG would make easier/quicker to identify what is holding the First Active log behind the current log.

Please see IBM suport case TS013259422 "First Active Transaction Log falling" behind for additional information.

Thank you.