LearnHouse

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

紀錄一下,作量測很容易會讓資料庫塞太多東西,塞爆後會出現很多奇怪的問題

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

資料來源:http://www.db2china.net/home/space.php?uid=7190&do=blog&id=9009

posted by admin in 參考文獻 and have No Comments

Place your comment

Please fill your data and comment below.
Name
Email
Website
Your comment