Description of the application and precautions of the DB2 tablespace

This article mainly describes the DB2 tablespace. In order to better describe the DB2 tablespace, we will analyze it in depth by using examples, we all know that tablespaces are the logical layers between databases and tables stored in the database. Create a DB2 tablespace in the database and a table in the tablespace.

One obvious advantage of using tablespaces is that data can be reasonably distributed and stored on different disks or in different locations on disks, which helps improve data access efficiency.

DB2 tablespaces are managed in two ways: System Management Space (SMS) and Database Management Space (DMS ).

By type: Rule tablespace, large object tablespace, system temporary tablespace, and user temporary tablespace.

The table that contains user data in the Rule tablespace. The Default User tablespace name is USERSPACE1, and the index is also stored in the Rule tablespace. In addition, the system directory table is also stored in the Rule tablespace. The default system directory tablespace is SYSCATSPACE.

Temporary tablespace is divided into system temporary tablespace and user temporary DB2 tablespace.

The system temporary tablespace is used to store internal temporary data required for various data operations (sorting, reorganizing tables, creating indexes, and connecting tables). Although you can create any number of system temporary tablespaces, however, we recommend that you only create one table with the page size used by most tables. The default temporary tablespace name is TEMPSPACE1.

The user's temporary DB2 tablespace is used to store the described global temporary table (it is described that the global temporary table stores the application temporary data ). The user's temporary tablespace is not created by default during database creation.

Each SMS container is a directory in the file space of the operating system. Each DMS container is a fixed, pre-allocated file, or physical device.

The management of SMS is relatively simple and is automatically managed by the operating system. The size of the space is automatically adjusted as the data volume changes.

DMS is managed by databases. The size of the space is determined during creation. When the space is insufficient, you must manually add or delete some data to release the space.

In most cases, DMS performs better than SMS.

Simple syntax for creating SMS tablespace using command line:

 
 

  
  
CREATE TABLESPACE ;   
  
  
MANAGED BY SYSTEM USING (' ;') 
 
 

Example 1: Create an SMS tablespace on Windows:

 
 

  
  
CREATE TABLESPACE RESOURCE MANAGED BY SYSTEM USING ('d:\acc_tbsp','e:\acc_tbsp','f:\acc_tbsp') 
 
 

The result is that three folders named acc_tbsp are created on the D, E, and F disks. Each folder contains a file named SQLTAG. NAM.

Example 2: delete the SMS tablespace created in Example 1:

 
 

  
  
DROP TABLESPACE RESOURCE 
 
 

After running this command, the corresponding tablespace will be deleted from the DB2 registry, but the three folders on the disk still exist and need to be deleted manually.

Simple syntax for creating DMS tablespace using command line:

 
 

  
  
CREATE TABLESPACE ;   
  
  
MANAGED BY DATABASE USING (FILE ' ;' 
 
 

Or

 
 

  
  
CREATE TABLESPACE ;   
  
  
MANAGED BY DATABASE USING (DEVICE ' ;' 
 
 

Example 3: Create a DMS tablespace on Windows and use two file containers with their respective 5000 pages:

 
 

  
  
CREATE TABLESPACE RESOURCE MANAGED BY DATABASE USING (FILE 'd:\db2data\acc_tbsp' 5000, FILE 'e:\db2data\acc_tbsp' 5000) 
 
 

The running result is: An acc_tbsp file is created under the db2data folder on disk D and disk E. The file size is 20000 KB (4 kb by default ).

Example 4: Add a container to the tablespace created in Example 3:

 
 

  
  
ALTER TABLESPACE RESOURCE ADD(FILE 'f:\db2data\acc_tbsp' 5000) 
 
 

The running result is that the RESOURCE tablespace contains three containers (Files): d: \ db2data \ acc_tbsp, e: \ db2data \ acc_tbsp, and f: \ db2data \ acc_tbsp.

Example 5: Use the RESIZE clause to change the container size of the tablespace created in Example 3:

 
 

  
  
ALTER TABLESPACE RESOURCE RESIZE (file 'd:\db2data\acc_tbsp' 8000, file 'e:\db2data\acc_tbsp' 8000, file 'f:\db2data\acc_tbsp' 8000) 
 
 

The size of each container (File) is changed to 8000 pages. Increasing the container capacity will not cause errors. However, if the container data is full and the container capacity is reduced, an error will occur.

Example 6: Use the EXTEND clause to change the container size of the tablespace created in Example 3:

 
 

  
  
ALTER TABLESPACE RESOURCE EXTEND (file 'd:\db2data\acc_tbsp' 1000, file 'e:\db2data\acc_tbsp' 1000, file 'f:\db2data\acc_tbsp' 1000) 
 
 

The running result of this command is based on the original capacity, and each container adds another 1000 pages.

Example 7: Delete the DMS tablespace created in Example 3:

 
 

  
  
DROP TABLESPACE RESOURCE 
 
 

The running result is to delete the RESOURCE tablespace In the DB2 registry, and automatically delete the corresponding folders and files on the disk.

Example 8: Create a DMS tablespace on UNIX and use three logical volumes on each of the 10000 pages:

 
 

  
  
CREATE TABLESPACE RESOURCE MANAGED BY DATABASE USING (DEVICE '/dev/rdblv6' 10000, DEVICE '/dev/rdblv7' 10000, DEVICE '/dev/rdblv8' 10000) 
 
 

The UNIX device mentioned in the preceding statement must already exist, and the instance owner and SYSADM group must be able to write them.

Features SMS DMS

Can I dynamically increase the number of containers in the tablespace? N Y

Can I store index data in tables with different DB2 tablespaces? N Y

Can I store big object data in tables with different tablespaces? N Y

Can a table be scattered and stored in multiple tablespaces? N Y

Is space allocated only when needed? Y N

Can tablespaces be stored in different disks? Y N

After the partition is created, can the partition size be changed? N

Example 9: create a temporary system tablespace:

 
 

  
  
CREATE SYSTEM TEMPORARY TABLESPACE tmp_tbsp MANAGED BY SYSTEM USING(‘d:\tmp_tbsp’,’e:\tmp_tbsp’) 
 
 

System temporary tables can only be stored in the system temporary tablespace. Therefore, the database must always have at least one system temporary tablespace.

Example 10: create a user temporary tablespace:

 
 

  
  
CREATE USER TEMPORARY TABLESPACE usr_tbsp MANAGED BY DATABASE USING(FILE ‘d:\db2data\user_tbsp’ 5000, FILE ‘e:\db2data\user_tbsp’ 5000) 
 
 

User TEMPORARY tablespace is used to store the specified temporary table (defined using the declare global temporary table Statement)

Example 11: RENAME the tablespace using the RENAME statement:

 
 

  
  
RENAME TABLESPACE RESOURCE TO RES1 
 
 

After renaming a tablespace with this statement, all directory records that reference the DB2 tablespace are automatically changed, so you do not need to care about the individual objects in the tablespace.

Example 12: Create a table named T1 in the RESOURCE tablespace:

 
 

  
  
CREATE TABLE T1(ABC INT) IN RESOURCE 
 
 

The above content describes the DB2 tablespace for the instance, hoping to help you in this regard.