by Nick Lansdowne

In the majority of ITM implementations the size of the Tivoli Data Warehouse is maintained based on the pruning configuration values defined for the Summarization and Pruning Agent and the individual attribute groups. For example, the S&P agent is configured to run on an interval basis or at a specific time on a daily basis, and the retention period for the raw and aggregated data is configured for each attribute group.

However, when a managed system is decommissioned, the aggregated data for a specific attribute group will not be pruned beyond the retention period of the raw data. For example, if a Windows OS agent is decommissioned for which historical data was collected for the attribute group NT_Logical_Disk, and that attribute group had a retention period of 28 days for the raw data, then the pruning of the aggregated tables will not run after that 28 day period. Hence, aggregated data may remain long after the defined pruning date. This is because the candidates for pruning are identified from the raw table, and no data persists for the managed system after the 28 day retention period.

This article will discuss how such decommissioned data can be identified, and deleted from the database. It is applicable to pre ITM 6.2 FP01 and ITM 6.1 FP07 that updated the pruning logic.

Tivoli Data Warehouse Tables

Firstly, let's understand the naming convention behind the Tivoli Data Warehouse tables. Historical data collection of an attribute group will create a database table with the name of the attribute group. For example, if data has been collected for the attribute group NT_Logical_Disk then a database table will exist named NT_Logical_Disk. For DB2, by default this will be in the name space for ITMUSER.

If summarization is configured for the attribute group, then a summarization table will exist for each of the aggregation periods. Each table has a base name of the attribute group, with an extension to indicated the summarization period. For example, assuming that both daily and weekly aggregation is configured for the attribute group NT_Logical_Disk, then two additional tables will exist for the attribute group, named NT_Logical_Disk_D and NT_Logical_Disk_W, respectively. The full complement of extensions is defined in the table below.

Identifying Decommissioned Managed Systems

It is possible to identify decommissioned managed systems using a basic database queries. The query identifies aggregation tables that include data for managed systems that are not included within the raw data tables, hence assumes the raw data table retention period has passed. From the DB2 command line processor, the syntax is:

For example, for the NT_Logical_Disk attribute group, and the daily aggregated data, run the command:

If you are confident that the identified managed systems have been decommissioned, then aggregated data can be deleted with the command:

or you may wish to take the more cautious approach, and deleted the data for an individual managed system:

Hence, for the managed system identified in the previous example, this command would be:

Note, the field label Server_Name is correct for the Windows OS Agents but does differ for other agents. For example, the Linux and Tivoli Performance Analyser agent tables distinguish the managed systems with the field System_Name.

Which Raw Data and Aggregation Tables are in my Database?

The above command will need to be run against each of the aggregated data tables, and hence you must first identify all those tables.

Firstly, to identify the raw data tables within your Tivoli Data Warehouse database, from the DB2 command line processor run:

For example:

Subsequently, to identify the related aggregation tables for each of these raw data tables, run the command:

For example, the NT_Logical_Disk aggregation tables can be identified with the command:

This information can be fed-back into the command to identify the decommissioned nodes.

Conclusions

The data within the Tivoli Data Warehouse is generally self maintaining if the Summarization & Pruning agent configuration values defined during the design phase are adhered to. However, decommissioned nodes are a part of a network infrastructure evolution and hence its worthwhile basing some housekeeping scripts on the above techniques.

by Nick Lansdowne