linux db2 修改日志路径,db2中更改日志文件大小及其个数

db2中更改日志文件大小及其个数比较简单，只需要修改2个参数即可，比ORACLE方便的多。

默认日志文件放在SQLOGDIR目录下。

[huateng@db2server SQLOGDIR]$ pwd

/home/huateng/huateng/NODE0000/SQL00001/SQLOGDIR

[huateng@db2server SQLOGDIR]$ ls -lh

总计 53M

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000011.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000012.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000013.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000014.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000015.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000016.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000017.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000018.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000019.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000020.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000021.LOG

-rw------- 1 huateng huateng 4.1M 03-30 06:26 S0000022.LOG

-rw------- 1 huateng huateng 4.1M 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 SQLOGDIR]$ db2 get db cfg | grep LOGFILSIZ

Log file size (4KB)                         (LOGFILSIZ) = 1024

当前日志文件数为13，大小大约为4M。

下面将日志文件数改为3个，大小为16M。

[huateng@db2server SQLOGDIR]$ db2 update db cfg using LOGPRIMARY 3SQL5153N  The update cannot be completed because the following relationship

would be violated: "softmax <= 100 * logprimary".

[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 200

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.

[huateng@db2server SQLOGDIR]$ db2 update db cfg using LOGPRIMARY 3

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.

[huateng@db2server SQLOGDIR]$ db2 update db cfg using LOGFILSIZ 4096

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.

[huateng@db2server SQLOGDIR]$ db2 force applications all

DB20000I  The FORCE APPLICATION command completed successfully.

DB21024I  This command is asynchronous and may not be effective immediately.

[huateng@db2server SQLOGDIR]$ db2stop

2012-03-30 06:34:01     0   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

[huateng@db2server SQLOGDIR]$ ls -lh

总计 209M

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000011.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000012.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000013.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000014.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000015.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000016.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000017.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000018.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000019.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000020.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000021.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000022.LOG

-rw------- 1 huateng huateng 17M 03-30 06:31 S0000023.LOG

-rw------- 1 huateng huateng 512 03-30 06:31 SQLLPATH.TAG

不过日志组数看起来没有改变，这是因为当前的数据库配置在归档模式.

[huateng@db2server SQLOGDIR]$ db2 get db cfg | grep LOG Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 190

Log buffer size (4KB)                        (LOGBUFSZ) = 256

Log file size (4KB)                         (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/huateng/NODE0000/SQL00001/SQLOGDIR/

Overflow log path                     (OVERFLOWLOGPATH) =

Mirror log path                         (MIRRORLOGPATH) =

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) =

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

effective.

[huateng@db2server SQLOGDIR]$ db2 connect reset

DB20000I  The SQL command completed successfully.

[huateng@db2server SQLOGDIR]$ db2stop2012-03-30 06:36:36     0   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

总计 49M

-rw------- 1 huateng huateng 17M 03-30 06:36 S0000000.LOG

-rw------- 1 huateng huateng 17M 03-30 06:36 S0000001.LOG

-rw------- 1 huateng huateng 17M 03-30 06:36 S0000002.LOG