IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Installation Guides > Installation Guide
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2
Tivoli Data Warehouse range partitioning
Range partitioning is a database data organization feature that allows for quicker pruning of data from the database in a single operation. Range partitioning permits the fast roll out of data without having to perform a resource intensive DELETE operation on blocks of rows. Range partitioning also improves query performance when the partitioning key is part of the query clause.
Supported databases for Tivoli Data Warehouse database range partitioning:
- IBM DB2 Enterprise Edition 9.7 or higher.
- DB2 for z/OS 9.1 or higher.
- Oracle 11g Release 1 or higher, with the partitioning option.
Performance is improved because the database can discard unnecessary partitions from the list of blocks that must be fetched from disk. With table partitioning you can significantly improve pruning and query performance in large Tivoli Data Warehouse databases. As of Tivoli Monitoring version 6.3, Tivoli Data Warehouse range partitioning provides the following functionality:
- You can configure the Warehouse Proxy Agent and the Summarization and Pruning Agent to create partitioned tables.
- You can use the schema publication tool to generate the DDL for partitioned tables, for a database administrator to run later.
- The Summarization and Pruning Agent is responsible for managing partitions based on your configuration, that is adding partitions or pruning partitions.
- Depending on the Tivoli Data Warehouse database, the Summarization and Pruning Agent prunes data from partitioned tables as follows:
- On DB2 for Linux, UNIX, and Windows or Oracle: The Summarization and Pruning Agent prunes data from partitioned tables by dropping the appropriate partitions.
- On DB2 on z/OS: The Summarization and Pruning Agent prunes data by rotating partitions.
If the table being pruned is not partitioned or is partitioned using a different scheme than the one used by Tivoli Data Warehouse, rows are pruned using SQL DELETE (the existing pruning logic). It is recommended best practice to use the Warehouse Proxy Agent and the Summarization and Pruning Agent to enable partitioning.
Range partitioning using DB2 for Linux, UNIX, and Windows or Oracle: The partition range used by Tivoli Data Warehouse is one day and the partition is named PYYYYMMDD. A catch all partition with an additional suffix of _MV is also created and will contain any data older than the day that the table was created by either the Warehouse Proxy Agent (detail tables) or the Summarization and Pruning Agent (summary tables). The data in the catch all partition is not pruned until the entire partition is outside of the retention period of the table. Therefore, when data is deleted from a partitioned table by removing or detaching partitions, data older than the configured retention period can still exist in the table after pruning is performed. The partitioning key is one column: the aggregation timestamp, which is usually called WRITETIME.
Range partitioning using DB2 on z/OS: The partition range used by Tivoli Data Warehouse is one day and the partition is named using an incremental number beginning with 1. Partitions are rotated, they cannot be detached from a table. Rotating partitions allow old data to roll off, while reusing the partition for new data. In order to control the number of partitions added, the Summarization and Pruning Agent does not allow the addition of partitions when the maximum number is reached. The maximum partition number is equal to the sum of 1 (the partition holding the current day's data) plus the number of retention days, weeks, months, quarters, or years. This retention period depends on the pruning settings for the aggregated table and the number of upward partitions set in the KSY_PARTITIONS_UPWARD variable. It is recommended to set some pruning parameters for a table to avoid the unlimited growth of partitions against a DB2 on z/OS Tivoli Data Warehouse database.
The environment variables listed in this section can be configured from the agent configuration GUI, the Tivoli Enterprise Portal, and the Command-Line Interface. Errors are reported via the appropriate Tivoli Data Warehouse agent and available for situation processing and workspaces.
When tables are pruned by dropping partitions, the reported statistics for the number of rows deleted is an estimate only. The estimate is based on the number of rows that were in the partition the last time the database statistics were updated for the table. The database statistics must be updated on a regular basis so that this number is as accurate as possible and to help improve the performance of SQL against the table.
Data partitioning might require additional licenses for your database management system. Consult with your database administrator for any rules or restrictions imposed by your database implementation before using range partitioning.
- Specify range partitioned tables for the Warehouse Proxy Agent
Table partitioning only occurs when new tables are created in the Warehouse Proxy Agent. There is no change to existing tables that were created before Tivoli Monitoring V6.3.
- Specify range partitioned tables for the Summarization and Pruning Agent
Table partitioning only occurs when new tables are created in the Summarization and Pruning Agent. There is no change to existing tables that were created before Tivoli Monitoring V6.3.
- Specify range partitioned tables for the schema publication tool
As of Tivoli Monitoring V6.3, the schema publication tool allows for the creation of tables with partitions using SQL scripts. This can be used when the user ID for the Warehouse Proxy Agent or Summarization and Pruning Agent does not have the required access permission to create tables.