db2表空间管理详解

select substr(tbsp_name,1,20) as TABLESPACE_NAME,substr(tbsp_content_type,1,10)
as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as
TOTAL_MB,sum(tbsp_used_size_kb)/1024 as USED_MB,sum(tbsp_free_size_kb)/1024 as
FREE_MB,tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION group by
tbsp_name,tbsp_content_type,tbsp_page_size order by 1
上述语句可以查看各个表空间的使用率

db2创建数据库：

在发出 CREATE DATABASE 命令时，DB2 会创建许多文件，这个在前面已经见过。这些文件包括日志文件、配置信息、历史文件和三个表空间。这些表空间是：
SYSCATSPACE：这是保存 DB2 系统编目的地方，系统编目跟踪与 DB2 对象相关联的所有元数据。
TEMPSPACE1：DB2 用来放置中间结果的临时工作区域。
USERSPACE1：默认情况下存放所有用户对象（表、索引）的地方。
所有这些文件都放在默认驱动器上的 DB2 目录中。默认驱动器通常是安装 DB2 产品的卷。
对于简单的应用程序，这个默认配置应该可以满足需要。但是，可能希望改变数据库文件的位置，或者改变 DB2 管理这些对象的方式。
接下来，我们将更详细地研究 CREATE DATABASE 命令。
对于从 DB2 8 进行迁移的用户，有一个特殊的注意事项：在 DB2 9 之前，CREATE DATABASE 命令会为上面列出的所有对象创建 SMS 表空间。
在 DB2 9 中，所有表空间都将定义为自动存储（DMS）表空间。
以下的 CREATE DATABASE 命令将数据库放在 Windows 操作系统下的 D: 驱动器上的 TEST 目录中：
CREATE DATABASE MYDB ON D:\TEST
选择 Automatic storage（默认设置）允许 DBA 为数据库设置在创建所有表空间容器时可以使用的存储路径。
DBA 不必显式地定义表空间的位置和大小，系统将自动地分配表空间。例如，下面的数据库创建语句将为数据库中的所有表空间设置自动存储。
CREATE DATABASE TEST
AUTOMATIC STORAGE ON /db2/storagepath001,
/db2/storagepath002,
/db2/storagepath003
AUTORESIZE YES
INITIALSIZE 300 M
INCREASESIZE 75 M
MAXSIZE NONE
在 AUTOMATED STORAGE ON 选项后面，给出了三个文件目录（路径）。这三个路径是一个表空间的容器的位置。其他的选项是：
AUTORESIZE YES 当表空间用光空间时，系统将自动地扩展容器的大小。 INITIALSIZE 300 M 没有定义初始大小的任何表空间的大小默认为 300 MB。每个容器是 100 MB（有三个存储路径）。 INCREASESIZE 75 M （或百分数） 当表空间用光空间时，表空间的总空间增加 75 MB。还可以指定一个百分数，在这种情况下，表空间会增长它的当前大小的百分数。 MAXSIZE NONE 表空间的最大大小没有限制。
如果 DBA 希望限制一个表空间可以占用的存储空间，那么可以指定一个最大值。
当使用 AUTOMATIC STORAGE 定义表空间时

，不需要提供其他参数：
CREATE TABLESPACE TEST MANAGED BY AUTOMATIC STORAGE;
在这个命令中，可以提供与表空间相关联的任何参数；但是，使用自动存储可以大大简化日常的表空间维护。与重要的大型生产表相关联的表空间可能需要 DBA 更多地干预。 在没有启用自动存储的数据库中创建表空间时，必须指定 MANAGED BY SYSTEM 或 MANAGED BY DATABASE 子句。使用这些子句会分别创建 SMS 表空间和 DMS 表空间。在这两种情况下，都必须提供容器的显式列表。
如果数据库启用了自动存储，那么有另一个选择。可以指定 MANAGED BY AUTOMATIC STORAGE 子句，或者完全去掉 MANAGED BY 子句（这意味着自动存储）。在这种情况下，不提供容器定义，因为 DB2 会自动地分配容器。

