How does DB2 view log space?

During routine DB2 maintenance, transaction log full is a common problem, and the usage of log space is also a matter of great importance. How can we view the log space usage?
In fact, we mentioned archive settings yesterday. We know that DB2 has several parameters at the DATABASE level. The following determines the space used for transaction logs.
Log file size (4 kb) (LOGFILSIZ) = 60000
Number of primary log files (LOGPRIMARY) = 16
Number of secondary log files (LOGSECOND) = 200
Changed path to log files (NEWLOGPATH) =
Path to log files =/db2instanclog01/sample
So we can calculate the space allocated to the log (200 + 16) * 60000*4/1024/1024 about 50 GB.
If the log is a separate file system partition, we can see the usage of the log space on the operating system and monitor the log usage. The following information is displayed:

$ Df-g
Filesystem GB blocks Free % Used Iused % Iused Mounted on
/Dev/db2instanclog01 60.00 54.50 10% 1458 1%/db2instanclog01
This requires logging on to the system. In fact, db2pd can also see the log information.
Db2pd-d sample-logs

Database Partition 0 -- Database SAMPLE -- Active -- Up 34 days 17:30:12 -- Date 2013-11-26-10.42.49.558342

Logs:
Currently Log Number 117123
Pages Written 33697
Cur Commit Disk Log Reads 0
Cur Commit Total Log Reads 0
Method 1 Archive Status Success
Method 1 Next Log to Archive 117123
Method 1 First Failure n/
Method 2 Archive Status n/
Method 2 Next Log to Archive n/
Method 2 First Failure n/
Log Chain ID 2
Current LSN 0x00001A2DDE649E70

Address StartLSN State Size Pages Filename
0x0700000177B795D0 running 1a2cbfd88010 0x00000000 60000 60000 S0117104.LOG
0x0700000177B806D0 running 1a2cce7e8010 0x00000000 60000 60000 S0117105.LOG
0x0700000177B8B450 running 1a2cdd248010 0x00000000 60000 60000 S0117106.LOG
0x0700000177B82EF0 20171a2cebca8010 0x00000000 60000 60000 S0117107.LOG
0x0700000177B9ECD0 running 1a2cfa708010 0x00000000 60000 60000 S0117108.LOG
0x0700000177BA27D0 running 1a2d09168010 0x00000000 60000 60000 S0117109.LOG
0x0700000177B79E30 running 1a2d17bc8010 0x00000000 60000 60000 S0117110.LOG
0x0700000177BAAD50 running 1a2d26628010 0x00000000 60000 60000 S0117111.LOG
0x0700000177B9FFD0 running 1a2d35088010 0x00000000 60000 60000 S0117112.LOG
0x0700000177BB44D0 running 1a2d43ae8010 0x00000000 60000 60000 S0117113.LOG
0x0700000177BD45D0 running 1a2d52548010 0x00000000 60000 60000 S0117114.LOG
0x0700000177B7F0D0 running 1a2d60fa8010 0x00000000 60000 60000 S0117115.LOG
0x0700000177B9C850 running 1a2d6fa08010 0x00000000 60000 60000 S0117116.LOG
0x0700000177B84750 running 1a2d7e468010 0x00000000 60000 60000 S0117117.LOG
0x0700000177B877D0 running 1a2d8cec8010 0x00000000 60000 60000 S0117118.LOG
0x0700000177B857D0 running 1a2d9b928010 0x00000000 60000 60000 S0117119.LOG
0x0700000177B7DC50 running 1a2daa388010 0x00000000 60000 60000 S0117120.LOG
0x0700000177B83750 running 1a2db8de8010 0x00000000 60000 60000 S0117121.LOG
0x0700000177B907B0 running 1a2dc7848010 0x00000000 60000 60000 S0117122.LOG
0x0700000177B91010 running 1a2dd62a8010 0x00000000 60000 60000 S0117123.LOG
0x0700000177B9A150 running 1a2de4d08010 0x00000000 60000 60000 S0117124.LOG
However, you can only view the LSN information and archiving information corresponding to the current log and log files, but you cannot see the usage.
You can also see it in the instance snapshot, but this is not an example.

But we need to log on to the operating system above. How can we query through SQL at the remote end? In fact, DB2 still provides a lot of methods.
A. query through the Management View:
Select DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB, TOTAL_LOG_USED_TOP_KB, DBPARTITIONNUM from SYSIBMADM. LOG_UTILIZATION;

DB_NAME LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB TOTAL_LOG_AVAILABLE_KB TOTAL_LOG_USED_TOP_KB DBPARTITIONNUM
Certificate ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DSS 8.97 4631824 46955050 16655013 0
It is very clear at a glance. It is helpful for monitoring the usage of transaction logs and early detection of full transaction log space.
B. Another method is to view the snapshot view:
Select int (total_log_used/1024/1024) as "Log Used (Mb)", int (total_log_available/1024/1024) as "Log Space Free (Mb )",
Int (float (total_log_used)/float (total_log_used + total_log_available) * 100) as "Pct Used", int (tot_log_used_top/1024/1024) as "Max Log Used (Mb )",
Int (sec_log_used_top/1024/1024) as "Max Sec. Used (Mb)", int (sec_logs_allocated) as "Secondaries" from sysibmadm. snapdb;

Log Used (Mb) Log Space Free (Mb) Pct Used Max Log Used (Mb) Max Sec. Used (Mb) Secondaries
----------------------------------------------------------------------------------------
4544 45833 16264 12532 5

1 record (s) selected.

In fact, there is also a way to use table functions, but the parameters need to be included:
Select DB_NAME, TOTAL_LOG_AVAILABLE, TOTAL_LOG_USED, SEC_LOG_USED_TOP, SEC_LOGS_ALLOCATED from table (SNAP_GET_DB ('sample', 0 ))

DB_NAME TOTAL_LOG_AVAILABLE TOTAL_LOG_USED SEC_LOG_USED_TOP SEC_LOGS_ALLOCATED
Certificate ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SAMPLE 48045192251 4779767749 13141773417 5

1 record (s) selected
Look, DB2 has a lot of ways to view the log space. It doesn't mean that it provides powerful user interfaces. It depends on how you use them.

Recommended reading:

DB2 database performance adjustment and optimization (1st and 2) PDF

DB2 database performance optimization