DB2 SQL0964C The transaction log for the database is full

最近在做性能测试，在准备测试数据的时候，遇到讨厌的“SQL0964C The transaction log for the database is full. ”

测试数据要求：
表1 tableCur 95个字段，添加50w条数据， 单个存储过程每条记录commit方式完成，成功；
表2 TableHis，是TableCur的历史表，结构相同，需添加5000w条数据，即100天的TableCur的数据，以TableCur数据为源数据，存储过程中每天Commit一次（即一个sql插入50w条数据），失败，爆出SQL0964C 错误。

我初始实现方案：
存储过程1：

由于对DB2的不熟悉（平时都在Oracle下进行工作），根据网络上查到的方案进行测试，纠结了很久，都没能很好地解决，最后选择一种妥协的方案，貌似解决了这个问题。

The transaction log for the database is full这个错误说明事务日志已满，ibm官网找到对这个问题的说明
Cause
The default value of the DB2 Transaction log (logfilsiz) value of the database configured by ITDS is 2000. This is good enough with most directory implementations but in case the above error shows up, then the logfilsiz value must be updated to have a higher value.
给出的解决方法
Resolving the problem
increase the size of the DB2 transaction log

网络上找到很多解决方案都是基于官网给的这个方案，就是修改日志文件的大小。
但是，实际上，至少是我的场景下，这种方案并不凑效。

http://www.cnblogs.com/frankliiu-java/articles/2186343.html针对这个问题的博文中给的解决方案如下：
“Too many objects have been loaded into the library server and the transaction to delete rows in the library server transaction tracking table is overfilling the DB2® UDB transaction logs.

Workaround:
1. Stop the migrator process.
2. Stop all the DB2 UDB applications. The DB2 UDB command to stop all the applications is: db2 force applications all
3. Change the number of secondary log files in the DB2 UDB database configuration to a greater number such as 100 (default is 20).
4. To see the database configuration settings, enter the following command in a DB2 UDB command window: db2 get db cfg
5. To update the number of secondary log files to 100, enter the following: db2 command db2 update dbm cfg using LOGSECOND=100
6. Start the migrator process.
The error message should now not be appearing in the library server log, and the transaction to delete rows in the library server transaction tracking table should be successful.“
这里的解决方案是增加日志文件大小、日志文件数量的方式，与官网给的方案基本异曲同工，但仍未能解决问题。

但是这篇博文最后一段话给了我启发—-
“注意：如果出现此问题，则应该分析造成整个日志文件空间变满的原因是什么。它可能是由失控查询或用户错误造成的，因此增加日志文件的数量或大小只能在表面上解决问题。比如说，假设某个用户发起了一个 DELETE FROM tab1 语句，且 TAB1 是一个相当大的表。虽然这一语句看上去没什么问题，每行生成一条删除日记记录，但是如果未经过配置处理它可以轻易地将日志空间填满.”

我的场景中遇到的问题基本上就是这种情况，即数据库操作过程中单次事务中涉及的数据量太大，导致日志很容易就爆满。基于此，我的解决思路调整为化整为零，将以此大数据量的事务分解成若干小数据量事务，最后，我修改自己的存储过程实现：
当前表和历史表的数据在同一个存储过程中完成，即每次提交101条数据（1条TableCur + 100条TableHis），整个过程中需要继续50w次Commit，但解决了SQL0964C的问题。

附：DB2中两种日志管理方案
Circular logging
循环日志
Circular logging is the default transaction log type. Circular logging uses one transaction log of designated space (the size is defined in the Server document from 192MB to 4GB), and overwrites old data in the transaction log (first in, first out) when the allocated file size has been reached with new transactions.

Circular logging uses less space on a Domino server, but because the transactions in the log are overwritten, this method limits the administrator’s recovery options. All transactions in the log are written to the databases before they are overwritten in the log.*

Circular logging is most useful for recovery of the server in the event of server crashes, and will improve the server performance. If you use circular logging, the administrator will need to implement a backup strategy for Notes databases that deals with each database as an independent entity with no incremental backup capacity. Circular logging does not support incremental backups.

Circular logging may be of use on hub servers where the server performs an administrative function, such as a mail hub or replication hub, where the server has no mail or application databases that will require restoration from backup.

Archive logging
归档日志
Archive logging writes all transactions to the log, and once all changes have been written to the database, allows the archive of the transaction log. These transaction logs are termed inactive, since they do not contain any transactions necessary for a restart recovery.

When Domino restarts using the existing file again, it increments the log file name. If all the log files become inactive and are not archived, Domino creates additional log files. Archive logging is the recommended method of transaction logging.

Archive logging requires the use of a third-party backup utility, such as Tivoli Data Protection, to copy and archive the existing log files (.TXN files). Archive logging will enable rollback, media recovery, and point-in-time recovery methods with the use of a third-party backup utility.

You should not use archive logging without a third-party backup utility which can read the logs. The backup utility polls the log extents to determine which need backing up and reports when the backup is complete so the log extent can be reused. If this is not done, the Domino server may run out of disk space and crash.

Note: If you are using Archive logging and you are not backing up transaction log extents (.TXN), the Maximum Log Space field in the Server document will not be adhered to.

*NOTE: All information is written to the databases from the Unified Buffer Manager (UBM) (the same information is also contained in the transactional logs) before it is overwritten in the transaction log. The information is read from the UBM because the transactional logs are not read from during runtime. This would be too resource-intensive as there would be too much I/O activity and there would not be a performance increase. This is done continuously during runtime. The transactional logs are only read from after a server outage.