How SQL operations affect real-time statistics counters

Db2 updates its in-memory real-time statistics (RTS) counters for SQL operations such as INSERT, DELETE, and UPDATE, and externalizes the values to the RTS tables in the catalog at a specified interval and for certain events.

The following table shows the real-time statistics counters that Db2 updates for a specific types of SQL statements.

SQL statement issued	Incremented counters
UPDATE	Update
INSERT	Insert
DELETE	Delete
ROLLBACK (or implicit rollback occurs)	Depends on the type of rolled-back SQL statements: Update counters, for rollback of UPDATE statements Delete counters, for rollback of INSERT statements Insert counters, for rollback of DELETE statements

At the interval specified by the STATSINT subsystem parameter or at certain events, Db2 externalizes the RTS counter values to the counter columns in SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSTABLESPACESTATS catalog tables, and calculates values for the totals columns, TOTALROWS and TOTALENTRIES, based on the counters. For more information, see When Db2 externalizes real-time statistics.

If an insert, update, or delete counter column value in the catalog is 2147483647, the actual number of inserts, updates, or deletes might be greater than this value. However, this limitation does not apply to the calculated totals column values. For more information, see Accuracy of real-time statistics.

UPDATE of partitioning keys

Action	Incremented counters
When UPDATE is executed	Update count of old partition = +1 Insert count of new partition = +1
When UPDATE is committed	Delete count of old partition = +1
When UPDATE is rolled back	Update count of old partition = +1 (compensation log record) Delete count of new partition = +1 (remove inserted record)

If an update to a partitioning key does not cause rows to move to a new partition, the counts are accumulated as expected:

Action	Incremented counters
When UPDATE is executed	Update count of current partition = +1 NEAR/FAR indirect reference count = +1 (if overflow occurred)
When UPDATE is rolled back	Update count of current partition = +1 (compensation log record)