TABLESPACES IN DB2.....A Review

DB2 stores data in table spaces composed of one or many VSAM datasets.

The six type of Table Spaces that DB2 supports are-:

• SIMPLE
• SEGMENTED
• PARTITIONED
• LARGE OBJECTS
• UNIVERSAL
• XML 

Simple Table spaces

1.     Fairly Outdated 

2.     One or many tables in single tablespace

3.     Rows from different tables usually interleaves on same page so locking usually locks rows from other tables also.

4.     Creation not supported in DB2 9.1 but existing tables are supported

Segmented Table Spaces

1.     Organizes pages of the TS into pages

2.     Each segment contains rows of only one table

3.     Size is limited to 64 GB

Number of pages	SEGSIZE
28 or less	4 to 28
28 to 128	32
128 or more	64

4.     Problem of locking rows of other table (Simple Tablespace) is resolved

5.     If a mass delete is performed segment pages are available for immediate reuse.(no need to run reorg)

6.     Mass deletes are much faster

7.     COPY utility doesn’t have to copy empty pages

8.     Eg-:

Create tablespace DEVTS in DEVDB

Using stogroup DSG910

          Priqty 1000

Secqty 50

Segsize 4

Locksize table

Bufferpool bp32

Close no

Ccsid ebcidic

9.  Once set, the SEGSIZE parameter cannot be altered.

Partitioned Tablespaces

1.     Store large amount of data

2.     Total of 4096 partitions can be created (Max Each Partition-64GB)

3.     However total table size is limited to 16 TB

4.     Utilities can be executed on separate partitions in parallel.

5.     Data can be spread over multiple volumes without having to use the same storage group for each dataset

6.     Eg-:

Create tablespace DEVTS in DEVDB

          Using stogroup DEVSG10

          Priqty 20

          Secqty 10

          Erase no

          Numparts 4

                   (part 1 using stogroup DEVSG11

                             Priqty 10

                             Secqty 10

                    Part 3 using stogroup DEVSG12

                             Priqty 20

                             Secqty 10)

Locksize page

Lockmax system

Bufferpool bp32

Close yes

Compress yes

Ccsid ebcidic

Partitions can be added by altering the table

Alter table TABNAME add partition

Universal Table spaces

1.     DB2 9 introduces a new TS.A table spaces that is both segmented and partitioned.

2.     Two types of Universal TS -: a) partition-by-growth TS

b) range-partitioned TS

          3. Partition-By-Growth-:

Use MAXPARTITIONS clause on CREATE TABLESPACE statement to specify the maximum no. of partitions that TS can accommodate. Mainly used to protect from applications that performs an infinite insert loop.

Range-Partitioned  TS-: Specify the NUMPARTS parameter along with SEGSIZE to create the partitions. The integer (Numparts) number of datasets will be created and the data will be inserted according to the partitioning schema of the table.

LOB Table spaces

Every column with any LOB data types requires a LOB TS. The auxiliary table referencing to the BLOB/CLOB column  needs to be created in the LOB TS in the same database.

One LOB TS for each LOB column.

Eg-:

Create LOB tablespace DEVTS

Using stogroup DEVSG10

Priqty 1200

Secqty 1200

Locksize LOB

Bufferpool bp32k2

Log no

XML Tablespaces

1.     When u create a XML column in a table DB2 implicitly creates an XML table space and XML table to store the XML data along with the node ID.

2.     Following objects are also created-:

A hidden column to store the document ID which is a unique generated value that uniquely identifies a row.

A unique index on the document ID.The documented index points to the base tablw RID.