Performance and availability history table partitioning
TrueSight Middleware and Transaction Monitor (TMTM) can use native partitioning features of DB2 v9 ESE. To enable partitioning, modify the TMTM History Service parameters in services.cfg to configure the number of partitions you would like to use. Partitioning should be considered by installations that plan to collect large amounts of performance and availability history data.
There are four parameters that affect partitioning:
- amount_of_hires_per_partition
- amount_of_hires_to_keep
- amount_of_lowres_per_partition
- amount_of_lowres_to_keep
The total number of partitions is determined by the following formulas. The maximum number of partitions should not exceed 32000 for DB2 LUW and 1000000 for Oracle. The total number of partitions is per table.
Set a value for amount_of_hires_per_partition for high resolution history partitioning, and/or set a value for amount_of_lowers_per_partition for low resolution history partitioning. The partitioning is on the hddatetime column. A value of zero for either parameter turns partitioning off.
The values can be expressed in hours or days (for example: 10H, 10D, 10d, 10h), but each partition is partitioned to hold a certain number of hours. The name of the partition indicates the upper boundary for the value in the column hddatetime for that partition. The base partition is always 2001010100.
The table below is a partitioned high resolution history table storing four hours in each partition (amount_of_hires_per_partition=4H).
Partition name | Start | End |
|---|---|---|
P2001010100 | 0001-01-01-00:00:00.000000 | 2001-01-01-00:59:59.999999 |
P2007062501 | 2001-01-01-02:00:00.000000 | 2007-06-25-01:59:59.999999 |
P2007062505 | 2007-06-25-02:00:00.000000 | 2007-06-25-05:59:59.999999 |
P2007062509 | 2007-06-25-06:00:00.000000 | 2007-06-25-09:59:59.999999 |
P2007062513 | 2007-06-25-10:00:00.000000 | 2007-06-25-13:59:59.999999 |
P2007062517 | 2007-06-25-14:00:00.000000 | 2007-06-25-17:59:59.999999 |
The table below is a partitioned low resolution history table storing one day in each partition (amount_of_lowres_per_partition=1D).
Partition name | Start | End |
|---|---|---|
P2001010100 | 0001-01-01-00:00:00.000000 | 2001-01-01-00:59:59.999999 |
P2007062500 | 2001-01-01-00:00:00.000000 | 2007-06-25-23:59:59.999999 |
P2007062600 | 2007-06-26-00:00:00.000000 | 2007-06-26-23:59:59.999999 |
P2007062700 | 2007-06-27-00:00:00.000000 | 2007-06-27-23:59:59.999999 |
P2007062800 | 2007-06-28-00:00:00.000000 | 2007-06-28-23:59:59.999999 |
P2007062900 | 2007-06-29-00:00:00.000000 | 2007-06-29-23:59:59.999999 |
Because partitions are added and dropped, the TMTM user requires the authority to do so. There is no process within TMTM to migrate nonpartitioned history data to partitions.