Skip to content

Db2 Daily Health Checks to Ensure Smooth Database Operation

November 19, 2026

If you think about it, there are only a few areas of Db2 that you need to check/monitor on a daily basis. If done correctly, your databases will run smooth with few surprises albeit an exception is if the Application or new SQL is introduced and hasn’t been reviewed by the DBA. Note: This is predicated on all settings left set to AUTOMATIC with the exception of INSTANCE_MEMORY if running multiple instances on the same server , LPAR or VM (in this case set INSTANCE_MEMORY to a value and leave DATABASE_MEMORY set to AUTOMATIC. For our clients, we look at the following on a daily basis to ensure smooth operation:

  1. Check the db2diag.log for errors
    1. Command — db2diag -l severe, db2diag -l warning
    2. Take action if indicated by the error
  2. Check the DIAGPATH for dumps or traps
    1. Investigate any dumps or traps and take action if necessary
    2. Not all dumps or traps require action, could have been generated by a user closing a connection or something similar
  3. Check CPU and Memory usage at OS level
    1. Report if outside average daily usage (you determine this over time by monitoring)
    2. Use dmctop/db2top to identify top resource users, investigate further if usage is suspect
  4. Check Db2 log usage
    1. Are secondary logs being used?
    2. Is the log usage increasing?
    3. If secondary logs being used and log usage increasing use dmctop/db2top to find the application that is causing high log usage and coordinate with applications to see if new sql has been introduced or if a large run such as end of month, quarter or year is being run causing high log space usage. If above not applicable then likely there is an application not committing or in a loop requiring further investigation and cancellation if necessary.
    4. Secondary logs should not be routinely used as there is more overhead with them, ensure primary logs can handle most normal workload
  5. Locking Problems — One of the main areas where problems occur in most production databases due to bad application sequencing, wrong isolation level being used or applications not committing often enough
    1. Check # of deadlocks — this should be a low number if not 0.
    2. Check # of locktimeouts — it is normal in most databases to have some locktimeouts as part of routine application processing, in order to determine what is normal, you must monitor the database over time to determine what is normal for the database in question and then investigate if higher than normal.
    3. Monitor with db2pd -d dbname -locks, db2pd -d dbname -lockwait or via mon_get routine or dmctop/db2top
    4. If more than normal locktimeouts or deadlocks occurring, find the offending agent and work with the application development team to resolve.
    5. Have an active LOCKING event monitor for every database and use db2evmonfmt to format a report and analyze it and work with developers to resolve.
  6. Space — Although not a primary DBA function make sure that the filesystems assigned to Db2 tablespaces is adequate and alert system administrators if space used exceeds 80%. Monitor log space filesystem space and alert system administrators if space used exceeds 50%.
    1. Make sure automatic storage is being used! Convert user SMS tablespaces to automatic storage as this has been deprecated for several releases.
  7. Backups — Make sure daily backups are being used and completed successfully.
    1. Command — db2diag -g message:=”Backup complete”
    2. Or use db2 list history backup since xxxxxxx for database dbname
  8. Suboptimal SQL
    1. Use dmctop/db2top, monreport, or mon_get routine to identify the top 10 SQL
      1. My favorite is db2 call monreport.currentsql and pkgcache
      2. Get the executable ID and use explain_from_section and db2exfmt to format the SQL into an Explain
      3. Review and work with developers to improve
      4. Use design advisor for possible index improvement
      5. Repeat this cycle as there will always be a top 10 list of SQL as you fix one a new one will take its place

In summary, if you do these daily health checks your databases will run smoothly and you will have few surprises. There are other commands and tools to use other than those cited above, use whatever works for you. And yes, the daily checks can all be scripted and notifications sent to you, the DBA. Now, this list of daily checks is subjective — this is what my team and I do on a daily basis for our clients and our databases run “smooth”.

From → Uncategorized

Leave a Comment

Leave a Reply