IBM Support

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.


The intent of Advanced Log Space Management (ALSM) is to make log space management in Db2 more autonomic.  Starting in Db2 Version 11.5, a technology preview of ALSM is available.  The technology preview of ALSM helps to avoid log full situations.

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

With ALSM, the Db2 transaction manager identifies long running transactions that could be causing a log full condition and extracts their log data from the active log files into separate extraction log files dedicated for the transaction.  This allows the original log files to be removed to free up disk space, so that new active log files can be created.


Only use the feature in non-production environments.  The feature is still missing elements whose absence can cause undefined results.  Specifically, the feature is missing support for:

  • 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.

How to enable:

To enable the feature, perform the following steps:

  • 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

Once enabled, ALSM launches a log extraction scan that can be monitored through one of the following monitor interfaces:

See the Knowledge Center for each of the above for further details on the new output related to ALSM and log extraction.

Log extraction is handled by a new EDU -- db2loggx.  When log extraction is taking place, new files appear in the active log path:

  • 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 extraction should have no or minimal impact to active workloads.  When log space consumption becomes high, extraction will begin to extract log data to reduce the chance of encountering transaction log full conditions.  The log data extracted is used for rollback, currently committed readers and crash recovery.  If  log extraction determines that there is no benefit to extracting data then the extraction scan goes idle waiting for conditions to re-occur for it to begin again.  As extracted data is no longer required, the extracted log files are removed.


If there is an error during the extraction scan process, the scan goes idle and waits for the appropriate time to begin again.  During this time where the extraction scan is idle, a transaction log full condition can occur.  In general, if a transaction log full error still occurs, even when the extraction is in progress, it can be because of any of the following:

  • 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.

An error with the log extraction scan is not deemed fatal to the database and regular database activity can continue.  Details from the log extraction scan can be found through the above-mentioned monitoring interfaces.
If there is an error during rollback or crash recovery trying to read log data from the extraction log files, then the required log data is retrieved from the archive log path and the read operation succeeds successfully.

If there is an error when a currently committed reader attempts to read log data from the extraction log files, then the scan resorts to standard lock wait behavior.


logging; log management; transaction log full; log space management

Document information

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