Foglight DB2 "Lock Waits" Mismatch with DB2 Native Command (4380573)

Sometimes, the "Lock Waits" value shown in Foglight for DB2 is different from what you see when running a DB2 command like `db2 get snapshot for database`. This article explains why these numbers may not match, how the data is collected, and how long Foglight keeps the historical lock information.

There are three main reasons for differences between values shown in Foglight and DB2 native commands:

1. How Data Is Collected:

• DB2 Command: 
  When you run `db2 get snapshot for database` after a `reset monitor`, the value you see (for example, "Lock waits = 94") is the total number of lock waits that have occurred since the last reset.
• Foglight:
  Foglight collects lock wait information at fixed intervals (for example, every 5–15 minutes) by polling the database using the `MON_GET_DATABASE` function. Each time it polls, it records the current cumulative value since the last reset.

2. How Data Is Displayed:

• DB2 Command:
  The snapshot shows the total lock waits since the last reset.
• Foglight: 
  In the UI, Foglight displays the values collected at each polling time. If you select a historical time range (for example, 12:00–13:00), Foglight will usually show the value from the last poll in that range (which should be the cumulative total since the last reset if there was no reset in between). If there is a problem with data collection or the agent, or if no data was collected (for example, due to retention policy), you may see a zero.

3. Retention Policy for Historical Data:

• By default, Foglight only keeps historical lock wait data for 3 days in the tables `DB2_History_Locks_Wait` and `DB2_Partition_History_Locks_Wait`.
• After 3 days, this data is automatically purged, so you cannot see or query lock wait history older than 3 days in the Foglight UI or reports.

Understanding What You See in Foglight

• If you reset the DB2 monitor at 12:00, any lock waits after that time will be counted from zero.
• Foglight polls and records the cumulative lock waits at regular intervals. By the end of your hour (13:00), the last recorded value should match the DB2 snapshot if no resets happened in between and if Foglight is collecting correctly.
• If you check Foglight for the same time window more than 3 days later, the data will not be available due to the retention policy.

What to Do If You See a Mismatch

• Make sure you are looking at data that is less than 3 days old.
• Confirm that the Foglight agent was running and collecting data during your time window.
• Remember that Foglight’s values are based on the polling interval. If a reset happened between polls, the numbers may be lower or zero.
• If you need to keep historical data for longer, consider adjusting the retention policy (may require more storage).

Example Scenario

• You reset the DB2 monitor at 12:00.
• Between 12:00 and 13:00, 94 lock waits occurred.
• You check DB2 at 13:00: "Lock waits = 94.
• You check Foglight for 12:00–13:00, but do this more than 3 days later.

Result: Foglight shows zero, because the data has been purged.

Summary

• Foglight collects lock wait data at intervals and records the cumulative value since the last reset.
• The default retention for historical lock wait data is 3 days; after that, it is deleted.
• If you look for data outside this 3-day window, you will not see any values, even if there was activity.
• Always check within 3 days if you need to compare with DB2 snapshot results.