10 questions to review and understand your active log data set configuration

By alain pary posted Aug 09, 2017 12:44 AM


10 questions to review and understand your active log data set configuration

Alain Pary, dbizners


The db2 logging process is an old strong process. You must take care of it because if all your active log data sets are not reusable because they are full, your db2 stops until it can offload them in archive logs. The performance of your recovery process is also strongly linked to your active log data set capacity.


1. Why do we need active logs in a DB2 Subsystem ?

  • For each insert DB2 save a REDO record in the active log data sets.
  • For each delete DB2 save an UNDO record in the active log data sets.
  • For each update DB2 save an UNDO and a REDO record in the active log data sets.
  • REDO records are needed in recovery processes to reapply all the data modifications after the backup reload until the RBA selected.
  • UNDO records are need in rollback processes to back out work until the start of the unit of work.
  • Active log data sets are periodically offloaded to archive log data sets.
  • More detailed informations can be find in your “Administration Guide” .


2. What increase your log data volume ?

  • Start using 10 bytes RBA/LRSNs
  • Data capture yes (a complete row stores for each UNDO or REDO records).
  • Index with copy yes.
  • An increase of your workload.
  • Switch to COMPRESS NO for objects.
  • Compression dictionary after objects reorganization.
  • Increase of the transactions COMMIT rate.
  • Rollback process ,the back out is also registered .


3. What decrease your log data volume ?

  • Compression of table (undo and redo records are also compressed).
  • Reordered Row Format (new default in V12)
  • Usage of utilities LOG NO for applicative features
    • Massive insert via utility
    • Cleaning with reorg discard.

4. Why increase your active log total size ?

  • Avoid allocation of archive log for recovery purpose .
  • Avoid sequential access of archive log and lose the added value of the syslgrnx range.
    • Archive log dataset can only be acceded sequentially, even if they are on disk.
    • Archive log on tape can’t be shared between concurrent recovery process (no parallelism)
  • The cost of some Gigabytes on disk versus the improvement of the recovery process.
  • Increase time to fix an archiving problem.
    • DB2 Messages
      • DSNJ115I: Archive Data Set Could Not Be Allocated
      • DSNJ110E: Last Active Log Data Set Is % Full
      • DSNJ111E: All Active Log Data Sets Full
        • If no more active log available DB2 stops

5. How to find information about active and archive log definitions ?

  • -DIS LOG
    • How many log defined.
    • Last checkpoint.
    • Information about archive log used for recovery process.
  • DSNUJ004
    • Print BSDS in a readable format.
    • BSDS is the repository of information about active and archive log.


6. How to know the elapsed time covered by your active logs ?

  • This method is based on information stored in the BSDS and is reactive.
  • We are based on DB2 BSDS V11 converted to 10 Bytes RBA without Datasharing.
000000000492E8C9E000    000000000492FEC2DFFF    2016.015    DSN=DB2XD01L.ARCHLOG1.D16015.T2200189.A0059463
2016.015  20:22:55.1    2016.015  21:00:18.8    22:00   PASSWORD=(NULL) VOL=PDBLT13 UNIT=3390
  • For each archlog1 entry, compute the delay (in seconds) between end time and start time
    • (2016.015  21:00:18.8 - 2016.015  20:22:55.1) = 2243 seconds
  • Make a moving sum over the number of logs active in your subsystem.
  • We can check how much time is covered or not for each limit(from 1 to 24 hours)


  • X value represents a time duration in hour
    • 8 represents that we want to know when 8 Hours are covered by active  log data sets.
  • Y value is the ratio of  hours covered by your actual active log data sets
    • Sample 9 hours are covered 56% of time.
  • We simulate the doubling of the number of logs (more easy to compute that implement).
  • The log 2X full covered times is near 10 hours. Doubling the log volume (by increasing number or size of the logs) results in tripling the full times covered.
  • The LOG 2X curve shift to the right with about 9 units.

7. How to add a new active log to my configuration ?

  • Define a VSAM linear data set
  • Run DSNJLOGF (preformat active log)
    • Issue twice for dual copy.
  • Limitation
    • 93 active log data sets
    • V10- V11
      • maximum size of 4 GB each
      • 372 GB maximum.
    • V12
      • Maximum size of 788 GB each
      • EA (extended addressability SMS data class)
      • 284 GB max (73 TB)
    • Log can also be added via program DSNUJ003 (change log inventory), this process is executed via a batch job in this case.

8. What are the point of interest when you increase the active logs total space ?

  • Adding new logs .
    • Increase the number of archive log allocations need need for a recovery process.
    • Decrease the availability of adding active logs in case of archiving problem.
  • Increase the size of active logs.
    • More commands need to apply on your BSDS (add & drop log).
    • Review ZPARM parameters.
    • Free space in Archive pool .
    • In V12 with the new size limitation you can easily make an increase.
      • Sample : Replace 40 logs of 2G by 10 logs of 20 G (from 80 G to 200 G)
  • Zparm of DSN6ARVP, DSN6LOGP macro must be reviewed.

9.How to decrease the impact of active log data sets volume on a DB2 Subsystem ?

  • Increase the frequency of full or incremental copy.
    • Decrease the log apply phase in the recovery process.
  • Increase your commit frequency.
    • Decrease the rollback process and avoid access on archive log data sets.
  • Adjust your checkpoint frequency.
    • The last checkpoint is use in case of DB2 Restart to rebuild the data coherency.
    • You must find a tradeoff between checkpoint process cost and restart DB2 time .
    • Checkpoint can be set :
      • The number of log records is specified by the CHKLOGR
      • The number of minutes is specified by the CHKMINS

10. What should be retained from this ?

  • Active logs are a major component in your db2 subsystem.
  • The data stored in active logs is continuously increasing.
  • Check your configuration regularly to keep your active log volume in accordance with you growing workload.