How to find out current transaction log size in DB2?

Question

How to find out the current transaction log size? Is it possible to do e.g. by querying some system tables with SQL? It would also be interesting to find out the maximum size for the transaction log.

Is the only option to look it up from the file system?

Solution 1

Yes, you can get this data from table SYSIBMADM.DBCFG

For example - I had to check database log parameters, so I used query:

SELECT * FROM SYSIBMADM.DBCFG
WHERE
    NAME IN ('logfilsiz','logprimary','logsecond')

Solution 2

The GET DATABASE CONFIGURATION command will give you all the configuration information about a database.

It includes information about the log file size, the number of primary and secondary log files etc. Sample output below.

Log file size (4KB)                         (LOGFILSIZ) = 1024
Number of primary log files                (LOGPRIMARY) = 13
Number of secondary log files               (LOGSECOND) = 4
Changed path to log files                  (NEWLOGPATH) =
Path to log files                                       = D:\DB2\NODE0000\SQL00003\SQLOGDIR\
Overflow log path                     (OVERFLOWLOGPATH) =
Mirror log path                         (MIRRORLOGPATH) =
First active log file                                   =
Block log on disk full                (BLK_LOG_DSK_FUL) = NO
Block non logged operations            (BLOCKNONLOGGED) = NO
Percent max primary log space by transaction  (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0