Db2 Version 11.5 - Advanced Log Space Management - Tech Preview
Technical Blog Post
With Db2 Version 11.5 the journey has begun to make log space management more autonomic. Reduce your likelihood of hitting transaction log full conditions with Advanced Log Space Management (ALSM), which is a technology preview and not supported for production environments until further notice.
ALSM looks to assist with these known customer issues:
- Applications failing with a log full (SQL0964N) error due to some long running transaction holding back the active log. In particular, when such a long running transaction does not generate much transaction log data (in comparison with other well behaving transactions running concurrently). Examples of long running transactions that can cause log full conditions in other applications are:
- A LOAD operation (including ADMIN_MOVE_TABLE that makes use of LOAD)
- A CREATE INDEX operation
- An indoubt transaction
- A poorly written application that sits idle (i.e. forgetting to issue COMMIT or ROLLBACK) after making some change to the database
- Databases configured with circular or log retain logging (LOGARCHMETH1/2)
- Databases configured with mirrored logging (MIRRORLOGPATH)
- Databases configured with the High Availability and Disaster Recovery (HADR) feature
- Databases in pureScale environments
- Disk space: ALSM consumes additional disk space (to hold the extraction log files). ALSM works best when there is more disk space than what is required by the configured active log space (LOGPRIMARY, LOGSECOND, LOGFILSIZ database configuration parameters). We recommend having at least 20% more disk space than the configured amount.
- Online backup: By default online backup operations include active log files for recovering to the end of backup. Extraction log files are NOT included in backup images. With extraction, this could increase the range of active log files that needs to be included in the backup image. Thus, backup images could be larger in size and take longer.
- Crash recovery: The crash recovery redo phase does not make use of extraction log files, thus log retrieval might be needed for any log files not found locally. The undo phase makes use of the extraction log files, but performance might not be optimal. Configuring your overflow log path to point to your disk archiving log path may provide some performance improvements.
- Restore and rollforward: Restore and rollforward deletes all extraction log files and thus rollforward retrieves any log files needed for the recovery.
- Install Db2 Version 11.5 GA
- Configure your database to use log archiving (see LOGARHMETH1/2)
- Set the registry variable DB2_ADVANCED_LOG_SPACE_MGMT=ON
- Deactivate and reactivate the database for the change to take effect
See the Knowledge Center for each of the above for further details on the new output related to ALSM and log extraction.
- X<log file number>_TID<transactionId>.LOG - Extraction transaction ID (TID) file. This file contains extracted log records for a specific transaction used by rollback, currently committed and crash recovery operations. One TID file exists for each active log file where log data is extracted for a transaction ID.
- X<log file number>.TMP - Extraction meta data about extracted logs created during an in-progress extraction for an active log file.
- X<log file number>.META - Extraction meta data about extracted logs created after extraction completes for an active log file.
- Log archiving is not healthy: Log data from the active log files that is not archived yet is not extracted. Ensure log archiving is healthy and/or a FAILARCHPATH is configured.
- Buffer pool flushing is slow: Log data from the active log files that is at or above what has been flushed from the buffer pools is not extracted. Ensure PAGE_AGE_TRGT_MCR and PAGE_AGE_TRGT_GCR (or SOFTMAX on older databases configurations) are set to appropriate values based on your workload throughput.
- Extraction is slow: It is possible that log writing is faster than log extraction or log extraction has triggered too slowly. In these cases, the feature might not be performing optimally. It is advised to use the monitoring interfaces to self diagnose, or contact IBM Service for analysis.
logging; log management; transaction log full; log space management
More support for:
DB2 for Linux, UNIX and Windows
Component: Recovery, Availability
Software version: 11.5
Operating system(s): Platform Independent
Reference #: 0886243
Modified date: 26 June 2019