Cube summary table refresh overview

The cube summary table refresh feature creates specially-designed materialized query tables (MQTs) to improve the performance of queries from dashboard dimensional activities. An MQT that uses aggregation functions is sometimes called a custom summary table.

MQTs can be defined as either system-maintained or user-maintained.

A system-maintained MQT allows the data in the table to be refreshed by DB2 with the REFRESH TABLE statement.
Data in a user-maintained MQT is refreshed using insert, update, and delete operations.

MQT advisor

The MQT advisor is a special feature in IBM® Business Monitor that recommends MQTs designed specifically for a given monitor model. In designing MQTs, the MQT advisor considers the dimensions, levels, and measures in the monitor dimensional model. The MQT advisor generates a DDL file containing statements to create user-maintained MQTs and supporting objects, such as indexes and stored procedures.

The MQT advisor takes the guesswork out of designing the right MQTs to improve Monitor dashboard performance. By examining the dimensions, levels, and measures and the underlying attributes stored in the Monitor database for your model, the MQT advisor recommends the full set of MQTs for your application.

By default, the MQT advisor creates MQTs to satisfy queries that use up to three different dimensions. You can also configure the specific dimensions that are included, the combination of dimensions, the number of MQTs, and the existence and number of staging tables. These more advanced settings can be used to balance user dimensional analysis requirements and performance requirements.

In user-maintained MQTs, DB2® does not automatically refresh the data. Instead, the generated DDL file from the MQT advisor also creates stored procedures that refresh the MQTs, and registers these stored procedures to the scheduler for hourly execution. The schedule can be further configured under Monitor Scheduled Services in the administrative console. The MQT advisor runs under the WebSphere scripting client.

You use the getMqtDDL script to implement the MQT advisor. The getMqtDDL script generates two database scripts:

One that creates the MQT resources and enables the scheduled service
One that drops the MQT resources and disables the scheduled service

If, after you enable the MQT service, you need to remove the monitor model and delete the monitor model schema, you must run the drop MQT database script before deleting the monitor model schema.

Guidelines

The following guidelines apply when using MQTs:

Use a sound dimensional model with well-defined measures and dimensions.
Do not use the same metric for a measure and a dimension. If you want to show a measure and a dimension based upon the same metric, define two separate metrics in the model.
Do not use a metric in more than one dimension.
Do not include measures based upon a stopwatch metric in the model.
Do not specify more than 10 dimensions for inclusion in MQT advisor. With more than 10 dimensions, the MQT advisor might recommend summary tables that are too complex and that cannot be installed.
Be aware that the refresh time is directly influenced by the number of instances in the fact table (that is, the more instances, the longer the refresh takes).

Error messages

You might see the following errors messages while setting up and using the MQTs:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL1476N The current transaction was rolled back because of error "-603". SQLSTATE=40506
This message might be received while executing the DDL, specifically while creating the indexes.

To resolve this error, temporarily turn off routing to MQTs by setting the database configuration parameter DFT_MTTB_TYPES to NONE.
SOAPException: faultCode=SOAP-ENV:Client; msg=Read timed out; target Exception=java.net.SocketTimeoutException: Read timed out
This message might be received while generating the MQT DDL for a complex monitoring model.

To resolve this error, locate the file MONITOR_INSTALL_ROOT/properties/soap.client.props and change the following value from com.ibm.SOAP.requestTimeout=180 to com.ibm.SOAP.requestTimeout=6000.
SQL0286N A default table space could not be found with a page size of at least "4096" that authorization ID "ADMINISTRATOR" is authorized to use. SQLSTATE=42727
You will see this error if the user temporary tablespace is not configured. A User Temporary Tablespace must be configured in the Monitor database to run the refresh stored procedures.
SQL0727N An error occurred during implicit system action type "5". Information returned for the error includes SQLCODE "-101", SQLSTATE "54001" and message tokens "Statement too long or too complex". SQLSTATE=56098
You might see this message when you invoke the refresh stored procedure.

To reduce the length of the SQL statement in the refresh stored procedure, you might need to increase the number of staging tables used during the refresh.
SQL10003C There are not enough system resources to process the request. The request cannot be processed. SQLSTATE=57011
This message might be received while executing the DDL, specifically while creating the tables.

The error indicates a memory constraint on the host system. More system memory is required. Alternatively, decreasing the -maxDimensions parameter might help.