DB2短时间内产生大量归档日志、且每个日志都很小的原因

最新推荐文章于 2021-05-08 02:01:26 发布

匿_名_用_户

最新推荐文章于 2021-05-08 02:01:26 发布

阅读量3.7k

点赞数

分类专栏： DB2 文章标签： DB2 归档日志

本文链接：https://blog.csdn.net/qingsong3333/article/details/75267334

版权

DB2 专栏收录该内容

181 篇文章 9 订阅

订阅专栏

问题现象：

为什么我的DB2归档日志目录里，短时间内产生了大量的归档日志，而且每个归档日志都很小，远达不到设定的日志大小？

原因分析：

如果有应用使用过活动日志，当数据库取消激活的时候，就会对日志进行归档。如果有大量的短连接，那么就会频繁地归档。这里的短连接是指连接上数据库，只使用了少量的日志，然后就断开。

数据库激活的时候会分配相应的资源，比如缓冲池等，有显式激活和隐式激活两种：显式激活即使用 "db2 activate db <dbname>"；隐式激活就是第一个连接连到数据库时，就会隐式地激活数据库。数据库是显式激活还是隐式激活的，是可以通过db2diag.log看出来的：

显式地激活数据库：
$ db2 activate db sample
DB20000I The ACTIVATE DATABASE command completed successfully.

db2diag.log中FristConnect会显示ACTIVDATED: YES，表示是显式激活的

2017-07-06-17.51.38.577456-420 E3547E503 LEVEL: Event
PID : 1860 TID : 139692209071872 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-7 APPID: *LOCAL.inst105.170707005136
AUTHID : INST105 HOSTNAME: db2a
EDUID : 18 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase:: FirstConnect, probe:1000
START : DATABASE: SAMPLE : ACTIVATED: YES

如果数据库是显式激活的，必须显式地取消激活，否则数据库一直外于active状态：
$ db2 deactivate db sample
DB20000I The DEACTIVATE DATABASE command completed successfully.

2017-07-06-17.53.36.693974-420 E7927E517 LEVEL: Event
PID : 1860 TID : 139692104214272 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-22 APPID: *LOCAL.inst105.170707005336
AUTHID : INST105 HOSTNAME: db2a
EDUID : 43 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase:: FreeResourcesOnDBShutdown, probe:15579
STOP : DATABASE: SAMPLE : DEACTIVATED: YES

隐式地激活数据库:
$ db2 connect to sample

Database Connection Information

Database server = DB2/LINUXX8664 10.5.8
SQL authorization ID = INST105
Local database alias = SAMPLE

db2diag.log中FristConnect会显示ACTIVDATED: NO，表示是隐式激活的：
2017-07-06-17.53.57.490708-420 E9864E502 LEVEL: Event
PID : 1860 TID : 139692104214272 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-23 APPID: *LOCAL.inst105.170707005357
AUTHID : INST105 HOSTNAME: db2a
EDUID : 43 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase:: FirstConnect, probe:1000
START : DATABASE: SAMPLE : ACTIVATED: NO

最后一个连接断开的时候，（如果是隐式激活的）数据库会取消激活
$ db2 terminate
DB20000I The TERMINATE command completed successfully.

2017-07-06-17.54.51.948604-420 E11499E516 LEVEL: Event
PID : 1860 TID : 139692104214272 PROC : db2sysc 0
INSTANCE: inst105 NODE : 000 DB : SAMPLE
APPHDL : 0-23 APPID: *LOCAL.inst105.170707005357
AUTHID : INST105 HOSTNAME: db2a
EDUID : 43 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase:: FreeResourcesOnDBShutdown, probe:15579
STOP : DATABASE: SAMPLE : DEACTIVATED: NO

问题重现：

下面使用这个脚本来模拟大量的“短连接”

$ cat connect.sh
#!/bin/bash
for i in {1..100}
do
db2 "connect to sample"
db2 "insert into t1 values($i)"
db2 connect reset
done

执行完这个脚本之后，查看归档日志目录，发现有100个12K的日志
$ ls -lh
total 1.2M
-rw-r----- 1 inst105 db2iadm 20K Jul 6 18:03 S0000000.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000001.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000002.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000003.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000004.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000005.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000006.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000007.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000008.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000009.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000010.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000011.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000012.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000013.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000014.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000015.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000016.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000017.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000018.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000019.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000020.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000021.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000022.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000023.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000024.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000025.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000026.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000027.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:04 S0000028.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000029.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000030.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000031.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000032.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000033.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000034.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000035.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000036.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000037.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000038.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000039.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000040.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000041.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000042.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000043.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000044.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000045.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000046.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000047.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000048.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000049.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000050.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000051.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000052.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000053.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000054.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000055.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000056.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000057.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000058.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000059.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000060.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000061.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000062.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000063.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000064.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000065.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000066.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000067.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000068.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000069.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000070.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000071.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000072.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000073.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000074.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000075.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000076.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000077.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000078.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000079.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:05 S0000080.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000081.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000082.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000083.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000084.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000085.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000086.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000087.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000088.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000089.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000090.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000091.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000092.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000093.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000094.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000095.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000096.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000097.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000098.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000099.LOG
-rw-r----- 1 inst105 db2iadm 12K Jul 6 18:06 S0000100.LOG

但实际上配置的LOGFILSIZ是4000KB
$ db2 get db cfg for sample | grep -i logfilsiz
Log file size (4KB) (LOGFILSIZ) = 1000

至此，重现了问题，如果我们连上数据库，进行一些“正常”的操作，产生了一些日志，查看大小，确实是4.0M

-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000101.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000102.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000103.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000104.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000105.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000106.LOG
-rw-r----- 1 inst105 db2iadm 4.0M Jul 6 18:13 S0000107.LOG