Db2 LUW Tablespaces

Bufferpools and Tablespaces

Bufferpools and Tablespaces are two important aspects when it comes to Storage in Db2. Let's start with basic definitions.

• Page - Basic unit of storage in Db2. There are 4 page sizes. 4K, 8K, 16K, 32K
• Extent - Set of Pages
• Container - Set of Extents
• Tablespaces - These are logical objects

Tablespaces are broadly categorized into two types

1. Basing on space management
1. SMS - System Managed Space - Space in this will be managed by OS - Containers are directory
2. DMS - Database Managed Space - Space in this will be managed by Db2 - Containers are files
3. Automatic storage tablespace - could either SMS or DMS
1. SMS - for storing temporary data
2. DMS - for storing permanent data
2. Basing on type of data they store
1. Regular - to store tradition data
2. Large - to store tradition data + non traditional data - most frequently used and IBM recommended
3. User temporary - to store user temporary tables - DGTT - SESSION schema
4. System temporary - to store data for operations like sorting (Select query order by clause)

How to create tablespace?

db2 "create <tbsp_data_type> tablespace <tablespacename>
PAGESIZE n K
MANAGED BY <space management>
BUFFERPOOL <bufferpoolname>
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON"

To create a SMS tablespace - we will not specify size attributes for SMS tbps because this is managed by OS

db2 "create tablespace  <tablespacename>

PAGESIZE n K
MANAGED BY SYSTEM USING ('containerstring1','containerstring2',...)
BUFFERPOOL <bufferpoolname>
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON"

To create a DMS tablespace 
db2 "create tablespace  <tablespacename>
PAGESIZE n K
MANAGED BY DATABASE USING (FILE 'containerstring1' no_of_pages,
   FILE 'containerstring2' no_of_pages,...)
BUFFERPOOL <bufferpoolname>
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON"

Since space in DMS is limited, we will create tablespace as Autoextend/Autoresize
db2 "create tablespace  <tablespacename>
PAGESIZE n K
MANAGED BY DATABASE USING (FILE 'containerstring1' no_of_pages,
   FILE 'containerstring2' no_of_pages,...)
AUTORESIZE YES INITIALSIZE x  <K|M|G> INCREASESIZE x <K|M|G> MAXSIZE <x <K|M|G>  or NONE>
BUFFERPOOL <bufferpoolname>
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON"

To address the limitations of both these type of tablespaces, we will create Automatic storage tablespaces
db2 "create tablespace  <tablespacename>
PAGESIZE n K
MANAGED BY AUTOMATIC STORAGE
AUTORESIZE YES INITIALSIZE x  <K|M|G> INCREASESIZE x <K|M|G> MAXSIZE <x <K|M|G>  or NONE>
BUFFERPOOL <bufferpoolname>
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON"