Db2 简明教程
DB2 - Tablespaces
本章将详细说明表空间
This chapter describes the tablespaces in detail
Introduction
表空间是一种存储结构，其中包含表、索引、大型对象和长数据。可用于将数据库中的数据组织到与数据在系统上的存储位置相关的逻辑存储组中。这些表空间存储在数据库分区组中
A table space is a storage structure, it contains tables, indexes, large objects, and long data. It can be used to organize data in a database into logical storage group which is related with where data stored on a system. This tablespaces are stored in database partition groups
Benefits of tablespaces in database
表空间在数据库中有以下多种好处：
The table spaces are beneficial in database in various ways given as follows:
Recoverability : 表空间让备份和恢复操作更方便。使用单个命令，您可以在表空间中备份或恢复所有数据库对象。
Recoverability: Tablespaces make backup and restore operations more convenient. Using a single command, you can make backup or restore all the database objects in tablespaces.
Automatic storage Management : 数据库管理器会根据需要创建和扩展容器。
Automatic storage Management: Database manager creates and extends containers depending on the needs.
Memory utilization : 单个缓冲池可以管理多个表空间。您可以为临时表空间指定它们自己的缓冲池，以提高诸如排序或联接之类的活动的性能。
Memory utilization: A single bufferpool can manage multiple tablespaces. You can assign temporary tablespaces to their own bufferpool to increase the performance of activities such as sorts or joins.
Container
表空间包含一个或多个容器。容器可以是目录名、设备名或文件名。在数据库中，单个表空间可以在同一物理存储设备上拥有多个容器。如果表空间是通过自动存储表空间选项创建的，则容器的创建和管理将由数据库管理器自动处理。如果未使用自动存储表空间选项创建，则您需要自己定义和管理容器。
Tablespaces contains one or more containers. A container can be a directory name, a device name, or a filename. In a database, a single tablespace can have several containers on the same physical storage device. If the tablespace is created with automatic storage tablespace option, the creation and management of containers is handled automatically by the database manager. If it is not created with automatic storage tablespace option, you need to define and manage the containers yourself.
Default tablespaces
当创建一个新数据库时，数据库管理器会为数据库创建一些默认表空间。此表空间用于存储用户和临时数据。每个数据库至少需要包含三个表空间，如下所示：
When you create a new database, the database manager creates some default tablespaces for database. These tablespace is used as a storage for user and temporary data. Each database must contain at least three tablespaces as given here:
Catalog tablespace : 其中包含数据库的系统目录表。它被命名为 SYSCATSPACE，并且无法删除。
Catalog tablespace: It contains system catalog tables for the database. It is named as SYSCATSPACE and it cannot be dropped.
User tablespace : 此表空间包含用户定义的表。在数据库中，我们有一个默认的用户表空间，名为 USERSPACE1。如果您在创建表时没有指定为此表指定用户定义表空间，则数据库管理器将为您选择默认用户表空间。
User tablespace: This tablespace contains user-defined tables. In a database, we have one default user tablespace, named as USERSPACE1. If you do not specify user-defined tablespace for a table at the time you create it, then the database manager chooses default user tablespace for you.
Temporary tablespace : 临时表空间包含临时表数据。此表空间包含系统临时表空间或用户临时表空间。
Temporary tablespace: A temporary tablespace contains temporary table data. This tablespace contains system temporary tablespaces or user temporary tablespace.
系统临时表空间在数据库管理器执行诸如排序或联接之类的操作时保存所需临时数据。数据库必须至少有一个系统临时表空间，它被命名为 TEMPSPACE1。它在创建数据库时创建。用户临时表空间保存来自表的临时数据。使用 DECLARE GLOBAL TEMPORARY TABLE 或 CREATE GLOBAL TEMPORARY TABLE 语句创建。在创建数据库时不会默认创建此临时表空间。
System temporary tablespace holds temporary data required by the database manager while performing operation such as sorts or joins. A database must have at least one system temporary tablespace and it is named as TEMPSPACE1. It is created at the time of creating the database. User temporary tablespace holds temporary data from tables. It is created with DECLARE GLOBAL TEMPORARY TABLE or CREATE GLOBAL TEMPORARY TABLE statement. This temporary tablespace is not created by default at the time of database creation.
Tablespaces and storage management:
Tablespaces and storage management:
根据您希望如何使用表空间可以使用不同的方法设置表空间。您可以设置操作系统来管理表空间分配、也可以让数据库管理器分配空间或者为您的数据选择自动分配表空间。
Tablespaces can be setup in different ways, depending on how you want to use them. You can setup the operating system to manage tablespace allocation, you can let the database manager allocate space or you can choose automatic allocation of tablespace for your data.
有以下三种类型的管理空间可用：
The following three types of managed spaces are available:
System Managed Space (SMS) : 操作系统的文件系统管理器分配和管理存储表的空间。按需分配存储空间。此模型包含表示数据库对象的多个文件。此表空间类型已在用户定义的表空间的 10.1 版中弃用，并未在目录和临时表空间中弃用。
System Managed Space (SMS): The operating system’s file system manager allocates and manages the space where the table is stored. Storage space is allocated on demand. This model consists of files representing database objects. This tablespace type has been deprecated in Version 10.1 for user-defined tablespaces, and it is not deprecated for catalog and temporary tablespaces.
Database Managed Space (DMS) : 数据库服务器控制存储空间。按您在创建 DMS 表空间时指定的容器定义在文件系统上预分配存储空间。从用户定义的表空间的 10.1 版修复包 1 中弃用，但并未在系统表空间和临时表空间中弃用。
Database Managed Space (DMS): The Database Server controls the storage space. Storage space is pre- allocated on the file system based on container definition that you specify when you create the DMS table space. It is deprecated from version 10.1 fix pack 1 for user-defined tablespaces, but it is not deprecated for system tablespace and temporary tablespace.
Automatic Storage Tablespace : 可以自动管理数据库服务器。数据库服务器根据数据库中的数据创建和扩展容器。使用自动存储管理，无需提供容器定义。数据库服务器会负责创建和扩展容器，以利用分配给数据库的存储。如果向存储组添加存储空间，则在现有容器达到其最大容量时会自动创建新的容器。如果您想立即使用新添加的存储，则可以重新平衡表空间。
Automatic Storage Tablespace: Database server can be managed automatically. Database server creates and extends containers depend on data on database. With automatic storage management, it is not required to provide container definitions. The database server looks after creating and extending containers to make use of the storage allocated to the database. If you add storage space to a storage group, new containers are automatically created when the existing container reach their maximum capacity. If you want to use the newly-added storage immediately, you can rebalance the tablespace.
Page, table and tablespace size:
Page, table and tablespace size:
临时 DMS 和自动存储表空间，您为数据库选择的页面大小确定表空间大小的最大限制。对于表 SMS 和临时自动存储表空间，页面大小限制表本身的大小。页面大小可以是 4kb、8kb、16kb 或 32kb。
Temporary DMS and automatic storage tablespaces, the page size you choose for your database determines the maximum limit for the tablespace size. For table SMS and temporary automatic storage tablespaces, the page size constrains the size of table itself. The page sizes can be 4kb, 8kb, 16kb or 32kb.
Tablespace type |
4K page size limit |
8K page size limit |
16K page size limit |
32K page size limit |
DMS, non-temporary automatic storage tablespace regular |
64G |
128G |
256G |
512G |
DMS, temporary DMS and non- temporary automatic storage table space large |
1892G |
16384G |
32768G |
65536G |