728x90
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.
- 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 ; - Connect to the database that you want to monitor using user ID that has DBADM authority.
db2 connect to [database] - 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]
728x90
'Db2 > Db2 reference' 카테고리의 다른 글
| The causes to mark index invalid, and how to recover the indexes. (0) | 2024.04.20 |
|---|---|
| DB2 11버전 표시 의미 (0) | 2018.03.13 |
| How many concurrently running statements allowed for a DB2 Java application and how to increase it? (0) | 2015.05.07 |
| Collecting explain data for SQL stored procedures in DB2 (0) | 2014.12.15 |
| Moving DB2 instances and database between filesystems (0) | 2014.10.31 |