IBM Tivoli Monitoring > Version 6.3 Fix Pack 2 > Administrator's Guide > Manage historical data
IBM Tivoli Monitoring, Version 6.3 Fix Pack 2
Tivoli Data Warehouse range partition migrations
Range partitioning is a database data organization feature that can significantly improve pruning and query performance in large Tivoli Data Warehouse databases. You can migrate your existing tables to partitioned tables to take advantage of the performance improvements provided with partitioned tables.
To use partitioned tables, the Summarization and Pruning agent and Warehouse Proxy agent must both be configured with partitioning enabled and the Tivoli Data Warehouse must allow partitioning.
The migration and required cleanup is handled using scripts generated by the schema publication tool in migrate mode. The scripts provide the following functions:
- tdw_migrate_setup.sql
- This script creates a stored procedure to redefine the source table to a new partitioned table and creates the control tables required for migration, such as the WAREHOUSE_MIGRATION_STATUS table.
- tdw_migrate_step1.sql
- This script invokes the stored procedure created in the setup script. The stored procedure renames the source table to MIGRATING_<short table name>, creates the new partitioned table, loads the data from the source table to the new table, and then renames the source table to DONE_<short table name>.
- tdw_migrate_step2.sql
- This script recreates the indices on the new partitioned tables, deletes the source tables, and grants SELECT to PUBLIC on the tables.
You can also migrate tables partitioned using a partitioning scheme different than the Tivoli Data Warehouse partitioning scheme. Only a table partitioned with the Tivoli Data Warehouse scheme can be managed by the Summarization and Pruning agent. To continue to use your user-defined partitioning scheme, use the KSY_TABLE_FILTER variable to list only the tables you want migrated.
A migrated table's partitions are defined based on the table's retention period and the forward partitions parameter. The forward partitions parameter is a configuration parameter defined in the Summarization and Pruning configuration file using the variable KSY_PARTITIONS_UPWARD. The retention period is the pruning parameter defined on the attribute group you select through the History Configuration dialog in the Tivoli Enterprise Portal or through the command line.
For more information about range partitioning, see "Tivoli Data Warehouse range partitioning" in the IBM Tivoli Monitoring Installation and Setup Guide.
The status tables WAREHOUSELOG and WAREHOUSEAGGREGLOG, can also be migrated. These tables can be filtered by specifying them in the KSY_TABLE_FILTER variable or by product code KHD for WAREHOUSELOG and KSY for the WAREHOUSEAGGREGLOG in the KSY_PRODUCT_FILTER variable. These tables are treated as detailed tables if the KSY_SUMMARIZATION_FILTER is used.
Prerequisites and best practices
Before you begin, ensure the following criteria is met:
- Ensure you have sufficient disk space, since during the migration two copies of the same data are present until the source table is deleted. You need at least twice the disk space of the tables being migrated.
- Define pruning for all the tables being migrated before the migration, so that data can be properly pruned after the tables are partitioned.
- Stop the Summarization and Pruning agent and Warehouse Proxy agent during the migration period. Also stop any reports or external users of the tables being migrated.
- Ensure that the Tivoli Enterprise Portal Server application support is updated. The portal server and agent application support must match.
Best practice is to migrate the tables in batches. This practice reduces the amount of disk space required for migration and the amount of time the Summarization and Pruning agent and Warehouse Proxy agent need to be offline. A batch of tables can be migrated within a maintenance window.
- Migrate non-partitioned tables to partitioned tables for DB2 on Linux, UNIX, and Windows
Use the following steps to migrate non-partitioned tables if you are using DB2 on Linux, UNIX, or Windows.
- Migrate non-partitioned tables to partitioned tables for DB2 on z/OS
Use the following steps to migrate non-partitioned tables if you are using DB2 on z/OS.
- Migrate non-partitioned tables to partitioned tables for Oracle
Use the following steps to migrate non-partitioned tables if you are using Oracle.
Parent topic:
Manage historical data