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

最新推荐文章于 2024-06-18 23:04:27 发布

原创最新推荐文章于 2024-06-18 23:04:27 发布 · 6.6k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#db2 #database #sql #c #path #command

DB2 专栏收录该内容

37 篇文章

订阅专栏

本文介绍了在执行DB2插入操作时遇到的SQL0964C错误，即数据库日志已满的情况。解决方案包括检查数据库配置，特别是日志文件大小（LOGFILSIZ）和数量，通过更新DB CFG参数增大日志文件大小，并重启数据库以应用更改。最终成功执行插入操作，问题得到解决。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

做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.

------------------------

over. haha!