Referential Integrity for Real-time Statistics Tables (RI for RTS)

OVERVIEW:

The RTS tables do not have DB2-enforced RI.

The DB2 component that handles the maintenance of this data can encounter certain failures that can result in obsolete/invalid/orphaned data in the table(s). By the way, this is more prevalent in a DS environment, as there is some level of coordination between the members when the in-memory data is externalized.

As a result, you wind up having orphaned rows in the table(s), as well as some values which are inaccurate.

PROBLEM:

I am mainly concerned about the orphaned rows. The orphaned rows cause: extra memory to store by the RTS component, extra online storage for the data/index, occasional confusion by DBAs/developers (for instance, the true key for systablespacestats includes OBID and INSTANCE), additional (trickier) SQL coding to ensure proper selection of the data.

CURRENT WORKAROUND:

We are currently working on a process (SQL-based) to remedy our current situation.

Unlike just about all of the other catalog tables, the RTS tables can be manipulated by SQL via INSERT, DELETE, UPDATE. The only DB2-enforced restrictions are: UNIQUE constraint on key components, column-level constraint on INSTANCE.

PROPOSAL:

My proposal is that IBM supply customers with a program that would report on the referential integrity of the data in RTS, and optionally remove orphaned rows.

USERS IMPACTED:

I believe that a high percentage of DB2 customers are impacted by this, and could benefit from such a program/tool. Most are probably unaware, and it is brought to their attention when they are faced with a reason to closely examine the RTS contents.

Most people I've discussed this with believe that since these tables are part of the catalog that DB2 will handle the RI. Originally users were not allowed to directly update any catalog tables. Then, there was good reason to allow users to UPDATE some tables, and only some columns. These tables, however, are fully INSERTable/DELETEable via SQL.