DB2 LUW Data Organization techniques - Table Partitioning with MDC

Data organization using table partitioning and MDC is another important topic in DB2 LUW. The details given here will help to visualize how the data is organized using the CREATE TABLE statements. The purpose of this topic is limited to table partitioning with MDC. There are four simple CREATE TABLE statements and their visual representation is given in this topic. Please continue looking the given visual representation for each example for better understanding of the data organization.

Example 1.

CREATE TABLE mdctab (YEARMONTH int, yearval int) PARTITION BY RANGE (yearval)
(STARTING FROM 201301 ENDING AT 201512 EVERY 3) ORGANIZE BY DIMENSIONS (yearval)

The CREATE TABLE statement given in Example 1 creates 12 partitions with the help of PARTITION BY RANGE (yearval). Each partition is created with 3 months data. And the ORGANIZE BY DIMENSION (yearval) helps to organize those three months data inside a single MDC block on each partition.

The visual representation of the data organization of Example 1.

 

Data Partition(YEARMONTH)

MDC Blocks(yearval)

 

 

2013

 

Part 1

201301

MDC Block 1

 

201302

 

201303

 

Part 2

201304

MDC Block 2

 

201305

 

201306

 

Part 3

201307

MDC Block 3

 

201308

 

201309

 

Part 4

201310

MDC Block 4

 

201311

 

201312

 

 

 

2014

 

Part 5

201401

MDC Block 5

 

201402

 

201403

 

Part 6

201404

MDC Block 6

 

201405

 

201406

 

Part 7

201407

MDC Block 7

 

201408

 

201409

 

Part 8

201410

MDC Block 8

 

201411

 

201412

 

 

 

2015

 

Part 9

201501

MDC Block 9

 

201502

 

201503

 

Part 10

201504

MDC Block 10

 

201505

 

201506

 

Part 11

201507

MDC Block 11

 

201508

 

201509

 

Part 12

201510

MDC Block 12

 

201511

 

201512

 

 

Example 2.

CREATE TABLE mdctab (dateval date, yearval GENERATED ALWAYS AS year(dateval),monthval GENERATED ALWAYS AS (month(dateval)))
PARTITION BY RANGE (monthval)
(STARTING FROM 1 ENDING AT 12 EVERY 1)
ORGANIZE BY DIMENSIONS (yearval,monthval)

The CREATE TABLE statement given in Example 2 creates 12 partitions with the help of PARTITION BY RANGE (monthval). Each partition is created with the data for the similar month from every year. And the ORGANIZE BY DIMENSION (yearval,monthval) helps to organize those data for every year inside a single MDC block on each partition.

            This results 3 MDC cells – one per year with the similar month in each partition. The third column represents the value of monthval – 1 to 12 months. MDC Block 1 - holds the data for the month of January, 2015 and MDC Block 2 - holds the data for the month of January, 2014 and MDC Block 3 – holds the data for the month of January, 2013. MDC Blocks 4 to 6 – holds data for the month of February from 2015, 2014 and 2013 respectively and so on.

The table partitions are named from Part 1 to Part 12 and MDC cells are names from 1 to 36 (MDC Block 1 to 36). On the left side, you see the column used as a table partitioning key which is monthval and on the top,
You see the column(s) used to create MDC Blocks which is (yearval, monthval) in this case.

 

The visual representation of the data organization of Example 2.

Data Partition(monthval)

MDC Blocks(yearval,monthval)

Part 1

1

MDC Block 1
2015,1

MDC Block 2
2014,1

MDC Block 3
2013,1

 

 

 

Part 2

2

MDC Block 4
2015,2

 MDC Block 5
2014,2

MDC Block 6
2013,2

 

 

 

Part 3

3

MDC Block 7
2015,3

MDC Block 8
2014,3

MDC Block 9
2013,3

 

 

 

Part 4

4

MDC Block 10
2015,4

MDC Block 11
2014,4

MDC Block 12
2013,4

 

 

 

Part 5

5

MDC Block 13
2015,5

MDC Block 14
2014,5

MDC Block 15
2013,5

 

 

 

Part 6

6

MDC Block 16
2015,6

MDC Block 17
2014,6

MDC Block 18
2013,6

 

 

 

Part 7

7

MDC Block 19
2015,7

MDC Block 20
2014,7

MDC Block 21
2013,7

 

 

 

Part 8

8

MDC Block 22
2015,8

MDC Block 23
2014,8

MDC Block 24
2013,8

 

 

 

Part 9

9

MDC Block 25
2015,9

MDC Block 26
2014,9

MDC Block 27
2013,9

 

 

 

Part 10

10

MDC Block 28
2015,10

MDC Block 29
2014,10

MDC Block 30
2013,10

 

 

 

Part 11

11

MDC Block 31
2015,11

MDC Block 32
2014,11

MDC Block 33
2013,11

 

 

 

Part 12

12

MDC Block 34
2015,12

MDC Block 35
2014,12

MDC Block 36
2013,12

 

 

 

Example 3.

CREATE TABLE mdctab (dateval date, yearval GENERATED ALWAYS AS year(dateval),monthval GENERATED ALWAYS AS (month(dateval)))
PARTITION BY RANGE (monthval)
(STARTING FROM 1 ENDING AT 12 EVERY 2)
ORGANIZE BY DIMENSIONS (yearval,monthval)

