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:

  • DB2_EVALUNCOMMITTED=ON
  • DB2_SKIPDELETED=ON
  • DB2_SKIPINSERTED=ON

Runstats

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. 

For the eight Transaction Management history containers (tables QP_TTHR_00-07), it is important that the DB2 catalog does not record zero rows for these tables. For new installs, run statistics frequently on these tables (and indexes) to ensure that the optimizer selects the best access path. Alternately, BMC Support can provide SQL that modifies the statistics for these tables in the DB2 catalog directly. Contact BMC Support for further information.

Reorganizations

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:

  • QP_TEMP_TIME_GRID
  • QP_TEMP_MERGE_RULE

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. 

Was this page helpful? Yes No Submitting... Thank you

Comments