DB2 Basics: Table spaces and buffer pools

This article is written specifically for IBM DB2 Universal database™for Linux, UNIX®, and Windows®

Brief introduction

The design and performance choices for new databases can be confusing for DBAs or future DBAs who have just dabbled in the DB2 domain. In this article, we will discuss two aspects of the DBA's important choices: tablespace and buffer pool. The design and tuning of tablespace and buffer pools can have a profound impact on the performance of DB2 servers, so we will focus on these activities.

In our example, we will use the DB2 V8.1 Enterprise Server Edition. Most examples also apply to low-level versions. We'll let you know if an example applies only to V8.1.

In the 1th section, we'll start with the type that defines the tablespace, and we'll explain how DB2 stores the data in a tablespace. We'll introduce configuration options and introduce you to the entire process of creating and managing tablespaces. Next, we'll focus on the buffer pool, describing what the buffer pool is and how to create and use it. In the 2nd section, we'll combine these two aspects and discuss how to organize the buffer pool and tablespace to get the best performance.

Section 1th: Defining

Table Space

All of the data in the database is stored in many table spaces. You can think of a table space as a child and a database as its parent, in which table spaces (children) cannot have multiple databases (parents). Because table spaces have different uses, they are categorized according to their purpose and management. There are five different table spaces depending on the purpose:

Directory table space Each database has only one table space, which is created when the Create DATABASE command is issued. The directory table space is named Syscatspace by DB2, which holds the system catalog table. The table space is always created when the database is created. Regular table space regular table spaces hold table data and indexes. It can also hold long data, such as large objects (Large Object,lob), unless the data is explicitly stored in a long table space. If some tablespace is a database-managed space (DB Managed Space,dms), you can place the table and its indexes in separate regular table spaces, respectively. We will define the differences between DMS and system managed space (Systems Managed space,sms) later in this article. There must be at least one regular table space in each database. Specifies that the default name for this tablespace is USERSPACE1 when creating the database. Long table space Long table spaces are used to store long or LOB table columns, and they must reside in DMS table spaces. They can also store columns or indexed data of a structured type. If you do not define a long table space, the LOB is stored in a regular table space. Long table spaces are optional and are not created by default. System temporary table space system temporary tablespaces are used to store the internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and connecting tables. Each database must have at least one system temp table space. The default name for the system temp table space created with the database is TEMPSPACE1. User temporary table space user temporary table space stores declared global temporary tables. No user temp table space exists when creating the database. At least one user temporary table space should be created to allow the definition of a declared temporary table. The User temporary table space is optional and is not created by default.

Table Space Management

You can manage table spaces in two different ways:

The system-managed space (SMS) SMS table space is managed by the operating system. The container is defined as a regular operating system file and is accessed through an operating system call. This means that all the regular operating system features will handle the following: The operating system buffers I/O, allocates space according to the operating system Convention, and automatically expands the tablespace if necessary. However, you cannot delete containers from the SMS table space and are limited to adding new containers to the partitioned database. The three default table spaces that are described in the previous section are SMS. The database-managed space (DMS) DMS table space is managed by DB2. You can define a container as a file (the given size will be assigned to them when you create a tablespace) or a device. How much I/O can be managed by the allocation method and how many I/O,DB2 the operating system allows. You can extend a container by using the AltER tablespace command. You can also release unused portions of the DMS container (starting with V8).

Here is an example that shows you how to increase the container size (both V7 and V8 support this feature):

ALTER TABLESPACE TS1
RESIZE (FILE '/conts/cont0' 2000,
DEVICE '/dev/rcont1' 2000,
FILE 'cont2' 2000)

Please note that only V8 supports resizing the original container to a smaller size.

How to create and view table spaces

When you create a database, three tablespaces (Syscatspace, TEMPSPACE1, and USERSPACE1) are created. By using the DB2 Command window or the UNIX command line, create a database named TestDB, connect to the database, and then list the table spaces:

CREATE DATABASE testdb
CONNECT TO testdb
LIST TABLESPACES

Listing 1 below shows the output of the list tablespaces command.

Listing 1. The output of the LIST tablespaces command

Tablespaces for Current Database
Tablespace ID　　　　　　　　　　　　= 0
Name　　　　　　　　　　　　　　　　 = SYSCATSPACE
Type　　　　　　　　　　　　　　　　 = System managed space
Contents　　　　　　　　　　　　　　 = Any data
State　　　　　　　　　　　　　　　　= 0x0000
　Detailed explanation:
　　Normal
Tablespace ID　　　　　　　　　　　　= 1
Name　　　　　　　　　　　　　　　　 = TEMPSPACE1
Type　　　　　　　　　　　　　　　　 = System managed space
Contents　　　　　　　　　　　　　　 = System Temporary data
State　　　　　　　　　　　　　　　　= 0x0000
　Detailed explanation:
　　Normal
Tablespace ID　　　　　　　　　　　　= 2
Name　　　　　　　　　　　　　　　　 = USERSPACE1
Type　　　　　　　　　　　　　　　　 = System managed space
Contents　　　　　　　　　　　　　　 = Any data
State　　　　　　　　　　　　　　　　= 0x0000
　Detailed explanation:
　　Normal

The three table spaces shown above are created automatically by the Create DATABASE command. The user can overwrite the default tablespace creation by including the table space description in the command, but you must create a table space and at least one regular tablespace, and at least one system temporary tablespace, when creating the database. You can create more table spaces of all types (except for catalog spaces) by using the Create DATABASE command or by using the Create Tablespace command later.