Change the size and number of log files in db2

In db2, it is easier to change the size and number of log files. You only need to modify two parameters, which is more convenient than ORACLE. By default, log files are stored in the SQLOGDIR directory. [Huateng @ db2server SQLOGDIR] $ pwd/home/huateng/NODE0000/SQL00001/SQLOGDIR [huateng @ db2server SQLOGDIR] $ ls-lh total 53 M Running-rw ------- 1 huateng 4.1 M 03-30 06:26 S0000011.LOG-rw ------- 1 huateng 4.1 M 03-30 06:26 hour-rw ------- 1 huateng 4.1 M 03-30 06:26 S0000013.LOG-rw ------- 1 huateng 4.1 M 03-30 06:26 S0000014.LOG-rw ------- 1 huateng 4. 1 M 03-30 06:26 S0000015.LOG-rw ------- 1 huateng 4.1 M 03-30 06:26 S0000016.LOG-rw ------- 1 huateng 4.1 M 03-30 06:26 minutes-rw ------- 1 huateng 4.1 M 03-30 06:26 S0000018.LOG-rw ------- 1 huateng 4.1 M 03-30 06:26 hour-rw ------- 1 huateng 4.1 M 03-30 06:26 s000001_log-rw ------- 1 huateng 4.1 M 03-30 06:26 S0000021.LOG-rw ------- 1 huateng huat Eng 4.1 M 03-30 06:26 S0000022.LOG-rw ------- 1 huateng 4.1 M 03-30 06:26 S0000023.LOG [huateng @ db2server SQLOGDIR] $ db2 connect to dbtest Database Connection Information Database server = DB2/ LINUX 9.7.0 SQL authorization ID = HUATENG Local database alias = DBTEST [huateng @ db2server SQLOGDIR] $ db2 get db cfg | grep LOGPRIMARY Number of primary log files (LOGPRIMARY) = 13 [huateng @ db2server S QLOGDIR] $ db2 get db cfg | grep LOGFILSIZ Log file size (4 kb) (LOGFILSIZ) = 1024 the current number of Log files is 13 and the size is about 4 MB. Change the number of log files to 3 and the size is 16 Mb. [Huateng @ db2server SQLOGDIR] $ db2 update db cfg using LOGPRIMARY 3SQL5153N The update cannot be completed because the following relationship wocould be violated: "softmax <= 100 * logprimary ". www.2cto.com [huateng @ db2server SQLOGDIR] $ db2 get db cfg | grep-I softmax Percent log file reclaimed before soft chckpt (SOFTMAX) = 520 [huateng @ db2server SQLOGDIR] $ db2 update db cfg using SOFTMAX 200DB20000I 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 valid tive. [huateng @ db2server SQLOGDIR] $ db2 update db cfg using LOGPRIMARY 3DB20000I 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 valid tive. [huateng @ db2server SQLOGDIR] $ db2 update db cfg using LOGFILSIZ 4096DB20000I 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 valid tive. [huateng @ db2server SQLOGDIR] $ db2 force applications allDB20000I The force application command completed successfully. DB21024I This command is asynchronous and may not be valid tive immediately. [huateng @ db2server SQLOGDIR] $ db2stop2012-03-30 06:34:01 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [huateng @ db2server SQLOGDIR] $ db2start03/30/2012 06:34:32 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. [huateng @ db2server SQLOGDIR] $ db2 connect to dbtest Database Connection Information Database server = DB2/LINUX 9.7.0 SQL authorization ID = HUATENG Local database alias = DBTEST www.2cto.com [huateng @ db2server SQLOGDIR] $ ls -lh total 209M-rw ------- 1 huateng 17 M 03-30 06:31 S0000011.LOG-rw ------- 1 huateng 17 M 03-30 06:31 hour-rw ------- 1 huateng 17 M 03-30 06:31 upload-rw ------- 1 huateng 17 M 03-30 06:31 S0000014.LOG-rw ------- 1 huateng 17 M 03-30 06:31 hour-rw ------- 1 huateng 17 M 03-30 06:31 S0000016.LOG -rw ------- 1 huateng 17 M 03-30 06:31 hour-rw ------- 1 huateng 17 M 03-30 06:31 S0000018.LOG-rw ------- 1 huateng 17 M 03-30 06:31 hour-31 hour- rw ------- 1 huateng 17 M 03-30 06:31 s000001_log-rw ------- 1 huateng 17 M 03-30 06:31 hour-rw ------- 1 huateng 17 M 03-30 06:31 S0000022.LOG ------- 1 huateng 17 M 03-30 06:31 S0000023.LOG-rw ------- 1 huateng 512 03-30 06:31 SQLLPATH. the TAG does not seem to change the number of log groups because the current database configuration is in the archive mode. [huateng @ db2server SQLOGDIR] $ db2 get db cfg | grep LOG Catalog cache size (4 kb) (CATALOGCACHE_SZ) = 190 Log buffer size (4 kb) (LOGBUFSZ) = 256 Log file size (4 kb) (LOGFILSIZ) = 4096 Number of primary log files (LOGPRIMARY) = 3 Number of secondary log files (LOGSECOND) = 4 Changed path to log files (NEWLOGPATH) = Path to log files =/home/huateng/NODE0000/SQL00001/SQLOGDIR/Overflow log path (OVERFLOWLOGPATH) = Mirror log path (logical logpath) = First active log file = S0000011.LOG 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) = RECOVERY First log archive method (LOGARCHMETH1) = DISK: /tmp/archivelog/Options for logarchmeth1 (LOGARCHOPT1) = Second log archive method (LOGARCHMETH2) = OFF Options for logarchmeth2 (LOGARCHOPT2) = www.2cto.com Log pages during index build (LOGINDEXBUILD) = OFF [huateng @ db2server SQLOGDIR] $ db2 update db cfg using LOGARCHMETH1 offDB20000I 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 valid tive. [huateng @ db2server SQLOGDIR] $ db2 connect resetDB20000I The SQL command completed successfully. [huateng @ db2server SQLOGDIR] $ db2stop2012-03-30 06:36:36 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. [huateng @ db2server SQLOGDIR] $ db2start03/30/2012 06:36:40 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. [huateng @ db2server SQLOGDIR] $ db2 connect to dbtest Database Connection Information Database server = DB2/LINUX 9.7.0 SQL authorization ID = HUATENG Local database alias = DBTEST [huateng @ db2server SQLOGDIR] $ ls- lh total 49M-rw ------- 1 huateng 17 M 03-30 06:36 hour-rw ------- 1 huateng 17 M 03-30 06:36 S0000001.LOG-rw ------- 1 huateng 17 M 03-30 06:36 hour author: TOMSYAN