Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

Chapter 4. Modernizing database definitions 53

4.4 Partitioned tables

DB2 UDB for iSeries V5R3 supports partitioned tables using SQL. Partitioning allows for the

data to be stored in more than one member, but the table appears as one object for data

manipulation operations such as select, insert, update, and delete. A partition is the

equivalent of a database file member. Each partition can be saved, restored, exported from,

imported to, dropped, or reorganized independently of the other partitions. You must have

DB2 Multisystem (5722-SS1 option 27) installed on your iSeries server in order to take

advantage of partitioned tables support.

There are two types of partitioning:

Hash partitioning and range partitioning. You specify the

type of partitioning with the PARTITION BY clause in the CREATE TABLE statement. In our

example, we create a partitioned table PAYROLL in library PRODLIB with partitioning key

EMPNUM in four partitions.

򐂰 Hash partitioning places rows at random intervals across a user-specified number of

partitions and key columns.

CREATE TABLE PRODLIB.PAYROLL(EMPNUM INT, FIRSTNAME CHAR(15), LASTNAME CHAR(15),

SALARY INT)

PARTITION BY HASH(EMPNUM) INTO 4 PARTITIONS

򐂰 Range partitioning divides the table based on user-specified ranges of column values.

CREATE TABLE PRODLIB.PAYROLL

(EMPNUM INT, FIRSTNAME CHAR(15), LASTNAME CHAR(15), SALARY INT)

PARTITION BY RANGE(EMPNUM)

STARTING FROM (MINVALUE) ENDING AT (500) INCLUSIVE,

STARTING FROM (501) ENDING AT (1000) INCLUSIVE,

STARTING FROM (1001) ENDING AT (MAXVALUE)

However, as of the beginning of V5R3, the partitioned tables support cannot take advantage

of the query optimizer for leveraging the performance advantages. The improvement will

come in the future. The partitioned tables should really only be used in V5R3 if you have a

table that is approaching the single size table limit of 4.2 billion rows or 1.7 terabytes of

storage.

For more information on partitioned tables refer to the whitepaper Table partitioning

strategies for DB2 UDB for iSeries, which can be found at:

http://www-1.ibm.com/servers/enable/site/education/abstracts/2c52_abs.html

54 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone