This tutorial explains about one of the db2 database configuration parameter LOGFILSIZ and How to decide and change the log file size ? . LOGFILSIZ defines the size of transaction log file that means the size of each primary and secondary log file. The default size is 1000 pages where each page having 4 K size that means log file can hold upto 4 MB of transactional data . The size of these log files limits the number of log records that can be written to them . When they become full , a new log file is required. If the database has a large number of transactions like update, delete or insert (i.e. in a high transaction OLTP environment) , the size of the log file should be larger because small size log files will be filled very quickly and new log files would have to be created frequently and also has to wait for a usable log file. This can affect system performance . At the same time ,the value of the logfilsiz should be reduced if disk space is less, since primary logs are preallocated at the size mentioned . In log retention loging , the current active log file is closed and truncated when the application disconnects from a database. When the database is reconnected , the next log file is used. Therefore, if we use very large log file , there may be lot of waste space . Conclusion : Make understand the logging requirements (maximum size of uncommitted changes at any time) of your concurrent applications , and determine a log file size . Neither it should be too small nor it should be too large.
To get the exisiting LOGFILSIZ of a particular database , please issue the following command
db2 get db cfg for sales where sales is the database name.
The line related to LOGFILSIZ is as follows
Log file size (4KB) (LOGFILSIZ) = 1000
To change the LOGFILSIZ parameter , please issue the following command
db2 update db cfg for sales using logfilsiz 2000 ,where "logfilsiz" should be in the range of "4" to "262144".
where 2000 is the no of pages . so the log file size will be 2000*4 KB = 8 MB
In older versions , you can configure the upper limit of 256 GB of active log space. Let us see , how it is calculated.
Maximum pages allowed are 262144 .
So the upper limit of the each log file size will be 262144 * 4 KB = 1048576 KB = 1024 MB = 1 GB , where 4 KB is the size of each page.
Maximum combined number of primary and secondary log files is 256
So upper limit of active log space will be 256 * 1 GB = 256 GB.
In DB2 version 9.0 , upper limit of active log space 512. here upper limit for each log file, which is 2 GB
Starting from Version 9.5 Fix Pack 3 to latest version 9.7 , there is now 1024 GB of active log space available.
Note : When a log file becomes full , the action taken on primary and secondary log files depends on the type of logging performed . In Circular logging , primary log file can be reused when the data recorded in it have been committed. If the log file size is small , when the uncommitted data / changes in the log file exceeds log file size, the next primary log file is used . If all primary log files become full, the database manager will allocate secondary log files to hold the new log records. When all the primary and secondary log files are full (uncommitted data size exceeds all log file size) , the error "the transaction log is full " occurs. To solve the error , please read my next post Solution to Transaction log full . In Log retention logging , When a primary log file is full, the log is archived and a new primary log file is allocated.
0 comments:
Post a Comment