The CREATE TABLE statement given in Example 3 creates 6 partitions with the help of PARTITION BY RANGE (monthval). Each partition is created with the data for 2 similar months from every year. And the ORGANIZE BY DIMENSION (yearval,monthval) helps to organize those data per month for every year inside a single MDC block on each partition.

            This results 6 MDC cells – one per year and month combination in each partition. The third column represents the value of monthval – 1 to 12 months with 2 months data on each partition (months 1-2 combined, and 3 - 4 combined, etc). And so, now the MDC Block 1 - holds the data for the month of January, 2015 and MDC Block 2 - holds the data for the month of January, 2014 and MDC Block 3 – holds the data for the month of January 2013. MDC Blocks 4 to 6 – holds data for the month of February 2015, 2014 and 2013 respectively and so on.

The table partitions are named from Part 1 to Part 6 and MDC cells are named from 1 to 36 (MDC Block 1 to 36). On the left side, you see the column used as a table partitioning key which is monthval and on the top,
You see the column(s) used to create MDC Blocks which is (yearval, monthval) in this case.

            The difference between Example 2 and 3 is the number of table partitions are reduced from 12 to 6 by combining two months data from every year in each partition. At the same time there is no change in the MDC arrangements which still results 36 MDC cells.

The visual representation of the data organization of Example 3.

Data Partition(monthval)

MDC Blocks(yearval,monthval)

Part 1

1

MDC Block 1
2015,1

MDC Block 2
2014,1

MDC Block 3
2013,1

 

 

 

2

MDC Block 4
2015,2

 MDC Block 5
2014,2

MDC Block 6
2013,2

 

 

 

Part 2

3

MDC Block 7
2015,3

MDC Block 8
2014,3

MDC Block 9
2013,3

 

 

 

4

MDC Block 10
2015,4

MDC Block 11
2014,4

MDC Block 12
2013,4

 

 

 

Part 3

5

MDC Block 13
2015,5

MDC Block 14
2014,5

MDC Block 15
2013,5

 

 

 

6

MDC Block 16
2015,6

MDC Block 17
2014,6

MDC Block 18
2013,6

 

 

 

Part 4

7

MDC Block 19
2015,7

MDC Block 20
2014,7

MDC Block 21
2013,7

 

 

 

8

MDC Block 22
2015,8

MDC Block 23
2014,8

MDC Block 24
2013,8

 

 

 

Part 5

9

MDC Block 25
2015,9

MDC Block 26
2014,9

MDC Block 27
2013,9

 

 

 

10

MDC Block 28
2015,10

MDC Block 29
2014,10

MDC Block 30
2013,10

 

 

 

Part 6

11

MDC Block 31
2015,11

MDC Block 32
2014,11

MDC Block 33
2013,11

 

 

 

12

MDC Block 34
2015,12

MDC Block 35
2014,12

MDC Block 36
2013,12

 

 

 

Example 4.

CREATE TABLE mdctab (dateval date, yearval GENERATED ALWAYS AS year(dateval),monthval GENERATED ALWAYS AS (month(dateval)))
PARTITION BY RANGE (monthval)
(STARTING FROM 1 ENDING AT 12 EVERY 2)
ORGANIZE BY DIMENSIONS (yearval)

The CREATE TABLE statement given in Example 4 creates 6 partitions with the help of PARTITION BY RANGE (monthval). Each partition is created with the data for 2 similar months from every year. And the ORGANIZE BY DIMENSION (yearval) helps to organize those data for every year inside a single MDC block on each partition.

            This results 3 MDC cells – one per year with two similar months in each partition. The third column represents the value of monthval – 1 to 12 months with 2 months data on each partition (months 1&2 combined, and 3&4 combined, etc). And so, now the MDC Block 1 - holds the data for the month of January and February, 2015 and MDC Block 2 - holds the data for the month of January and February, 2014 and MDC Block 3 – holds the data for the month of January and February, 2013. MDC Blocks 4 to 6 – holds data for the month of March, April 2015, 2014 and 2013 respectively and so on.

The table partitions are named from Part 1 to Part 6 and MDC cells are named from 1 to 18 (MDC Block 1 to 18). On the left side, you see the column used as a table partitioning key which is monthval and on the top,
You see the column(s) used to create MDC Blocks which is (yearval) in this case.

            The difference between Example 3 and 4 is the number of MDC Blocks are are reduced from 36 to 18 by combining two months data from every year in each cell. At the same time there is no change in the table partition arrangements which remains as 6.

 

The visual representation of the data organization of Example 4.

Data Partition(monthval)

MDC Blocks(yearval)

Part 1

1&2

MDC Block 1
2015,1
2015,2

MDC Block 2
2014,1
2014,2

MDC Block 3
2013,1
2013,2

 

 

 

 

 

 

Part 2

3&4

MDC Block 4
2015,3
2015,4

MDC Block 5
2014,3
2014,4

MDC Block 6
2013,3
2013,4

 

 

 

 

 

 

Part 3

5&6

MDC Block 7
2015,5
2015,6

MDC Block 8
2014,5
2014,6

MDC Block 9
2013,5
2013,6

 

 

 

 

 

 

Part 4

 

MDC Block 10
2015,7
2015,8

MDC Block 11
2014,7
2014,8

MDC Block 12
2013,7
2013,8

 

 

 

7&8

 

 

 

 

 

 

 

Part 5

9&10

MDC Block 13
2015,9
2015,10

MDC Block 14
2014,9
2014,10

MDC Block 15
2013,9
2013,10

 

 

 

 

 

 

Part 6

11&12

MDC Block 16
2015,11
2015,12

MDC Block 17
2014,11
2014,12

MDC Block 18
2013,11
2013,12

 

 

 

 

 

 

About the Author:








}