DB2 performance and maintenance considerations
This section describes routine maintenance procedures for DB2 not already described in Routine database maintenance . It includes the following information relevant to DB2 LUW.
DB2 LUW environment variables
Set the following DB2 LUW DB2 environment variables for best performance:
For DB2 LUW, it is recommended to run statistics on the entire schema at least weekly, or if volume in any of the reference tables changes considerably.
Table and index reorganization is crucial for the reference tables. These tables are read frequently in an OLTP fashion and fast response is critical. Therefore, page arrangement is very important for these tables.
For DB2 LUW history tables, when a reorganization is necessary and there is no maintenance window available to bring down the TMTM History Service, consider ONLINE INDEX reorganizations over INPLACE reorganizations.
Performance and Availability work tables
There are two tables that are used as work tables by TrueSight Middleware and Transaction Monitor (TMTM) and contain data that is transitory. They are used by History compression. The behavior and treatment of these tables is different from that of other tables. The differences are outlined here. The work table names are:
For DB2 LUW, these TMTM work tables are created with Not Logged Initially (NLI) enabled and are set empty every hour. There two tables can be nonrecoverable.
It is critical that the DB2 optimizer assume that there are rows in the QP_TEMP_TIME_GRID and QP_TEMP_MERGE_RULE tables.
Statistics should not be run on these tables at database creation and initialization time. If no compressions have executed, such as is the case during create/init time, there are zero rows in these tables. If at any time the DB2 catalog cardinality for these tables is zero, there are serious performance ramifications, as inefficient access paths are chosen concerning these tables.
If the DB2 catalog does contain zero rows for either of these tables, run statistics on the tables and indexes at a time when there are at least 10,000 rows in the respective table. Rows should be present most of the time. You can also select to directly update the catalog to influence the optimizer into optimizing for 10,000 rows. Consult your DB2 documentation for further details.