Db2 active log path may become exhausted/full due to archived log files not being removed in v11.1.3.3, when db2ReadLog in use

Content

This problem only affects Db2 Versions 11.1.3.3 and 11.1.3.3.ifix001 only.

When the db2ReadLog() API is used to retrieve log data from the database (such as part of a replication solution, like IBM Infosphere QReplication, IBM Infosphere CDC, etc), and a database is configured for log archival (LOGARCHMETH1 and/or LOGARCHMETH2), old log files might not be removed from the database active log path.  This may cause the eventually exhaustion of the filesystem where the database active log path resides and thus the effective inability to use the database.
 

To avoid the issue

Set the DB2_USE_BUFFERED_READ_FOR_ACTIVE_LOG registry variable to NO:

   $  db2set DB2_USE_BUFFERED_READ_FOR_ACTIVE_LOG = NO 
(The database must be deactivated and reactivated for this setting to take affect.)

By setting this registry variable to NO, a performance improvement that was implemented in v11.1.3.3 for rollback processing of large units of work will be disabled, and the performance of rollback processing will remain at the level of v11.1.2.2 and prior releases.

Note, the problem is fixed in Version 11.1.4.4 via APAR IT25556, and the performance improvement can be used safely in Version 11.1.4.4 and future releases, by explicitly enabling: 

   $ db2set DB2_USE_BUFFERED_READ_FOR_ACTIVE_LOG = YES
(The database must be deactivated and reactivated for this setting to take affect.)

To determine if a database was affected by the issue:

1) Determine the log file that was most recently successfully archived, by subtracting 1 from the 'Method 1 Next Log to Archive' value (or, if both LOGARCHMETH1 and LOGARCHMETH2 methods are defined, subtract 1 from the lower of both 'Method 1 Next Log to Archive' and 'Method 2 Next Log to Archive' values).  In this example log file 35 is the most recently successfully archived log file.

  $ db2pd -db MYDBNAME -logs

  Database Member 0 -- Database MYDBNAME  -- Active -- Up 0 days 00:19:24 

  Logs:
  Current Log Number            36
  Pages Written                 750
  Cur Commit Disk Log Reads     0
  Cur Commit Total Log Reads    0
  Method 1 Archive Status       Success
  Method 1 Next Log to Archive  36
  Method 1 First Failure        n/a
  Method 2 Archive Status       n/a
  Method 2 Next Log to Archive  n/a
  Method 2 First Failure        n/a
  Log Chain ID                  0
  Current LSO                   198151822
  Current LSN                   0x000000000022DACD

2) Determine the current first active log file required for database crash recovery.  In this example, log file 23 is the first active log.

  $ db2 get db config for logarch | grep "First active log file"
    First active log file                = S0000023.LOG

3) Navigate to the active log path for the database.

  $  db2 get db config for MYDBNAME | grep "Path to log files"
    Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00002/LOGSTREAM0000/

  $  cd /home/db2inst1/db2inst1/NODE0000/SQL00002/LOGSTREAM0000/

4) Within the active log path, look for any log files which are older then or equal to the log file which was most recently successfully archived (log file 35 in this example), and also older then the current first active log file (log file 23 in this example). 

   $ ls -l
  total 106808
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:21 S0000001.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:21 S0000002.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:21 S0000005.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:24 S0000019.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:24 S0000020.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:24 S0000021.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:24 S0000022.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:24 S0000023.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:24 S0000024.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:24 S0000025.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000026.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000027.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000028.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000029.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000030.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000031.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000032.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000033.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000034.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000035.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000036.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000037.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000038.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000039.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000040.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000041.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000042.LOG
  -rw------- 1 db2inst1 pdxdb2 4202496 Jun 27 11:25 S0000043.LOG

5) In this example log files S0000001.LOG, S0000002.LOG, S0000005.LOG, and S0000019.LOG through S0000022.LOG fit the criteria. These log files were successfully archived and are older the current first active log file required for crash recovery, and thus are candidates for removal.  Please see instructions below to rectify the issue.

To rectify the issue, if the database was affected by it:

option 1) Deactivate and re-activate the database.  Db2 will automatically cleanup these unnecessary log files during re-activation.

   $ db2 deactivate db MYDBNAME
        (the FORCE option can also be used to force off any existing database connections).
   $ db2 activate db MYDBNAME

option 2) Manually move these log files out of the active log path:

2.1) Shut down any replication solutions (like IBM Infosphere QReplication, IBM Infosphere CDC, etc) that might be reading older log data within the set of log files identified as candidates for removal above. Also, deactivate any HADR standby database(s) if they are in REMOTE_CATCHUP or REMOTE_CATCHUP_PENDING states and are processing older log data within the set of log files identified as candidates for removal above.

2.2) Move log files which are candidates for removal to another staging filesystem, for eventual removal. Caution, never delete log files, always move them and allow aging before deleting them in the future when there is high confidence that they are no longer needed for recovery. In this example, I move all log files of 22 and smaller to another filesystem, for eventual removal.

   $ mv S0000001.LOG /some_other_filesystem/
   $ mv S0000002.LOG /some_other_filesystem/
   $ mv S0000003.LOG /some_other_filesystem/
   ...etc...
   $ mv S0000022.LOG /some_other_filesystem/