DB2在大事务运行时，会占用大量的日志空间，如果没有人为干预，就会撑满整个事务日志（如果设置了第二日志，会继续使用第二日志） ，撑满日志后，其他更新、
插入、load等操作就无法进行，且撑满日志的事务开始回滚，回滚期间日志不会释放，如果事务较大，回滚也需要花费大量的时间，且无法避免回滚。
如，在diag日志中如下错误：
db2inst1@bicitymkt1:/gzdmt01_inst/db2inst1/sqllib/db2dump > tail -100 db2diag.log
2014-05-06-12.18.32.077524+480 E2750035999A592 LEVEL: Error
PID : 508878 TID : 11050 PROC : db2sysc 14
INSTANCE: db2inst1 NODE : 014 DB : GZDMT
APPHDL : 0-26535 APPID: 135.10.44.140.64817.14050604183
AUTHID : GZCRM
EDUID : 11050 EDUNAME: db2agntp (GZDMT) 14
FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:2860
MESSAGE : ADM1823E The active log is full and is held by application handle
"23429". Terminate this application by COMMIT, ROLLBACK or FORCE
APPLICATION.
2014-05-06-12.18.32.089277+480 I2750036592A554 LEVEL: Error
PID : 508878 TID : 11050 PROC : db2sysc 14
INSTANCE: db2inst1 NODE : 014 DB : GZDMT
APPHDL : 0-26535 APPID: 135.10.44.140.64817.14050604183
AUTHID : GZCRM
EDUID : 11050 EDUNAME: db2agntp (GZDMT) 14
FUNCTION: DB2 UDB, data protection services, sqlpWriteLR, probe:6680
MESSAGE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
"Log File has reached its saturation point"
DIA8309C Log file was full.
从上面可以看出23429会话撑满了整个日志，并在回滚。
查看23429会话信息，另可以使用DB2TOP实时查看监控进度：
db2inst1@bicitymkt1:/gzdmt01_inst/db2inst1/sqllib/db2dump > db2 list applications|grep 23429
GZ856 SQLA.exe 23429 135.6.44.46.26564.140506011959 GZDMT 3
db2inst1@bicitymkt1:/gzdmt01_inst/db2inst1/sqllib/db2dump > db2 get snapshot for application agentid 23429
Application Snapshot
Application handle = 23429
Application status = Rollback to Savepoint
Start Time = 2014-05-06 14:27:33.011796
Completed Work = 5860141490 bytes
Total Work = 38455914414 bytes
Status change time = Not Collected
Application code page = 1208
Application country/region code = 86
DUOW correlation token = 135.6.44.46.26564.140506011959
Application name = SQLA.exe
Application ID = 135.6.44.46.26564.140506011959
Sequence number = 00024
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =
Connection request start timestamp = 2014-05-06 09:19:58.533825
Connect request completion timestamp = 2014-05-06 09:19:58.538374
Application idle time = Not Collected
CONNECT Authorization ID = GZ856
Client login ID = ADMINISTRATOR
Configuration NNAME of client = WINDOWS7
Client database manager product ID = SQL09052
Process ID of client application = 5852
Platform of client application = NT
Communication protocol of client = TCP/IP
Inbound communication address = 135.6.44.46 50279
Database name = GZDMT
Database path = /db2data/db2inst1/NODE0000/SQL00001/
Client database alias = CITYMKT
Input database alias =
Last reset timestamp =
Snapshot timestamp = 2014-05-06 14:56:03.113500
Authorization level granted =
User authority:
CREATETAB authority
BINDADD authority
CONNECT authority
CREATE_NOT_FENC authority
LOAD authority
IMPLICIT_SCHEMA authority
CREATE_EXT_RT authority
QUIESCE_CONN authority
Group authority:
Coordinating database partition number = 0
Current database partition number = 0
Coordinator agent process or thread ID = 33984
Current Workload ID = 1
Agents stolen = 0
Agents waiting on locks = 0
Maximum associated agents = 17
Priority at which application agents work = 0
Priority type = Dynamic
Lock timeout (seconds) = 600
Locks held by application = 4
Lock waits since connect = 0
Time application waited on locks (ms) = Not Collected
Deadlocks detected = Not Collected
Lock escalations = 0
Exclusive lock escalations = 0
Number of Lock Timeouts since connected = 0
Total time UOW waited on locks (ms) = Not Collected
Total sorts = 13
Total sort time (ms) = Not Collected
Total sort overflows = 5
Buffer pool data logical reads = Not Collected
Buffer pool data physical reads = Not Collected
Buffer pool temporary data logical reads = Not Collected
Buffer pool temporary data physical reads = Not Collected
Buffer pool data writes = Not Collected
Buffer pool index logical reads = Not Collected
Buffer pool index physical reads = Not Collected
Buffer pool temporary index logical reads = Not Collected
Buffer pool temporary index physical reads = Not Collected
Buffer pool index writes = Not Collected
Buffer pool xda logical reads = Not Collected
Buffer pool xda physical reads = Not Collected
Buffer pool temporary xda logical reads = Not Collected
Buffer pool temporary xda physical reads = Not Collected
Buffer pool xda writes = Not Collected
Total buffer pool read time (milliseconds) = Not Collected
Total buffer pool write time (milliseconds)= Not Collected
Time waited for prefetch (ms) = Not Collected
Unread prefetch pages = Not Collected
Direct reads = Not Collected
Direct writes = Not Collected
Direct read requests = Not Collected
Direct write requests = Not Collected
Direct reads elapsed time (ms) = Not Collected
Direct write elapsed time (ms) = Not Collected
Number of SQL requests since last commit = 1
Commit statements = 23
Rollback statements = 0
Dynamic SQL statements attempted = 117
Static SQL statements attempted = 23
Failed statement operations = 6
Select SQL statements executed = 4
Xquery statements executed = 0
Update/Insert/Delete statements executed = 7
DDL statements executed = 9
Inactive stmt history memory usage (bytes) = 0
Internal automatic rebinds = 0
Internal rows deleted = 0
Internal rows inserted = 0
Internal rows updated = 0
Internal commits = 1
Internal rollbacks = 0
Internal rollbacks due to deadlock = 0
Binds/precompiles attempted = 0
Rows deleted = 0
Rows inserted = 180248921
Rows updated = 0
Rows selected = 56481
Rows read = 952771401
Rows written = 314312007
UOW log space used (Bytes) = Not Collected
Previous UOW completion timestamp = Not Collected
Elapsed time of last completed uow (sec.ms)= Not Collected
UOW start timestamp = Not Collected
UOW stop timestamp = Not Collected
UOW completion status = Not Collected
Open remote cursors = 0
Open remote cursors with blocking = 0
Rejected Block Remote Cursor requests = 0
Accepted Block Remote Cursor requests = 4
Open local cursors = 0
Open local cursors with blocking = 0
Total User CPU Time used by agent (s) = 2480.118585
Total System CPU Time used by agent (s) = 17.442316
Host execution elapsed time = Not Collected
Package cache lookups = 230
Package cache inserts = 31
Application section lookups = 144
Application section inserts = 23
Catalog cache lookups = 143
Catalog cache inserts = 4
Catalog cache overflows = 0
Catalog cache high water mark = 0
Workspace Information
Shared high water mark = 0
Total shared overflows = 0
Total shared section inserts = 0
Total shared section lookups = 0
Private high water mark = 0
Total private overflows = 0
Total private section inserts = 0
Total private section lookups = 0
Most recent operation = Execute Immediate
Most recent operation start timestamp = Not Collected
Most recent operation stop timestamp = Not Collected
Agents associated with the application = 1
Number of hash joins = 5
Number of hash loops = 0
Number of hash join overflows = 0
Number of small hash join overflows = 0
Number of OLAP functions = 0
Number of OLAP function overflows = 0
Memory usage for application:
Memory Pool Type = Application Heap
Current size (bytes) = 65536
High water mark (bytes) = 720896
Configured size (bytes) = 16777216
Agent process/thread ID = 33984
Database partition number = 0
Agent Lock timeout (seconds) = 600
Memory usage for agent:
Memory Pool Type = Other Memory
Current size (bytes) = 196608
High water mark (bytes) = 262144
Configured size (bytes) = 284541583360
查看当时日志使用率，有几个分区接近100%：
db2 => select DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB,TOTAL_LOG_AVAILABLE_KB,TOTAL_LOG_USED_TOP_KB,
DBPARTITIONNUM from SYSIBMADM.LOG_UTILIZATION order by LOG_UTILIZATION_PERCENT desc ;
DB_NAME LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB TOTAL_LOG_AVAILABLE_KB TOTAL_LOG_USED_TOP_KB DBPARTITIONNUM
-------- ----------------------- -------------------- ---------------------- --------------------- --------------
GZDMT 98.99 47334360 478139 47334378 14
GZDMT 95.81 45812553 1999946 45812597 9
GZDMT 95.65 45735695 2076804 45745831 0
GZDMT 95.00 45423513 2388986 45423557 4
GZDMT 94.00 44945908 2866591 44945952 1
GZDMT 92.75 44347136 3465363 44347180 10
GZDMT 90.75 43394525 4417974 43394569 15
GZDMT 89.00 42554007 5258492 42554051 5
GZDMT 88.26 42200068 5612431 42200112 13
GZDMT 87.50 41837996 5974503 41838040 11
GZDMT 86.78 41495283 6317216 41495327 8
GZDMT 86.11 41171580 6640919 41171624 7
GZDMT 85.92 41082908 6729591 41082952 2
GZDMT 85.16 40718409 7094090 40718453 3
GZDMT 84.25 40283255 7529244 40283299 6
GZDMT 84.01 40171008 7641491 40171052 12
16 record(s) selected.
经过6小时的回滚，数据库恢复正常，日志空间释放：
db2 => select DB_NAME, LOG_UTILIZATION_PERCENT, TOTAL_LOG_USED_KB,TOTAL_LOG_AVAILABLE_KB,TOTAL_LOG_USED_TOP_KB,
DBPARTITIONNUM from SYSIBMADM.LOG_UTILIZATION order by LOG_UTILIZATION_PERCENT desc ;
DB_NAME LOG_UTILIZATION_PERCENT TOTAL_LOG_USED_KB TOTAL_LOG_AVAILABLE_KB TOTAL_LOG_USED_TOP_KB DBPARTITIONNUM
----------------- ----------------------- -------------------- ---------------------- --------------------- --------------
GZDMT 1.00 478591 47333908 44945952 1
GZDMT 0.99 477369 47335130 42554051 5
GZDMT 0.99 478024 47334475 45423557 4
GZDMT 0.99 476295 47336204 47334378 14
GZDMT 0.92 440716 47371783 41082952 2
GZDMT 0.81 388926 47423573 45812597 9
GZDMT 0.78 374955 47437544 41495327 8
GZDMT 0.75 361647 47450852 43394569 15
GZDMT 0.74 357956 47454543 44347180 10
GZDMT 0.65 312430 47500069 45745831 0
GZDMT 0.50 239519 47572980 41838040 11
GZDMT 0.25 123434 47689065 42200112 13
GZDMT 0.24 119230 47693269 40283299 6
GZDMT 0.15 76236 47736263 40718453 3
GZDMT 0.10 51250 47761249 41171624 7
GZDMT 0.01 6980 47805519 40171052 12
16 record(s) selected.
而后对事务日志使用率进行了监控，如果超过50%，就人为干预，通告使用者，或者直接断开会话db2 "force application (23429)"。
--转自
发起投票
技术讨论
加好友
发消息
2015-9-24 9:24:07 |
赞(
操作
