Object placement for partitioned tables

For a partitioned table, you can place each table partition in an individual dbspace. You may also place each column for a table partition in an individual dbspace. In general, however, individual dbspaces are recommended only for BLOB or CLOB columns or columns of CHAR, VARCHAR or VARBINARY greater than 255 bytes. For example:

CREATE TABLE tab2(
	col1 INT IQ UNIQUE(65500),
	col2 VARCHAR(20),
	col3 CLOB PARTITION (p1 IN dsp11, p2 IN dsp12, 
		p3 IN dsp13),
	col4 DATE,
	col5 BIGINT,
	col6 VARCHAR(500) PARTITION (p1 IN dsp21, 
		p2 IN dsp22),
	PRIMARY KEY (col5) IN dsp2) IN dsp1
	PARTITION BY RANGE (col4) 
	(p1 VALUES <= ('2006/03/31') IN dsp31, 
	p2 VALUES <= ('2006/06/30') IN dsp32,
	p3 VALUES <= ('2006/09/30') IN dsp33
	);CREATE DATE INDEX c4_date ON tab2(col4) IN dsp3;

Resulting data allocation is as follows:

Partition

Dbspace

Data

p1

dsp11

FP indexes for col3 (CLOB data)

dsp21

FP index for col6 (VARCHAR(500) data)

dsp31

FP indexes for col1, col2, col4, and col5

p2

dsp12

FP for col3 (CLOB data)

dsp22

FP for col6 (VARCHAR(500) data)

dsp32

FP indexes for col1, col2, col4, and col5

p3

dsp13

FP index for col3 (CLOB data)

dsp33

FP indexes for col1, col2, col4, col5, and col6 (varchar(500) data)

Non-partitioned

dsp1

Lookup store for col1 and other share data (for all partitions)

Non-partitioned

dsp2

Primary key HG on col5 (for all partitions)

Non-partitioned

dsp3

DATE index col4_date (for all partitions)