db2数据库报错：The transaction log for the database is full. SQLSTATE=57011

db2数据库报错：The transaction log for the database is full. SQLSTATE=57011

2011-05-10 12:50

做DB2的插入操作报错

db2 => insert into atmjobsts values ('20090805','2')

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0964C The transaction log for the database is full. SQLSTATE=57011

原因：

缓冲池扩展失败，由于可用的虚拟内存的大小不足

解决：

1. 查看

db2 => get db cfg

Database Configuration for Database

...

Log file size (4KB) (LOGFILSIZ) = 10240

Number of primary log files (LOGPRIMARY) = 13

Number of secondary log files (LOGSECOND) = 4

Changed path to log files (NEWLOGPATH) =

Path to log files = /db2data/db2shjh/db2inst1/NODE0000/SQL00001/SQLOGDIR/

Overflow log path (OVERFLOWLOGPATH) =

Mirror log path (MIRRORLOGPATH) =

First active log file =

Block log on disk full (BLK_LOG_DSK_FUL) = NO

Percent max primary log space by transaction (MAX_LOG) = 0

Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

...

2. 修改

后来把这个参数的大小相应调整了一下，调整后如下：

Log file size (4KB) (LOGFILSIZ) = 61440

调整方法为

update db cfg using LOGFILSIZ 61440

3. 调整后重启数据库

执行 db2stop 来停止DB2服务，如果停止不掉可以考虑执行 db2stop force 强制停止。

执行 db2start 启动DB2服务。

4. 查看

Log file size (4KB) (LOGFILSIZ) = 61440

Number of primary log files (LOGPRIMARY) = 13

Number of secondary log files (LOGSECOND) = 4

Changed path to log files (NEWLOGPATH) =

Path to log files = /db2data/db2shjh/db2inst1/NODE0000/SQL00001/SQLOGDIR/

Overflow log path (OVERFLOWLOGPATH) =

Mirror log path (MIRRORLOGPATH) =

First active log file =

Block log on disk full (BLK_LOG_DSK_FUL) = NO

Percent max primary log space by transaction (MAX_LOG) = 0

Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

5. 再执行 插入操作 OK 解决

db2 => insert into atmjobsts values('20090805','3')

DB20000I The SQL command completed successfully.