Techie Tip – Real Time Statistics (RTS) feature

If you make use of RTS and DEFINE NO, you might want to review your processes to ensure that you’re picking up all of the tablespaces you should be……

Since V7 of DB2 for z/OS, IBM has provided a Real Time Statistics (RTS) feature, which is a set of tables that DB2 keeps (more or less) up to date with recent activity against all tables and indexes in the system. In particular, the RTS tables provide detailed information on the amount of update activity that has happened since a given object was last REORGed or had RUNSTATS executed against it. That information is obviously very useful when determining when those utilities need to be run next, so many sites base their housekeeping schedules on this information (either using their own bespoke code and procedures, or a vendor tool such as IBM’s DB2 Automation Tool). In particular, looking at the number of INSERT/UPDATE/DELETE operations as a proportion of the total rows in the table (using the TOTALROWS column) is useful, so you can reorg a table when more than 10% of the rows have been changed, for example.

We came across a problem recently, where one of our customers was using this feature in a DB2 9 for z/OS subsystem that had lots of DEFINE NO tablespaces (which instructs DB2 to defer creating the underlying VSAM dataset until the table is actually referenced – very handy for ERP systems such as SAP that create a large number of tables that are never used in a given environment).

For normal DEFINE YES tablespaces, a row is created in the RTS tables as soon as the tablespace is created, with the statistics initially set to zero values and updated in real time to reflect activity against the underlying table. Unfortunately, the situation is different for DEFINE NO tablespaces: the row isn’t created until the VSAM dataset is defined on first use (no problem there) but instead of zeroes some fields (including the number of rows in the TOTALROWS column) are set to null instead. That null value isn’t updated until the first REORG or LOAD is run against the table, so if the table is populated via INSERT rather than LOAD, your nice RTS monitoring routines may never trigger that first REORG!

This “feature” has been reported to IBM, but in the meantime if you make use of RTS and DEFINE NO, you might want to review your processes to ensure that you’re picking up all of the tablespaces you should be……

Share
This entry was posted in DB2, DB2 9, DB2 Support, IBM, System Z and tagged , , , , , , , . Bookmark the permalink.