代码页和整理次序
所有DB2字符数据类型（CHAR、VARCHAR、CLOB、DBCLOB）都有一个相关联的字符代码页。可以认为代码页是一个对照表，用来将字母数字数据转换为数据库中存储的二进制数据。
一个 DB2 数据库只能使用一个代码页。代码页是在 CREATE DATABASE 命令中使用 CODESET 和 TERRITORY 选项设置的。
代码页可以使用单一字节表示一个字母数字字符（单一字节可以表示 256 个独特元素），也可以使用多个字节。
英语等语言包含的独特字符相当少；因此单字节代码页对于存储数据足够了。
日语等语言需要超过 256 个元素才能表示所有的独特字符；因此需要多字节代码页（通常是双字节代码页）。
在默认情况下，数据库的整理次序根据 CREATE DATABASE 命令中使用的代码集进行定义。如果指定选项 COLLATE USING SYSTEM，就根据为数据库指定的 TERRITORY 对数据值进行比较。如果使用选项 COLLATE USING IDENTITY，那么以逐字节的方式使用二进制表示来比较所有值。
DB2 Administration Guide 列出了创建数据库时可用的各种代码页。在大多数情况下，DBA 会让这个设置保持为数据库所在的操作系统的默认代码页。
对于需要使用 XML 数据的应用程序，有一个特殊的注意事项。当前，DB2 只在定义为 Unicode（UTF-8）的数据库中支持 XML 列。如果数据库在创建时没有启用 Unicode 支持，就不能在其中创建 XML 列


create tablespace语法树



>>-CREATE
--+-----------------------+---------------------------->
+-LARGE-----------------+
+-REGULAR---------------+
| .-SYSTEM-. |
'-+--------+--TEMPORARY-'
'-USER---'

>--TABLESPACE--tablespace-name---------------------------------->

>--+-----------------------------------------------------------+-->
| .-DATABASE PARTITION GROUP-. |
'-IN--+--------------------------+--db-partition-group-na

me-'

>--+--------------------------+--------------------------------->
'-PAGESIZE--integer--+---+-'
'-K-'

.-MANAGED BY--AUTOMATIC STORAGE--| size-attributes |---------------------.
>--+------------------------------------------------------------------------+-->
'-MANAGED BY--+-SYSTEM--| system-containers |--------------------------+-'
'-DATABASE--| database-containers |--| size-attributes |-'

>--+---------------------------------+-------------------------->
'-EXTENTSIZE--+-number-of-pages-+-'
'-integer--+-K-+--'
'-M-'

>--+-----------------------------------+------------------------>
'-PREFETCHSIZE--+-AUTOMATIC-------+-'
+-number-of-pages-+
'-integer--+-K-+--'
+-M-+
'-G-'

>--+-----------------------------+------------------------------>
'-BUFFERPOOL--bufferpool-name-'

>--+----------------------------------+------------------------->
'-OVERHEAD--number-of-milliseconds-'

>--+------------------------+----------------------------------->
+-NO FILE SYSTEM CACHING-+
'-FILE SYSTEM CACHING----'

>--+--------------------------------------+--------------------->
'-TRANSFERRATE--number-of-milliseconds-'

>--+---------------------------------+-------------------------><
'-DROPPED TABLE RECOVERY--+-ON--+-'
'-OFF-'
size-attributes

|--+---------------------+--+-----------------------------+----->
'-AUTORESIZE--+-NO--+-' '-INITIALSIZE--integer--+-K-+-'
'-YES-' +-M-+
'-G-'

>--+------------------------------------+----------------------->
'-INCREASESIZE--integer--+-PERCENT-+-'
'-+-K-+---'
+-M-+
'-G-'

>--+-----------------------------+------------------------------|
'-MAXSIZE--+-integer--+-K-+-+-'
| +-M-+ |
| '-G-' |
'-NONE-----------'

system-containers
.----------------------------------------------------------------------.
| .-,------------------. |
V V | |
|----USING--(----'container-string'-+--)--+-----------------------------+-+--|
'-| on-db-partitions-clause |-'

database-containers
.--------------------------------------------------------------.
V |
|----USING--| container-clause |--+-----------------------------+-+--|

'-| on-db-partitions-clause |-'

container-clause

