Understanding and Tuning Your Packaged Applications › Monitoring and Maintaining Your Objects › Real-Time Statistics

Real-Time Statistics

You can use DB2's ability to collect statistics in real time to help monitor the activity against your packaged application objects. Real-time statistics lets DB2 collect statistics on table spaces and index spaces and periodically write this information to two user-defined tables. Beginning with DB2 9, these tables are an integral part of the system catalog. User-written queries and programs, or a DB2-supplied stored procedure, or Control Center, can use the statistics to make decisions for object maintenance.

DB2 constantly collects statistics for database objects and keeps the statistics in virtual storage. The statistics are calculated and updated asynchronously during externalization. To externalize the statistics, the environment must be properly set up. A new set of DB2 objects must be created to let DB2 write the statistics. SDSNSAMP(DSNTESS) contains the information necessary to set up these objects.

You must create two tables, with appropriate indexes, to hold the statistics:

• SYSIBM.TABLESPACESTATS
• SYSIBM.INDEXSPACESTATS

These tables are kept in a database named DSNRTSDB, which must be started to externalize the statistics being held in virtual storage. DB2 populates the tables with one row per table space or index space, or one row per partition. For tables that are shared in a data-sharing environment, each member writes its own statistics to the RTS tables.

Some of the important statistics that are collected for table spaces include:

• Total number of rows
• Number of active pages
• Time of last COPY, REORG, or RUNSTATS execution

Some statistics that may help determine when a REORG is needed include:

• Space allocated
• Extents
• Number of inserts, updates, or deletes (singleton or mass) since the last REORG or LOAD REPLACE
• Number of unclustered inserts
• Number of disorganized LOBs
• Number of overflow records that are created since the last REORG

The number of inserts, updates, and single or mass deletes since the last RUNSTATS execution can help determine when to execute RUNSTATS.

Statistics that are collected to help with COPY determination include distinct updated pages and changes since the last COPY execution and the RBA/LRSN of first update since the last COPY.

DB2 gathers the following statistics on indexes:

• Total number of unique or duplicate entries
• Number or levels
• Number of active pages
• Space allocated and extents

The time when the last REBUILD, REORG, or LOAD REPLACE occurred can help determine when a REORG is needed.

DB2 gathers statistics since the last REORG or REBUILD to determine how our data physically looks after certain processes occur, for example, batch inserts, so you can take appropriate actions if necessary. These statistics include:

• The number of updates
• The number of deletes, real or pseudo, single or mass
• The number of inserts, random inserts and inserts after the highest key

Following are the processes that have an effect on the real-time statistics:

• SQL
• Utilities
• Deleting objects
• Creating objects

After the tables are externalized, you can write queries against the tables.

Example: Query to identify page changes since the last image copy

This example shows how to identify when a tablespace has to be copied because more than 30 percent of the pages changed since the last image copy was taken.

SELECT NAME
  FROM SYSIBM.SYSTABLESPACESTATS
    WHERE DBNAME = 'DB1' and
    ((COPYUPDATEDPAGES*100)/NACTIVE)>30

Example: Determine when RUNSTATS is needed

This example compares the last RUNSTATS timestamp to the timestamp of the last REORG on the same object. If the date of the last REORG is more recent than the last RUNSTATS, it may be time to execute RUNSTATS.

SELECT NAME
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = 'DB1' and
   (JULIAN_DAY(REORGLASTTIME)>JULIAN_DAY(STATSLASTTIME))

Example: Determine if you have to run REORG after a series of inserts

This example may be useful to monitor the number of records that were inserted since the last REORG or LOAD REPLACE that are not well-clustered with respect to the clustering index. Well-clustered means that the record was inserted into a page that was within 16 pages of the ideal candidate page (determined by the clustering index). You can use the SYSTABLESPACESTATS table value REORGUNCLUSTINS to determine if you have to run REORG after a series of inserts.

SELECT NAME
FROM SYSIBM.SYSTABLESPACESTATS
WHERE DBNAME = 'DB1' and
   ((REORGUNCLUSTINS*100)/TOTALROWS)>10

DB2 includes a stored procedure, DSNACCOR, to help with this process, and possibly even work toward automating the whole determination/utility execution process. DSNACCOR is a sample procedure that queries the RTS tables to determine which objects:

• Need to be reorganized, image copied, and updated with current statistics
• Have taken too many extents
• May be in a restricted status

DSNACCOR creates and uses its own declared temporary tables and must run in a WLM address space. The output of the stored procedure provides recommendations by using a predetermined set of criteria in formulas that use the RTS and user input for their calculations. DSNACCOR can make recommendations for COPY, REORG, RUNSTATS, EXTENTS, RESTRICT, or for one or more of your choices and for specific object types, such as table spaces and indexes.