In DB2, when a database is created, 3 log files are allocated. They are known as primary log files. In Linux/Unix environment, they will be 1000 * 4K pages for each log file by default, whereas in Windows it is 250 * 4k pages.
By default, up to two secondary log files will be created if needed, and their size will equal that of each primary log file used. However, the total number of secondary log files allowed is also configurable (via the logsecond database configuration parameter).
You might think you can avoid running out of log space by configuring a database to use a large number of secondary log files. However, the maximum number of secondary log files allowed is 254. If the size of your log files is relatively small, you can still run out of log space quickly when transaction workloads become heavy. You should avoid allocating a large number of secondary log files, if possible, because performance is affected each time a log file has to be allocated. Ideally, you should allocate enough primary log files to handle most situations, then use just enough secondary log files to handle peak times in transaction workloads. If you're concerned about running out of log space and want to avoid allocating a large number of secondary log files, you can configure a database to use what is known as infinite logging. To enable infinite logging, simply set the database configuration parameters userexit and logsecond to YES and -1, respectively.
2 comments:
Word to the wise...NEVER EVER use Infinite Logging. Infinite Logging allows ACTIVE Log files needed for rollback and Active/Crash recovery to be treated like Archive Logs which are no longer needed to maintain the integrity of the database. The Active Log Files are then moved off the server to your Log Archive Storage device - which is typically tape. Once the Log files are moved out of the LOGPATH, they are subject to Expiration, accidental deletion, storage/tape media failures, etc. Any process you use to expire/delete Archive Logs can and probably will expire/delete these needed Active Logs.
If those Active log files are deleted, lost or corrupted and the Database needs them to rollback a transaction or complete a crash recovery, the Database WILL CEASE TO FUNCTION! There is no recovering from this. Your database is down and your data cannot be accessed.
There is NOTHING you can do to eliminate this risk - other than shutting off Infinite Logging.
Again, word to the wise - DON'T USE INFINITE LOGGING.
Hi HobieCopper, thanks for dropping a comment to my lonely blog.
I understand your concerns about the hazard of using infinite logging but I need to disagree that absolute prohibition of it is necessary.
This configuration exists for reasons and I think what's more critical is whether the DBA is as competent as you and me to understand the consequences of everything the do to their production system.
Post a Comment