Identifying the statements that affect a table

Use usage lists to identify DML statement sections that affect a particular table when the statement sections execute. You can view statistics for each statement and use these statistics to determine where additional monitoring or tuning might be required.

Before you begin

Do the following tasks:

Identify a table for which you want to view object usage statistics. You can use the MON_GET_TABLE table function to view monitor metrics for one or more tables.
To issue the required statements, ensure that the privileges that are held by the authorization ID of each statement include DBADM authority or SQLADM authority.
Ensure that you have EXECUTE privilege on the MON_GET_TABLE_USAGE_LIST and MON_GET_USAGE_LIST_STATUS table functions.

About this task

When you view the output of the MON_GET_TABLE table function, you might see an unusual value for a monitor element. You can use usage lists to determine whether any DML statements contributed to this value.

Usage lists contain statistics about factors like locks and buffer pool usage for each statement that affected a table during a particular time frame. If you determine that a statement affected a table negatively, use these statistics to determine where further monitoring might be required or how the statement can be tuned.

Procedure

To identify the statements that affect a table:

Set the mon_obj_metrics configuration parameter to EXTENDED by issuing the following command:
```
DB2 UPDATE DATABASE CONFIGURATION USING MON_OBJ_METRICS EXTENDED
```
Setting this configuration parameter to EXTENDED ensures that statistics are collected for each entry in the usage list.
Create a usage list for the table by using the CREATE USAGE LIST statement.
For example, to create the INVENTORYUL usage list for the SALES.INVENTORY table, issue the following command:
```
CREATE USAGE LIST SALES.INVENTORYUL FOR TABLE SALES.INVENTORY
```
Activate the collection of object usage statistics by using the SET USAGE LIST STATE statement.
For example, to activate collection for the INVENTORYUL usage list, issue the following command:
```
SET USAGE LIST SALES.INVENTORYUL STATE = ACTIVE
```
During the collection of object statistics, ensure that the usage list is active and that sufficient memory is allocated for the usage list by using the MON_GET_USAGE_LIST_STATUS table function.
For example, to check the status of the INVENTORYUL usage list, issue the following command:
```
SELECT MEMBER,
       STATE,
       LIST_SIZE,
       USED_ENTRIES,
       WRAPPED
FROM TABLE(MON_GET_USAGE_LIST_STATUS('SALES', 'INVENTORYUL', -2))
```
Run the statements for which you want to collect the object usage statistics.
When the time period for which you want to collect object usage statistics is elapsed, deactivate the collection of usage list data by using the SET USAGE LIST STATE statement.
For example, to deactivate collection for the INVENTORYUL usage list, issue the following command:
```
SET USAGE LIST SALES.INVENTORYUL STATE = INACTIVE
```
View the information that you collected by using the MON_GET_TABLE_USAGE_LIST function.
You can view statistics for a subset or for all of the statements that affected the table during the time period for which you collected statistics.
For example, to see only the 10 statements that read the most rows of the table, issue the following command:
```
SELECT MEMBER,
       EXECUTABLE_ID,
       NUM_REFERENCES,
       NUM_REF_WITH_METRICS,
       ROWS_READ,
       ROWS_INSERTED,
       ROWS_UPDATED,
       ROWS_DELETED
FROM TABLE(MON_GET_TABLE_USAGE_LIST('SALES', 'INVENTORYUL', -2))
ORDER BY ROWS_READ DESC
FETCH FIRST 10 ROWS ONLY
```
If you want to view the text of a statement that affected the table, use the value of the executable_id element in the MON_GET_TABLE_USAGE_LIST output as input for the MON_GET_PKG_CACHE_STMT table function.
For example, issue the following command to view the text of a particular statement:
```
SELECT STMT_TEXT
FROM TABLE
(MON_GET_PKG_CACHE_STMT(NULL, x'01000000000000007C0000000000000000000000020020081126171720728997', NULL, -2))
```
Use the list of statements and the statistics that are provided for the statements to determine where additional monitoring or tuning, if any, is required.
For example, a statement that has a low value for the pool_writes monitor element compared to the direct_writes monitor element value might have buffer pool issues that require attention.

What to do next

When you do not require the information in the usage list, free the memory that is associated with the usage list by using the SET USAGE LIST STATE statement. For example, to free the memory that is associated with the INVENTORYUL usage list, issue the following command:

SET USAGE LIST SALES.INVENTORYUL STATE = RELEASED