.-,---------------------------------------------------.
V |
|--(----+-FILE---+--'container-string'--+-number-of-pages-+-+--)--|
'-DEVICE-' '-integer--+-K-+--'
+-M-+
'-G-'

on-db-partitions-clause

|--ON--+-DBPARTITIONNUM--+-------------------------------------->
'-DBPARTITIONNUMS-'

.-,--------------------------------------------------.
V |
>--(----db-partition-number1--+--------------------------+-+--)--|
'-TO--db-partition-number2-'


参数详解
https://www.wendangku.net/doc/cb443199.html,RGE, REGULAR, SYSTEM TEMPORARY, or USER TEMPORARY
指定要创建的表空间的类型，如果不指定，默认类型是由”MANAGED BY”字句决定的。
LARGE:只存储永久性数据。这个类型只允许用在DMS表空间上。它也是创建DMS表空间的默认类型。
当一个表放置在一个large类型的表空间中：
这个表可以比放置在regular类型的表空间中更大。
这个表可以支持每个data page有超过255行记录，这样可以增加空间利用效用。
这个表上的索引需要每行多出额外的2字节，和regular表空间上的索引对比。
REGULAR：只存储永久性数据。这个类型可以用在DMS和SMS表空间。这个类型也是唯一可以用在SMS表空间的类型，同时也是SMS的默认类型。
SYSTEM TEMPORARY 存储临时表，和完成一些排序和连接等操作的工作区。一个数据库至少有一个系统临时表空间，因为临时表只能放置在这种表空间中。
在数据库创建时，自动创建一个临时表空间。由于临时表空间涉及到空间回收，故常采用SMS表空间
USER TEMPORARY 存储创建的临时表和声明的临时表。当数据库被创建时，没有用户临时表空间存在。为了允许定义临时表和声明临时表，至少有一个用户临时表空间被创建，
通过使用USE privileges. 由于临时表空间涉及到空间回收，故常采用SMS表空间
注:当存在多个临时表空间时，采取循环方式，平衡他们的使用。

2.tablespace-name
表空间的名称，这是一个整体名字，是一个SQL标示符。不能使用在catalog中存在的表空间名称，也不能以’SYS’开头命名表空间。
表空间所有者创建表空间，针对于这个表空间，被授予了USE privilege with the WITH GRANT OPTION。

3.IN DATABASE PARTITION GROUP db-partition-group-name
为表空间指定数据库分区组。数据库分区组必须已存在。当创建系统临时表空间时，唯一允许的分区组是IBMTEMPGROUP。
‘DATABASE PARTITION GROUP

’关键词是不强制的，可有可无，并不影响参数。
如果创建表空间时，没有指定分区组，Regular, Large和User Temporary类型使用IBMDEFAULTGROUP分组区；SYSTEM TEMPORARY类型，使用IBMTEMPGROUP类型。

4.PAGESIZE integer [K]
为表空间定义页大小page size。没有指定后缀K的情况下，有效值是4096,8192,16384,32768；有后缀K的情况下，有效值是4,8,16,32。如果不是这些有效值，会报错。
当表空间的page size和所指定的buffer pool的page size大小不一样，也会报错。pagesize的默认值在db cfg中可以找到，这个参数是在创建数据库时设置的。

5.MANAGED BY AUTOMATIC STORAGE
指定表空间为自动存储的表空间。如果数据库没有定义automatic storage，会报错。可以为SMS或DMS指定为自动存储。
| size-attributes |语句块
AUTORESIZE：指定DMS表空间是否开启auto-resize功能，当表空间要满时，自动增加大小。其他参数见上面语法图。

