Multi-Dimensional Clustering Table Size Estimator for DB2

Overview

Requirements

Download

FAQs

Forum

Reviews

Update: February 19, 2008

What is Multi-Dimensional Clustering Table Size Estimator for DB2?

Many applications, OLAP and data warehousing in particular, usually consider several attributes as dimensions for processing and maintenance. Performance and scalability of queries in this type of environment can be slow using the traditional relational-database approach based on one-dimensional clustering, in which an index composed of one or more key parts is identified as the basis for data clustering.

The Multi-Dimensional Clustering (MDC) Table support in DB2® is defined to include one or more clustering dimensions and resolves many of these performance issues. However, Database administrators working with MDC tables must manually estimate sizes of MDC tables and allocate space ahead of time. This can be both an error-prone and a time-consuming process.

Multi-Dimensional Clustering Table Size Estimator for DB2 can be used to determine the space requirements when a normal table is converted to MDC or when the clustering dimensions of an existing MDC table are changed. This utility optimally estimates the MDC table size requirements based on table statistics and MDC dimension parameters.

How does it work?

db2mdcsizer <database name> <schema name> <table name> <dimension column list>
[dimension column expression] [-s] [-g] [-e] [-c] [-user <username> -password <password>]

Notes:

• A maximum of 16 columns in the dimension column list is supported because MDC supports only up to a maximum of 16 dimension columns.
• Number of expressions in the dimension column expression should be same as the number of columns in the dimension column list.

The input parameters in the above code are as follows:

• s: The sampling factor can range from 1 to 100.
• g: The growth factor can range from 1.1 to 1000.
• e: The extent size can be any valid extent size (between 2 and 256).
• c: This parameter specifies whether estimates for all possible combinations of dimension column lists is required. If not specified, the default is the size estimate when the table is clustered on all the columns specified in the dimension column list.
• db2mdcsizer -help: This command brings up the help for the tool.

Note: The goal of the utility is to help estimate MDC table size within reasonable limits. Minor variance of a few cells might be observed if the sampling specified is not 100%.

The output includes the following:

• the dimension column list
• size of the original table (in MB)
• estimated size of MDC table (in MB)
• the percentage of increase in the estimated MDC table size with respect to the original table size
• space requirement for the MDC table.