DB2 tablespace Management

How should we manage the DB2 tablespace? This is a problem that many people have mentioned. Before solving this problem, let's take a look at what DB2 tablespace is:

DB2 tablespace

All data in the database is stored in many tablespaces. It can be considered that the tablespace is a child, and the database is its parent, where the tablespace child) cannot have multiple database parent ). Since tablespaces have different purposes, they are classified based on their purposes and management methods. There are five different tablespaces based on their purposes:

Directory tablespace
Each DATABASE has only one directory tablespace, which is created when the create database Command is issued. The directory tablespace is named SYSCATSPACE by DB2, which stores the system directory table. This tablespace is created every time a database is created.
Regular tablespace
Regular tablespaces store all permanent data, including regular tables and indexes. It can also save Long data such as LOBLarge Object, unless the data is explicitly stored in the long table space. If some tablespaces are used for Database management of non-partition tables, Database Managed Space, DMS) or system management Space for partition tables, you can place the table and its indexes in a separate regular tablespace. We will describe DMS and SMS in the tablespace Management Section. A directory tablespace is an example of a regular tablespace. By default, the directory tablespace is the only regular tablespace created during database creation.
Long table space
A long table space stores permanent data like a regular tablespace, including LOB. The tablespace must be DMS, which is also the default type. The tables created in a long table space are larger than those in a regular table space. Large tables support more than 255 rows per data page to improve the space utilization on the data page. When creating a database, DB2 creates a long table space named USERSPACE1.
Temporary system tablespace
Temporary system tablespace is used to store internal temporary data required during SQL operations such as sorting, reorganizing tables, creating indexes, and connecting tables. Each database must have at least one temporary system tablespace. The default temporary system tablespace created with the database is named TEMPSPACE1.
User temporary tablespace
The user temporary tablespace stores declared global temporary tables. The user's temporary tablespace does not exist when the database is created. At least one user temporary tablespace should be created to allow the definition of declared temporary tables. The user's temporary tablespace is optional and is not created by default.

DB2 tablespace Management

You can manage the DB2 tablespace in two different ways:

System Management space SMS)
The operating system manages the SMS tablespace. A container is defined as a regular operating system file and accessed through an operating system call. This means that all of the following tasks are handled by conventional operating system functions:
I/O Buffer
Allocate space according to operating system conventions
Automatically expand tablespaces if necessary
However, you cannot delete containers from the SMS tablespace, and only add new containers to the partitioned database. The three default tablespaces described in the previous section are SMS.

Database Management space DMS)
DMS tablespace is managed by DB2. You can define a container as a file and allocate all the given sizes to it when creating a tablespace) or a device. DB2 can manage how much I/O is allowed by the allocation method and operating system. You can use the alter tablespace command to expand the container. You can also release unused DMS containers from V8 ).
 
 

DB2 Management page size limit

Policy Selection for DB2 environment variable Management

Several Simple DB2 operation statements

Read stability at the DB2 isolation level

DB2 isolation level-Repeatable read