某日下午，开发报告说在执行DML操作时，数据库报事务日志已满的错误。具体信息如下：
$db2 delete from tzsdb01
DB21034E 该命令被当作 SQL 语句来处理，因为它不是有效的“命令行处理器”命令。
在SQL处理期间，它返回：
SQL0964C数据库的事务日志已满。SQLSTATE=57011
这个问题的原因是：表中数据量过大，delete 时，会写入日志，但日志容量过小。
解决方法：增大日志容量、数据量或减少一次的删除数据量，分多次删除。
生产环境为避免白天停库，提示开发分多次删除，这样就不出错了。但是要彻底解决这个问题，还需申请停库修改数据库参数。
我们先看一下，数据库的关于日志的配置参数
$ db2 get db cfg for tzsdb01
Database Configuration for Database zssqdb01
Database configuration release level = 0x0d00
Database release level = 0x0d00
Database territory = cn
Database code page = 1208
Database code set = UTF-8
Database country/region code = 86
Database collating sequence = IDENTITY Multi-page file allocation enabled = YES
Log retain for recovery status = RECOVERY
User exit for logging status = YES
Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(1662183)
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(130720)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(97)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(421273)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(46809)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(9361)
Database heap (4KB) (DBHEAP) = AUTOMATIC(2626)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
Log buffer size (4KB) (LOGBUFSZ) = 4096 Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 4
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/ |
先看增大日志的容量，注意红色的值为1024
$db2 update db cfg for zssqdb01 using logfilsiz 8192 将其增大到8192
然后停止应用，停库再启库就生效了
$db2 force applications all
$db2stop
$db2start
