How Db2 maintains in-memory statistics in data sharing

In data sharing environments, each member subsystem sets it own interval for writing in-memory statistics to real-time statistics tables. However, certain utilities and SQL statements in one member might invalidate the in-memory statistics of other members.

Certain utilities and SQL statements can invalidate the in-memory statistics of other members of a data sharing group. For example, such utilities and statements include:

  • The RUNSTATS utility
  • The REORG utility
  • The LOAD utility
  • The BACKUP SYSTEM utility
  • Any utility or SQL statement that resets page sets to empty, such as mass delete operations and drop table statements

Therefore, all members must externalize their statistics to the real-time statistics tables and reset their in-memory statistics, at the beginning of any such utility job.

Each member updates their statistics in a serial process, by completing the following actions:

  1. Acquires a row lock on the statistics table.
  2. Reads the target row from the statistics table.
  3. Aggregates the in-memory statistics.
  4. Updates the statistics table with the new total values.

Similarly, when a page set is reset to empty, the member that resets a page set notifies the other members. The in-memory statistics for the page set are invalidated in all members.

However, the utilities do not fail if all statistics cannot be externalized, or the empty-page notification process fails.

The NULL value indicates that the statistics are unknown.

In data sharing environments, the values in SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS can be negative for short periods of time. These negative values can occur, because individual subsystems externalize the statistics at different intervals. For example, consider the following situation:

  1. You have an empty table space.
  2. You insert 1000 rows on member A.
  3. You delete these 1000 rows on member B.
  4. If member B externalizes the in-memory statistics before member A does, the value of the TOTALROWS column in SYSIBM.SYSTABLESPACESTATS is -1000.
  5. After member A has externalized the in-memory statistics, the value of the TOTALROWS column in SYSIBM.SYSTABLESPACESTATS is 0.