Tablespace Introduction

A tablespace is the physical space where DB2 tables are stored. Every table lives inside a tablespace.

What is a tablespace

• Tablespaces are the physical space where the tables are stored. The actual data rows sit in the pages of a tablespace.
• A tablespace is created inside a database. A database is a logical grouping that consists of tablespaces, indexspaces, views, tables and so on.
• DB2 supports locking at three levels: tablespace level, table level and page level.
• Utilities such as COPY, REORG, RUNSTATS and RECOVER run at the tablespace level, so tablespace design affects maintenance and performance.

Tablespace in the DB2 object hierarchy

• The DB2 object hierarchy is: DATABASE at the top, then TABLESPACE, then TABLE.
• Indexes are stored separately in an indexspace. An indexspace cannot hold more than one index.
• DB2 creates the indexspace automatically when you run the CREATE INDEX statement.
• Views, aliases and synonyms are logical objects. They do not store data, so they do not need a tablespace.

Creating a tablespace

• A tablespace is created with the CREATE TABLESPACE statement:-
• CREATE TABLESPACE EMPTS
     IN MM01DB
     USING STOGROUP SYSDEFLT
     PRIQTY 100
     SECQTY 100
     SEGSIZE 32;
• The IN clause names the database, and the USING clause names the storage group or VSAM data sets that hold the physical space.
• A storage group is a collection of direct access volumes, all of the same type, and it can have a maximum of 133 volumes.
• After the tablespace exists, tables are placed in it with the IN clause of CREATE TABLE.