For those uninitiated to
what RTS are, they are a series of statistics that are automatically maintained
by DB2 and can be used by DBAs (and tools) to schedule maintenance tasks like
REORG and COPY.
Prior to the introduction of RTS, the only way to gather
statistics about DB2 database structures was by running the RUNSTATS utility.
RUNSTATS collects statistical information about DB2 database objects and stores
this data in the DB2 Catalog. There are two types of statistics collected by
RUNSTATS: data used by the optimizer to formulate efficient access paths and
data used by DBAs to determine when maintenance tasks should be run. The
optimization statistics are still required, and therefore, so is RUNSTATS. The
maintenance statistics, on the other hand, can be ignored and replaced by RTS.
RTS runs in the background and automatically updates statistics in
two system catalog tables (one for table space data and one for index data) as your
DB2 data is modified. This differs from RUNSTATS, which is a hands-on
administrative process. RTS is hands-off.
So what is new with RTS in DB2 12 for z/OS?
The most significant enhancement is the ability to maintain RTS
history using DB2’s system-time temporal capabilities. When history is enabled,
both SYSIBM.SYSINDEXSPACESTATS and SYSIBM.SYSTABLESPACESTATS will have a table
with "_H" on the end that have the same columns, with the same data types. But
the temporal relationship is not automatically enabled, you have to choose to
do so.
To enable the temporal relationship between a history table and
its associated catalog table you must issue an ALTER statement to “hook up” the
relationship. Here are the DDL statement for each of the RTS tables:
ALTER TABLE SYSIBM.SYSINDEXSPACESTATS
ADD VERSIONING
USE HISTORY TABLE SYSIBM.SYSIXSPACESTATS_H;
ALTER TABLE SYSIBM.SYSTABLESPACESTATS
ADD VERSIONING
USE HISTORY TABLE SYSIBM.SYSTABSPACESTATS_H;
Before turning on RTS history you should take care to build a
process for maintaining, consolidating and purging historical information.
Remember, that each time the STATSINT interval is reached, new RTS data will be
written, causing the old information to be written to the system time temporal
table. That history table will grow over time. You will want to have a plan for
how to manage that growth. Perhaps you will want to aggregate the data daily,
or weekly, or monthly. Or maybe you will just want to purge the data after a
period of time. At any rate, your strategy for managing this data should be
worked out before you decide to start collecting RTS history.
Why would you want to record historical RTS information? Well,
such details can be useful for analyzing activity and predicting future
requirements. With historical RTS you can see trends and might be able to
improve performance, maintenance tasks, and service to your end users. And, of
course, this means that you can write SQL time travel queries against the RTS
system catalog tables.
You can always turn off historical RTS collection by severing the temporal
relationship. This is accomplished by issuing ALTER TABLE specifying the DROP
VERSIONING clause on the appropriate table. For example:
ALTER TABLE SYSIBM.SYSTABLESPACESTATS
DROP VERSIONING;
At any rate, the ability to take advantage of DB2’s temporal capabilities
to capture RTS history is a powerful new feature of DB2 12 for z/OS that DBAs
and performance analysts will want to take advantage of.
One other DB2 12 change to RTS worth noting, is the addition of the
GETPAGES column to both table space and index space stats. This column is used
to record the number of GETPAGE requests for the table space since the last
time REORG was run (or since the object was created).
No comments:
Post a Comment