The following describes the benefits that are provided by using multiple columns as partitioning keys to split a table:

• Increase in SQL processing speed
  You can execute SQL processes in parallel and more quickly perform searches by using multiple keys to narrow the searched range.
  
• Decrease in operation time
  Matrix partitioning creates smaller partitions, which decreases the size of any single RDAREA, and reduces the time required to perform operations such as reorganization, backup, and failure recovery.
  

We recommend using key range partitioning with boundary values specified for both partitioning columns when the following conditions are met:

• Partitioning by the first dimension partitioning column results in a vast amount of data within each set of boundary values.
• Multiple columns need to be specified in the search condition for a UAP that accesses the table and you wish to limit the RDAREAs that are accessed by multiple columns. Or, you wish to limit the RDAREAs that are accessed only by column n specified in the SQL statement.

When the following conditions are met, we recommend that you combine key range partitioning with boundary values specified and hash partitioning:

• Partitioning by the first dimension partitioning column results in a vast amount of data within each set of boundary values.
• You wish to uniformly segment the range of data that was partitioned by the first dimension partitioning column.

To define matrix partitioning, specify the following in the PARTITIONED BY MULTIDIM operand of the CREATE TABLE definition SQL:

• Allocation of a table to an RDAREA
• The matrix partitioning method (partitioning key, partitioning method)

(a) Key range partitioning with boundary values specified is used for the second dimension partitioning column

In this example, boundary values are specified for the registration date (RDATE) and the store number (SNUM) in a customer data table. The table is then matrix partitioned such that the user data is stored by registration date and store number in the user RDAREAs USR01 to USR06. In this example, the number of user RDAREAs needed to store this data is (the number of boundary values for RDATE +1) (the number of boundary values for SNUM + 1), so the number of user RDAREAs needed is 3 2 = 6.

RDATE	SNUM
	100 or less	101 or greater
2000 and earlier	USR01	USR02
2001	USR03	USR04
2002 and later	USR05	USR06

The following shows the SQL code used to matrix partition the table:

 
CREATE FIX TABLE CTBL
  (RDATE DATE, SNUM INT, CNAME NCHAR(10))
  PARTITIONED BY MULTIDIM(
  RDATE (('2000-12-31'),('2001-12-31')), . . .1.
  STORE_NO ((100))                          . . .2.
  )IN ((USR01,USR02),(USR03,USR04),(USR05,USR06))

Explanation

1. Specifies the name of the first dimension partitioning column (name of the column that is used as the first partitioning key), and its list of boundary values.
2. Specifies the name of the second dimension partitioning column (name of the column that is used as the second partitioning key), and its list of boundary values.

Figure 3-15 shows an example of matrix partitioning.

Figure 3-15 Example of matrix partitioning (combining with key range partitioning with boundary values specified)

(b) Hash partitioning is used for the second dimension partitioning column

This example uses FIX hash partitioning for the second dimension partitioning column.

In this example, boundary values are specified for the registration dates in a customer table, and then a hash function is used to separate the data by store number and region code into three segments. This results in a table that is matrix partitioned such that each segment of customer data is stored in one of the user RDAREAs (USR01 to USR09) shown below. The number of user RDAREAs needed to store the resulting data is (number of boundary values + 1) (desired partitions to be obtained by hash function). In this example, the number is 3 3 = 9.

Registration date	Store number and region code (divided into 3 partitions by hash function)
2002 and earlier	USR01	USR02	USR03
2003	USR04	USR05	USR06
2004 and later	USR07	USR08	USR09

The following code shows the SQL statement used to define the table to be matrix partitioned:

 
CREATE FIX TABLE customer-table
  (registration-date DATE, store-number INT, region-code INT, customer-name NCHAR(10))
  PARTITIONED BY MULTIDIM
  (registration-date (('2002-12-31'),('2003-12-31')), ...1.
  FIX HASH HASH6 BY store-number, region-code          ...2.
  )IN ((USR01,USR02,USR03),
       (USR04,USR05,USR06),
       (USR07,USR08,USR09))

Explanation

1. Specifies the name of the first dimension partitioning column (name of the column to be used as the first partitioning key) and its list of boundary values.
2. Specifies the name of the second dimension partitioning column (name of the column to be used as the second partitioning key) and the name of the hash function.

Figure 3-16 shows an example of matrix partitioning.

Figure 3-16 Example of matrix partitioning (combination of key range partitioning with boundary values specified and hash partitioning)