Optimal aggregation levels for custom reports

Four predefined aggregation levels are used to aggregate and store the collected data to control the amount and availability of the stored data.

Each aggregation level has a storage period. After the storage period is over, the data from an aggregation level is automatically deleted. The storage period for aggregation level 1 is the shortest, and the storage period for aggregation level 4 is the longest.

Every minute (for extended insight data) or depending on the collection interval (for inflight data), collected performance data is received and stored in aggregation level 1. Aggregation means that data of a specified time frame that has the same attributes, for example, the same SQL statements, the same table space, or the same system, is summarized by using an aggregation function that matches the data types. Data types are, for example, the sum of deltas of DB2® counters, the average value for DB2 gauges, or the minimum and maximum values for DB2 water marks. After 15 minutes of collection, the data from aggregation level 1 is read and aggregated into aggregation level 2. After one hour of collection, the data of aggregation level 2 is read and aggregated into aggregation level 3. The aggregation from level 3 to level 4 happens first after one day of collection.

The following figure shows the aggregation concept.

Figure 1. Aggregation concept

Aggregation level 1 has one entry per transaction and minute. Aggregation level 2 has one entry per transaction and 15 minutes. Aggregation level 3 has one entry per transaction and hour. Aggregation level 4 has one entry per transaction and day.

Until the storage period for aggregation level 1 is reached, data for all four aggregation levels exists in parallel. If the data becomes older than the storage period of aggregation level 1, the data exists only in aggregation levels 2 to 4. If the data becomes older than the storage period of aggregation level 2, the data exists only in aggregation levels 3 and 4, and so on. The existence of the data in the higher levels is part of an aggregation, that is, the individual element has been subsumed into a larger entity and the access to the individual piece is no longer possible.

The chosen aggregation level has an impact on the data that is shown in the custom report as shown in the following example. To determine the right aggregation level to specify before you create your custom report, you can submit a query.

Example of how the aggregation level affects the results in the custom report

Jing is the database administrator of your company. She is investigating CPU times and wants to create a custom report. Jing is specifically interested in data that was collected from 10:05 a.m. to 10:30 a.m. When Jing configured monitoring for the database, she set the collection interval for the data collection to one minute.

To access this data, Jing is using a table function for the data category operating system data with aggregation level 2. Aggregation level 2 covers the data that was collected and aggregated every 15 minutes at 10:00 a.m., 10:15 a.m., and 10:30 a.m. For the time frame for which collected data should be returned in the report, she specifies a start time of 10:05 a.m. and an end time of 10:30 a.m.

Jing writes and executes the following SQL statement:

SELECT
        OPM_AGGREGATION_LEVEL_ID
        ,OPM_AGGREGATION_LEVEL_POSTFIX
        ,OPM_MIN_COLLECTION_TIMESTAMP
        ,OPM_MAX_COLLECTION_TIMESTAMP
        ,OPM_DATA_NOT_CONTAINED_MIN
        ,OPM_DATA_TIMEFRAME_MINUTES
        ,OPM_INTERVAL_COUNT
         
    FROM
        TABLE (
            REPORTS_1.GET_AGGREGATION_LEVELS(
            '2012-01-03-10.05.00.000000'
            ,'2012-01-03-10.30.00.000000'
            ))

Assuming that the data is already removed from aggregation level 1 so that aggregation level 2 is the best matching aggregation level for this query, the output is as follows:

OPM_AGGREGATION_LEVEL_ID = 2
OPM_AGGREGATION_LEVEL_POSTFIX = MEDIUM_TERM
OPM_MIN_COLLECTION_TIMESTAMP = '2012-01-03-10.15.00.000000'
OPM_MAX_COLLECTION_TIMESTAMP =
'2012-01-03-10.30.00.000000'
OPM_DATA_NOT_CONTAINED_MIN = 10
OPM_DATA_TIMEFRAME_MINUTES = 15
OPM_INTERVAL_COUNT = 2

In the output, the OPM_DATA_NOT_CONTAINED_MIN = 10 column indicates that a gap of 10 minutes exists for which collected data is not shown in the report. In this case, the reason is that the specified time frame from 10:05 a.m. to 10:30 a.m. and the used aggregation level 2 cover only the data that was collected from 10:15 a.m. to 10.30 a.m. because the specified start time of 10:05 is later than 10:00 a.m. The data that was collected from 10:05 to 10:15 is not shown in the custom report and therefore, the value for the column that shows the time frame for which collected data is not shown in the report is ten minutes.

To close this 10-minutes gap and to see the complete data, Jing has the following choices:

Assuming that the data is still available in aggregation level 1, she specifies a start time and end time for the time frame from 10:05 a.m. and 10:30 a.m. and uses aggregation level 1.
Aggregation level 1 has a finer granularity and covers data that was collected and aggregated each minute at 10:01 a.m., 10:02 a.m., 10:03 a.m. and so on, until 10:30 a.m. Thus, the data that was collected in the requested time frame of 25 minutes would be shown completely.
She increases the requested time frame for which collected data should be returned and specifies a start time of 10:00 a.m. and an end time of 10:30 a.m. but still uses aggregation level 2. Thus, the time frame conforms more to the 15-minutes granularity of the chosen aggregation level 2, and the data in the custom report will be shown completely.