How to find long-running uncommitted transactions.

Question

I want to prevent SQL0964C  "The transaction log for the database is full" error.

How can I monitor applications that remain uncommitted for a long time or consume huge amount of transaction log space?

 

Answer

The combination of MON_GET_UNIT_OF_WORK and MON_GET_CONNECTION table functions can be used to achieve such purpose.

1. Create a file (mon_transactions.sql) containing following SQL:

   SELECT
   con.application_handle,
   con.application_id,
   con.application_name,
   con.client_pid,
   uow.uow_start_time,
   uow.uow_log_space_used
   FROM
   table(mon_get_connection(cast(null as bigint), -1)) as con,
   table(mon_get_unit_of_work(null, -1)) as uow
   WHERE
   con.application_handle = uow.application_handle and
   uow.uow_log_space_used != 0
   ORDER BY uow.uow_start_time ;

2. Connect to the database that you want to monitor using user ID that has DBADM authority.

   db2 connect to [database]

3. Execute the monitoring SQL created in Step 1. 

   db2 -tvf mon_transactions.sql

[example output]

APPLICATION_HANDLE  APPLICATION_ID                APPLICATION_NAME CLIENT_PID  UOW_START_TIME             UOW_LOG_SPACE_USED
------------------ ------------------------------ ---------------- ----------  -------------------------- ------------------
             20136 192.168.1.1.49538.181224175700 db2bp                 10648  2018-12-25-09.59.43.450650              15593
             20201 *LOCAL.db2inst1.181214145300   db2bp                  8948  2018-12-25-10.27.51.465542              15436
2 record(s) selected.

 

 

The application with a very old UOW_START_TIME could cause SQL0964C error. If the UOW_LOG_SPACE_USED keeps increasing for a particular application, that application may also cause a log full situation.

Additional tips

"mon_req_metrics" database configuration parameter needs to be set as BASE (default) or EXTENDED in order to collect the metrics using the monitoring SQL. You can confirm the current value of database configuration parameters with the following command.

db2 get db cfg for [database]