プライベートでデータを蓄積しているDB2サーバーで、巨大なインデックスを作ろうとしたら、トランザクションログがいっぱいというエラーが発生しました。
db2inst1@ubuntu14.04:~$ db2 "CREATE INDEX idx_hoge_hogehoge ON hoge(hogehoge)" 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 db2inst1@ubuntu14.04:~$
なので、以下のサイトを参考に、設定を変更して、再実行し、無事に成功しました。
以下はその対応内容のログです。
まずは現状を確認します。
db2inst1@ubuntu14.04:~$ db2 get db cfg for HOGEHOGE | grep LOG Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300 Log buffer size (4KB) (LOGBUFSZ) = 256 Log file size (4KB) (LOGFILSIZ) = 1024 Number of primary log files (LOGPRIMARY) = 13 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/ Overflow log path (OVERFLOWLOGPATH) = Mirror log path (MIRRORLOGPATH) = Block log on disk full (BLK_LOG_DSK_FUL) = NO Block non logged operations (BLOCKNONLOGGED) = NO Percent max primary log space by transaction (MAX_LOG) = 0 Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 Log retain for recovery enabled (LOGRETAIN) = OFF First log archive method (LOGARCHMETH1) = OFF Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = Log pages during index build (LOGINDEXBUILD) = OFF db2inst1@ubuntu14.04:~$
3～5行目が関連する設定項目ですね。
まずは、即時反映できるという「LOGSECOND」を弄ってみます。
db2inst1@ubuntu14.04:~$ db2 update db cfg for HOGEHOGE using LOGSECOND 4000 IMMEDIATE SQL5130N The value specified for the configuration parameter "logsecond" is not in the valid range of "0" to "254". db2inst1@ubuntu14.04:~$
「LOGSECOND」の値に制限があるようです。
db2inst1@ubuntu14.04:~$ db2 update db cfg for HOGEHOGE using LOGSECOND 254 IMMEDIATE SQL5153N The update cannot be completed because the following relationship would be violated: "logprimary + logsecond <= 256". db2inst1@ubuntu14.04:~$
更に「LOGPRIMARY + LOGSECOND」の値にも制限があるようです。
結局、インスタンスを再起動する前提で、以下のように設定しました（元に戻しやすいように、それぞれ元の値の8倍）。
db2inst1@ubuntu14.04:~$ db2 update db cfg for HOGEHOGE using LOGFILSIZ 8192 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. SQL1363W One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, all applications must disconnect from this database before the changes become effective. db2inst1@ubuntu14.04:~$ db2 update db cfg for HOGEHOGE using LOGPRIMARY 104 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. SQL1363W One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, all applications must disconnect from this database before the changes become effective. db2inst1@ubuntu14.04:~$ db2 update db cfg for HOGEHOGE using LOGSECOND 32 DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully. db2inst1@ubuntu14.04:~$ db2stop SQL1025N The database manager was not stopped because databases are still active. db2inst1@ubuntu14.04:~$ db2stop force SQL1064N DB2STOP processing was successful. db2inst1@ubuntu14.04:~$ db2start SQL1063N DB2START processing was successful. db2inst1@ubuntu14.04:~$ db2 "CREATE INDEX idx_hoge_hogehoge ON hoge(hogehoge)" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL1224N The database manager is not able to accept new requests, has terminated all requests in progress, or has terminated the specified request because of an error or a forced interrupt. SQLSTATE=55032 db2inst1@ubuntu14.04:~$ db2 "CREATE INDEX idx_hoge_hogehoge ON hoge(hogehoge)" DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL1024N A database connection does not exist. SQLSTATE=08003 db2inst1@ubuntu14.04:~$ db2 connect to HOGEHOGE Database Connection Information Database server = DB2/LINUXX8664 9.7.5 SQL authorization ID = DB2INST1 Local database alias = HOGEHOGE db2inst1@ubuntu14.04:~$ db2 "CREATE INDEX idx_hoge_hogehoge ON hoge(hogehoge)" echo 2020/02/07 08:07 ; date DB20000I The SQL command completed successfully. db2inst1@ubuntu14.04:~$
無事に作成できました。
