Generating the cube summary tables

To enable the cube summary table refresh service, you use the MQT advisor to create and populate cube summary tables.

Before you begin

Before you begin this task, you must make sure that the data movement service has been enabled.

MQTs are supported only when you are using DB2® as the IBM® Business Monitor database.

About this task

You use the getMqtDLL script to generate the MQTs. You can take the default values when you use the script, or you can add optional parameters to customize the service.

Procedure

Change to the following IBM Business Monitor server subdirectory to run getMqtDLL: \profiles\<profile_name>\bin

To run getMqtDLL with default values, enter one of the following commands:

wsadmin -wsadmin_classpath "..\..\..\plugins\com.ibm.wbimonitor.lifecycle.spi.jar;
..\..\..\plugins\com.ibm.wbimonitor.repository.jar" 
-lang jython -f "..\..\..\scripts.wbm\mqt\getMqtDDL.jy" 
model_ID model_version

wsadmin -wsadmin_classpath "../../../plugins/com.ibm.wbimonitor.lifecycle.spi.jar:
../../../plugins/com.ibm.wbimonitor.repository.jar" 
-lang jython -f "../../../scripts.wbm/mqt/getMqtDDL.jy" 
model_ID model_version

The model_ID and model_version parameters are required. These parameters must be specified exactly as they appear on the Monitor Models page of the WebSphere administrative console (for example: getMqtDDL.jy Mymodel 2011-08-31T23:59:59).

To customize the MQTs recommended by the MQT advisor, use one or more of the following parameters with the getMqtDDL script:
-maxDimensions z

where z is the maximum number of dimensions to be analyzed together.
This parameter places limits on the number of dimensions that can be efficiently drilled into from the dashboard widgets.

The range of values is from 1 through 5. The default is 3.
Tip: Use the default value (3). Using a value of 4 or 5 might increase the time to refresh MQTs to an undesirable level.

-maxSummaries z

where z is the maximum number of summary tables per monitoring context.
maxSummaries specifies the maximum number of MQTs that will be generated. Multiple different combinations of dimensions can be stored in a single MQT, or they can be spread across multiple MQTs.

The range of values is from 0 through 100.
The default is 15.

-maxStageWidth z

where z is the maximum number of refresh staging tables to be generated.
The range of values is from 0 through the value specified in maxSummaries.
The default is 1.
Tip: By default, one staging table is used to refresh all summary tables. With more than five dimensions in the monitoring context, you might need to increase the number of staging tables.

-maxLevel(dimension, level)
where level is the depth of the dimension in the MQT.
The level can be one of the following values:
- -1: summarize only the deepest level
- 0: no summary for this dimension
- 1: summarize only top level
- n: summarize only the top n levels
maxLevel can be used to limit the number of dimensions used in the MQTs and the number of levels to be used for each dimension. You can specify multiple dimensions by repeating the maxLevel parameter, such as -maxLevel(DATE, 0) -maxLevel(CITY, 1).
-tableSpace t

where t is the tablespace in which to create MQTs.
The default is DSTS32K, which specifies a tablespace to be used to contain the generated MQTs.

-createDDLname filename

where filename is the name of the generated create DDL file.
The default is CreateMQT_<modelId>.ddl

-dropDDLname filename

where filename is the name of the generated drop DDL file.
The default is DropMQT_<modelId>.ddl
The following example shows the use of the optional parameters with getMqtDLL:
```
wsadmin -wsadmin_classpath "..\..\..\plugins\com.ibm.wbimonitor.lifecycle.spi.jar;
..\..\..\plugins\com.ibm.wbimonitor.repository.jar"
-lang jython -f "..\..\..\scripts.wbm\mqt\getMqtDDL.jy"
MortgageLendingBAM 2011-05-03T10:08:00
-maxDimensions 3 -maxSummaries 15 -maxStageWidth 1
-maxLevel(Date,3) -maxLevel(Location,2) -tableSpace DSTS32K
```

Results

A DDL file, which contains statements to create and load MQTs and to create supporting objects (such as indexes and stored procedures), is generated. Another DDL file is generated for dropping MQTs.

The stored procedures contain refresh logic necessary to update user-maintained MQTs and to update statistics used by the optimizer. The DDL file also registers the refresh stored procedure to the Monitor Scheduler and initializes a refresh schedule.

What to do next

You can configure the schedule for data refresh in the administrative console under the Manage Scheduled Services heading for the Cube Summary Table Refresh service.