Maintaining object tracking tables

Detail and summary object tracking tables should be maintained on a regular basis.

Detailed object tracking data about repository based objects is stored in the RSBI.OBJ_ACTIVITY_DTL table and detailed object tracking data about QMF catalog based objects is stored in the Q.OBJ_ACTIVITY_DTL table. Summarized object tracking data about repository based objects is stored in the RSBI.OBJ_ACTIVITY_SUMM table and summarized object tracking data about QMF catalog based objects is stored in the Q.OBJ_ACTIVITY_SUMM table.

A row is added to the Q.OBJ_ACTIVITY_DTL table and the RSBI.OBJ_ACTIVITY_DTL for each use of an object. For example, if a user executes a query (object) 20 times, 20 rows are added to the table.

Only one row is added to the summary tables Q.OBJ_ACTIVITY_SUMM and RSBI.OBJ_ACTIVITY_SUMM for each object regardless of the amount of times the object is used. So in this example, the object usage count, a field in the table, would increase by 20, but no new rows are added. Rows are added to the summary table only when a new object is created and rows are automatically deleted when an object is deleted.

Since the summary tables will not grow without boundaries and the detail tables will, it is more important to perform maintenance on the detail tables with greater frequency than the summary tables. The following are recommendations for optimum performance of the Q.OBJ_ACTIVITY_DTL table and RSBI.OBJ_ACTIVITY_DTL detail tables:

Run RUNSTATS for the table space and index.
Delete old data periodically.
For example, for a Db2® database, you can run this query to delete all rows older than 30 days from the QMF catalog object detail table (if your user ID has permission to access the summary and detail tables):
```
DELETE FROM Q.OBJ_ACTIVITY_DTL WHERE "DATE"  (CURRENT DATE - 30 DAYS)
```
For a Db2 database, you can run the following query to delete all rows older than 30 days from the repository object detail table (if your user ID has permission to access the summary and detail tables).
If you are using Db2 for LUW:
```
DELETE FROM RSBI.OBJ_ACTIVITY_DTL WHERE 
( DAYS ( CURRENT DATE ) - DAYS ( DATE ('1970001'))) - ETIME/3600/24/1000 >30
```
If you are using Db2 for z/OS:
```
DELETE FROM RSBI.OBJ_ACTIVITY_DTL WHERE
CAST(( DAYS ( CURRENT DATE ) - DAYS ( DATE ('1970001'))) AS BIGINT ) 
*86400000 - ETIME > 2592000000
```
For Db2 for z/OS®, perform maintenance on the repository object detail table and its associated auxiliary table (RSBI.ACTIVITY_LOB_DATA) and index (RSBI.IACTIVITY_LOB_DATA). For Db2 for z/OS, perform maintenance on the QMF catalog object detail table and its associated table space (RAADB.RAAIX2 ) and index (Q.RAAIX2). Q.RAAIX2 is a default name that can be changed by the database administrator when the QMF catalog is created.

Note: The first-used, last-used and last-modified summary statistics stored in the summary table are not affected when you delete detailed data.