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?

After installation, use the following syntax to launch the utility:

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.

Palasamudram N. Praveena is a project leader for the information management team at IBM®""s India Software Lab. She has about six years of experience in DB2. Currently, Ms. Praveena provides technical support for DB2 UDB Data Links Manager. She is a certified IBM DB2 Database Administrator and has been part of the DB2 support team for some time.

Sam Lightstone is a senior technical staff member and a development manager with IBM""s DB2 Universal Database development team. He is a co-founder and leader of research and development for DB2""s autonomic computing.