6.MANAGED BY SYSTEM
指定表空间为SMS，系统管理表空间。当type没有指定，默认行为是创建regular表空间。
| system-containers |语法块，指定SMS表空间的容器。
USING (‘container-string’,…)
对于SMS表空间，指定一个或多个容器存储数据。container-string的长度超过240字节。每一个container-string可以是绝对或相对路径。
注:如果用相对路径，是相对于数据库目录，而且可以是软连接。如果指定的文件夹不存在，数据库管理器会自动创建这个文件夹。如果表空间被删除，所有被数据库管理器创建的组件也会被删除。如果指定的文件夹已经存在，这个文件夹不能包含任何文件和子目录。
container-string的格式依赖于操作系统。windows下，绝对路径以盘符和:开始；Unix中，绝对路径以/开始。相对路径形式一样。
也可以支持远程资源，如LAN-redirected drives或NFS-mounted文件系统。
| on-db-partitions-clause | 指定容器在一个分区数据库中。如果没有指定，在分区组中找一个分区创建。如果是系统临时表，则在每个分区中创建。
7.MANAGED BY DATABASE
指定表空间是DMS，数据库管理表空间。当type没有指定，默认是large。
| database-containers | 语法块，为DBS表空间指定容器。
USING |container-clause|
(FILE|DEVICE ‘container-string’ number-of-pages,…)
对于DMS表空间，指定一个或多个container存储数据。容器类型(FILE或DEVICE)和大小(没有单位，默认是page的数量)需要指定。大小也可以指定单位，K,M,G，如果指定单位，用字节数除pagesize然后下取整，来决定page个数。container-stiring不能超过254个字节。
对于FILE的容器，必须是绝对或相对路径。如果目录中没有任何组件，它会被数据库管理器创建。如果文件不存在，它会被创建和初始化到指定大小。当表

空间被删除，所有组件也被删除。如果文件存在，会被覆盖，如果文件小于指定大小，会被扩展，如果大于指定大小，并不会被截断。
对于DEVICE容器，container-string必须是设备名，设备必须已存在。
所有容器必须在所有数据库中是唯一的。一个容器只能属于一个表空间。一个表空间中不同容器的大小可以不同；但是，一个表空间中的容器大小一样，可以获得更好的性能。container-string的精确的形式和操作系统有关。
远程资源也是支持的，同SMS。
|on-db-partitions-clause|，同SMS。
8.EXTENTSIZE number-of-pages
指定一个EXTENTSIZE，在跳转到下一个容器前，需要在当前容器中的写入的页数。
也可以指定单位，K,M，最后的extent size为字节数除pagesize下取整。数据库管理器会根据extent size，循环使用容器存储数据。 根据DB2对容器进行循环写入的原则，如果您选择实现磁盘条带分割以及DB2条带分割，那么表空间的扩展数据块大小和磁盘的分割大小应该相同，这样会使性能更加优化。
EXTENTSIZE的默认值在db cfg中可以找到，DFT_EXTENT_SZ，创建DB时候设置的，这个值有效值是2-256。
9.PREFETCHSIZE
在查询之前，取出查询所需要的数据，这样不需要等待I/O。
默认值由db cfg中的dft_prefetch_sz决定。
默认为AUTOMATIC指定prefetch size为自动更新，由数据库管理器控制。当容器增加等，会自动调整。
number-of-pages指定prefetch的页数，也可以用单位，也是通过字节自动计算页数。
10.BUFFERPOOL bufferpool-name
指定这个表空间使用的缓冲池，这个缓冲池必须已存在。如果没有指定，默认的缓冲池(IBMDEFAULTBP)将被使用。缓冲池的的页大小必须和表空间的页大小一样。
11.OVERHEAD number-of-milliseconds
指定I/O控制器开销和磁盘寻道和延迟时间。这个值用于在查询优化时决定I/O开销，可以是任何数字，包括浮点。如果表空的容器OVERHEAD值不同，则取平均值。db2 v9以上，这个值默认是7.5，从v9之前升级过来的，默认是12.67.
12.FILE SYSTEM CACHING or NO FILE SYSTEM CACHING
指定I/O操作是否在操作系统级别进行缓存。如果不指定这个参数，默认值如下：
在JFS on AIX, Linux System z?, all non-VxFS file systems on Solaris, HP-UX，SMS临时表空间，所有LOB和large data，都默认使用FILE SYSTEM CACHING。在所有其他平台和文件系统中，默认使用NO FILE SYSTEM
FILE SYSTEM CACHING在目标表空间上的I/O操作，会在操作系统级别上进行缓存。
NO FILE SYSTEM CACHING指定I/O操作绕过操作系统级别缓存。
13.TRANSFERRATE number-of-milliseconds
指定读一个page到内存的时间。这个值用来决定在查询优化时，I/O的成本。可以是浮点数，如果每个容器的值不同，取平均。V9以前版本，创建

