How to calculate the DB2 transaction used log size without locking issue

Technote (FAQ)


This document applies only to the following language version(s):

US English

Question

You might encounter locking issues that require you to monitor the status of the database. Why don't snapshot monitoring commands, such as the SYSIBMADM.LOG_UTILIZATION administrative view, work in these locking situations?

Cause

Some snapshot monitoring commands or administrative views acquire DB2 internal latches for resource serialization. These latches might be owned by EDUs on the problematic database.

Answer

The MON_GET_* table functions, such as the MON_GET_TABLESPACE and MONREPORT module, don't require a DB2 internal latch.

Unfortunately, the MON_GET_TRANSACTION_LOG table function is not available in releases prior to DB2 Version 10.1. However, you can calculate used log space with the db2pd -db DBNAME -dbcfg -logs output.

The following represents the calculation of the total available log space.

LOGDATAPAGE = 4096 (LOGPAGE) - 20 (OVERHEADBYTES) = 4076

Total available log space
= LOGFILSIZ * LOGDATAPAGE * ( LOGPRIMARY + LOGSECOND )


The following represents the calculation of the total used log space.

Total used log space
= Current LSO - StartLSO of First active log file + Compensation log space


Where "First active log Number" is the number in the "First active log file" name, found in the db2pd -dbcfg output, and "Compensation log space" is the total of all "LogSpace" values in db2pd -transactions output.

The following is an example of how to query the "Total available log space" and "Total used log space" with the SYSIBMADM.LOG_UTILIZATION administrative view.

$ date; db2 "select char(DB_NAME,16) DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB, TOTAL_LOG_USED_TOP_KB, DBPARTITIONNUM from SYSIBMADM.LOG_UTILIZATION"

Wed Mar 6 02:38:04 EST 2013

DB_NAME LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB TOTAL_LOG_AVAILABLE_KB TOTAL_LOG_USED_TOP_KB DBPARTITIONNUM
---------------- ----------------------- -------------------- ---------------------- --------------------- --------------
DB01 90.80 7402 749 7402 0

1 record(s) selected.



In the following example, the Total available log space calculation equates to 8152K and Total used log space equates to 7402K, based on the db2pd output.

The following is the "Total available log space" and "Total used log space", calculated from the db2pd output.

LOGDATAPAGE = 4096 - 20 = 4076

Total available log space
= LOGFILSIZ * LOGDATAPAGE * ( LOGPRIMARY + LOGSECOND )
= 256 * 4076 * 8
= 8347648 = 8152K

Total used log space
= Current LSO - StartLSO of First active log file + Compensation log space
= Current LSO 46082460 - StartLSO 41803457 + all LogSpace (1320458 + 1980634)
= 4279003 + (1320458 + 1980634) = 7580095 = 7402.4365234375K = 7402K

TOTAL_LOG_AVAILABLE_KB = 8152K - 7402K = 750K


$ date; db2pd -db DB01 -dbcfg -logs -transactions

Wed Mar 6 02:38:06 EST 2013
...

Database Member 0 -- Database DB01 -- Active -- Up 0 days 00:11:51 -- Date 03/06/2013 02:38:07

Database Configuration Settings:
...

LOGBUFSZ (4KB) 64 64
LOGFILSIZ (4KB) 256 256
LOGPRIMARY 5 5
LOGSECOND 3 3
First active log file S0000001.LOG S0000001.LOG
...

Database Member 0 -- Database DB01 -- Active -- Up 0 days 00:11:52 -- Date 03/06/2013 02:38:08

Logs:
Current Log Number 5
Pages Written 25
Cur Commit Disk Log Reads 0
Cur Commit Total Log Reads 0
Method 1 Archive Status Success
Method 1 Next Log to Archive 5
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Log Chain ID 0
Current LSO 46082460
Current LSN 0x0000000000046772

Address StartLSN StartLSO State Size Pages Filename
0x0700000063BB0940 000000000003A183 41803457 0x00000000 256 256 S0000001.LOG
0x0700000063BB11C0 000000000003D0A4 42846913 0x00000000 256 256 S0000002.LOG
0x0700000063BB1A40 0000000000040146 43890369 0x00000000 256 256 S0000003.LOG
0x0700000063BB22C0 00000000000431E9 44933825 0x00000000 256 256 S0000004.LOG
0x0700000063BB00C0 000000000004628B 45977281 0x00000000 256 256 S0000005.LOG
0x0700000070FBC580 0000000000000000 47020737 0x00000000 256 256 S0000006.LOG
0x0700000070F44100 0000000000000000 48064193 0x00000000 256 256 S0000007.LOG
0x0700000070FD00C0 0000000000000000 49107649 0x00000000 256 256 S0000008.LOG

Database Member 0 -- Database DB01 -- Active -- Up 0 days 00:11:52 -- Date 03/06/2013 02:38:08

Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn Firstlso Lastlso LogSpace SpaceReserved TID AxRegCnt GXID ClientUserID ClientWrkstnName ClientApplName ClientAccntng
0x07000000606AF380 28 [000-00028] 3 20002 WRITE 0x00000000 0x00000000 0x000000000003A2C0 0x000000000003F16D 41862316 43550312 1320458 3008538 0x000000000139 1 0 n/a n/a n/a n/a
0x07000000606B4480 29 [000-00029] 4 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F2 1 0 n/a n/a n/a n/a
0x07000000606B9580 30 [000-00030] 5 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x000000000150 1 0 n/a n/a n/a n/a
0x07000000606BE680 31 [000-00031] 6 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F4 1 0 n/a n/a n/a n/a
0x07000000606C3780 32 [000-00032] 7 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F6 1 0 n/a n/a n/a n/a
0x07000000606C8880 33 [000-00033] 8 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F7 1 0 n/a n/a n/a n/a
0x07000000606CD980 34 [000-00034] 9 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F8 1 0 n/a n/a n/a n/a
0x07000000606D2A80 35 [000-00035] 10 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000F9 1 0 n/a n/a n/a n/a
0x07000000606D7B80 36 [000-00036] 11 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000FA 1 0 n/a n/a n/a n/a
0x07000000606DCC80 37 [000-00037] 12 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0 0 0x0000000000FC 1 0 n/a n/a db2evml_DB2DETAILDEADLOCK n/a
0x07000000606E1D80 39 [000-00039] 13 30003 WRITE 0x00000000 0x00000000 0x000000000003F16E 0x0000000000046771 43550396 46082376 1980634 4512698 0x000000000101 1 0 n/a n/a n/a n/a

References:
Space requirements for log files http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.dbobj.doc/doc/c0004930.html

db2pd - Monitor and troubleshoot DB2 database command http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0011729.html


Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for Linux, UNIX and Windows
Monitor - Snapshot monitoring

Software version:

9.8

Operating system(s):

AIX, Linux

Reference #:

1626153

Modified date:

2013-04-16

Translate my page

Machine Translation

Content navigation