Tablespace

A tablespace is a DB2 object that physically stores tables. It is created inside a database, and one or more tables are created inside a tablespace. An indexspace is the matching object that stores an index - an indexspace can hold only one index.

Types of tablespaces

• Simple tablespace :- The default type. It can hold more than one table, but the rows of different tables are mixed together.
• Segmented tablespace :- Ideal for storing more than one table, especially relatively small tables. Each table gets its own segments, so no segment holds rows of more than one table. Mass delete of one table and sequential access are more efficient.
• Partitioned tablespace :- Used for very large tables. The tablespace is divided into partitions (up to 64), each holding boundary values of specific columns. The entire tablespace holds only one table, and utilities can run on one partition at a time.

Tablespace and indexspace

• Tablespaces are created with CREATE TABLESPACE, using either a STOGROUP or a VSAM VCAT for the underlying data sets.
• An indexspace cannot hold more than one index.
• DB2 creates the indexspace automatically when you run CREATE INDEX - you never create it yourself.
• Data in a tablespace is read from DASD into a bufferpool before it is returned to the program.

CREATE TABLESPACE - example

• A segmented tablespace for the CUSTOMER table:-
  
  CREATE TABLESPACE CUSTTS IN MMADBV USING STOGROUP SG1 PRIQTY 720 SECQTY 360 SEGSIZE 4 BUFFERPOOL BP0 LOCKSIZE PAGE;
• PRIQTY and SECQTY set the primary and secondary space allocation, SEGSIZE 4 makes it a segmented tablespace, and LOCKSIZE PAGE sets the locking granularity.