Performance and availability history table partitioning

This topic describes history partitioning for performance and availability history data processes for DB2 LUW and Oracle EE with the Oracle Partitioning option. This feature is not available for Microsoft SQL Server.

Due to its table and index partitioning feature, it is recommended that the Enterprise Server Edition of DB2 LUW 9.7 or later be installed when using native partitioning.

To enable partitioning, you must contact BMC Support to receive a script that converts the tables created by default to partitioned versions. Only after running this script does partitioning work properly. Do not attempt to configure partitioning without first contacting BMC Support.

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.

Note

Native partitioning for DB2 is supported when using aliases, provided that proper authority has been granted and there is enough space in the schema that the TMTM database user for temporarily storing detached tables as part of the history data purge process.
Was this page helpful? Yes No Submitting... Thank you

Comments