的DB，默认值是0.06毫秒，从V9以前版本升级到V9之后，默认值是0.18毫秒。
14.DROPPED TABLE RECOVERY
指明被删除的表是否可以被恢复，通过使用”ROLLFORWARD DATABASE …RECOVER DROPPED TABLE”。
ON，指定被删除的表，可以恢复。这是V8之后是默认值。
OFF，被删除的表不能恢复。V7的默认值。

表空间日常管理命令
本例创建的表空间pagesize大部分为32kb，故
1、创建pagesize为32kb的bufferpool
create bufferpool bp32k size 2000 pagesize 32k
2、查看bufferpool属性
select * from syscat.bufferpools
3、更改缓冲池大小
alter bufferpool bp32k size 2000
4、创建数据页为32kb数据块为1024kb预取值为默认，且不使用文件系统缓存的大型DMS表空间
create large tablespace tbs_data pagesize 32k managed by database using (file '/database/tbs_data/cont0' 100M,file '/database/tbs_data/cont1' 100M) extentsize 32 prefetchsize automatic bufferpool bp32k no file system caching
5、创建 数据页为32kb的SMS系统临时表空间
create temporary tablespace tbs_temp pagesize 32k managed by system using ('/database/tbs_temp') bufferpool bp32k
6、创建 数据页为32kb的SMS用户临时表空间
create user temporary tablespace tbs_user_temp pagesize 32k managed by system using('/database/tbs_usertemp') bufferpool bp32k
7、创建 数据页为32kb的自动存储管理表空间
create tablespace tbs_index pagesize 32k bufferpool bp32k
8、创建初始大小为100MB，增量为100MB，最大大小为100GB的 自动存储管理表空间
create tablespace tbs_data2 initialsize 100M increasesize 100M maxsize 100G
9、查看表空间信息
list tablespaces
list tablespaces show detail
db2pd -d testdb -tablespaces
get snapshot for tablespaces on testdb
也可以查看sysibmadm.snaptbsp和sysibmadm.snapcontainer这两个视图
10、查看表空相关的容器信息
list tablespace containers for 7 #tablespace id
list tablespace containers for 7 show detail
11、如果DMS表空间对应的存储中还有未分配空间，可通过alter tablespace的extend、reduce或resize选项扩展已有表空间容器的大小。其中extend用来扩展容器大小，reduce用来缩减已有容器大小，resize重新设定容器大小。
对于reduce和resize，需确保更改后的表空间有足够空间，否则DB2拒绝操作。
alter tablespace tbs_data extend (file '/database/tbs_data/cont0' 10M)
12、如果表空间容器已经没有剩余空间，可通过add选项增加容器，当然必要的情况下也可以通过drop选项删除容器。对于add和drop操作会在容器间发生数据重新平衡(rebalance)。如果数据量很大，rebalance时间有可能很久，对系统可能造成很大影响。
alter tablespace tbs_data add(file '/database/tbs_data/cont2' 100m)
13、如果使用begin new strip set选项则会在现有容器空间用完时使用新增容器，该选项使数据不会在容器间做reba

lance，但会造成数据偏移。
alter tablespace tbs_data begin new stripe set (file '/database/tbs_data/cont3' 100m)
14、查看自动存储管理表空间路径
db2pd -d testdb -storagepaths
15、对于自动存储管理表空间，无法在表空间级进行容器更改，只能在数据级别，因为自动存储路径是在建库时指定的。可以使用add storage on选项为数据库添加新的存储路径。在v9.7版本之前，自动存储路径只能增加，不能删除。新增加的存储路径不会备表空间立即使用，只有在已有存储路径文件系统空间满了，才会使用新增路径，增加路径只是为了解决容量问题。v9.7版本之后，只要对使用自动存储管理的表空间执行rebalance，则该表空间可以立即使用新增存储路径。

alter database testdb add storage on '/database/czm'
alter tablespace 7 rebalance

16、将DMS表空间转换成自动存储管理表空间
alter tablespace tbs_data managed by automatic storage
alter tablespace tbs_data rebalance