linux db2连接好后如何jianbiao,[DB2]表空间之SMS篇-CSDN博客

创建SMS类型表空间：

db2 => create tablespace TBS_SMS \

db2 (cont.) => managed by system \

db2 (cont.) => using('demo')

DB20000IThe SQL command completed successfully.

需要注意：SMS类型即系统管理表空间，指定容器时，只能指定目录名(本例中demo就是目录名)，就是我们特别指定的目录名。以后在该表空间上建表、索引等对象时，所产生的数据文件，均存放在我们指定的目录下。

1、查看表空间容器

下面我们查看下，这个目录存放的全路径。

首先查看，表空间的id号：

db2 => list tablespaces

Tablespaces for Current Database

Tablespace ID= 0

Name= SYSCATSPACE

Type= Database managed space

Contents= All permanent data. Regular table space.

State= 0x0000

Detailed explanation:

Normal

。。。。。。。。。。。。。。。。。

Tablespace ID= 5

Name= TBS_SMS

Type= System managed space

Contents= All permanent data. Regular table space.

State= 0x0000

Detailed explanation:

Normal

查看到我们新建的表空间的id号是5之后，我们查看容器即可找到，demo的全路径。可以看到是在/NODE0000/SQL00003目录下新建的文件夹。SQL00003是我们创建数据库demo时，自动创建的，像我们“定制”的一些数据，默认就会创建在该目录下。

db2 => list tablespace containers for 5 show detail

Tablespace Containers for Tablespace 5

Container ID= 0

Name= /home/db2inst1/db2inst1/NODE0000/SQL00003/demo

Type= Path

Total pages= 1

Useable pages= 1

Accessible= Yes

2、有关容器的一个问题

问：上课时，老师演示了如下代码，出现了错误，该如何解释？是不是容器不允许指定全路径？

create tablespace TBS_SMS1 \

managed by system \

using('/home/db2inst1/db2inst1/NODE0000/DEMO')

DB21034EThe command was processed as an SQL statement because it was not a

valid Command Line Processor command.During SQL processing it returned:

SQL0298NBad container path.SQLSTATE=428B2

我们去查看下错误的原因，其中有如下的提示(其余内容省略)：

db2 => ? SQL0298N

。。。。。。。。。。。。。

*Containers (directories) in system managed table spaces must be empty

when designated as containers and must not be nested underneath other

containers.

。。。。。。。。。。。。。

我们再去查看下/home/db2inst1/db2inst1/NODE0000/DEMO目录，发现目录非空。

[db2inst1@localhost DEMO]$ pwd

/home/db2inst1/db2inst1/NODE0000/DEMO

[db2inst1@localhost DEMO]$ ls

T0000000T0000001T0000002T0000003T0000004

原来是指定的目录非空，导致的错误。我们将创建空间的语句修改即可。创建完成后，数据库会自动得生成TBS这个目录(不需要我们实现手动创建此目录)。

db2 => create tablespace TBS_SMS1 \

db2 (cont.) => managed by system \

db2 (cont.) => using('/home/db2inst1/db2inst1/NODE0000/DEMO/TBS')

DB20000IThe SQL command completed successfully.

3、在表空间上创建表和索引

首先查看下容器目录中的文件情况，发现仅有一个文件SQLTAG.NAM

[db2inst1@localhost demo]$ pwd

/home/db2inst1/db2inst1/NODE0000/SQL00003/demo

[db2inst1@localhost demo]$ ls

SQLTAG.NAM

接下来我们使用如下脚本，在TBS_SMS上面创建一个emp表，再去查看下文件情况，发现此时多了一个dat文件：

create table emp \

( \

Empno DECIMAL(4), \

Ename VARCHAR(7), \

JobVARCHAR(8), \

Mamanger_id DECIMAL(4), \

Hiredate DATE, \

Salary DECIMAL(4), \

Comm DECIMAL(4), \

Department_id DECIMAL(2) \

) \

in TBS_SMS

[db2inst1@localhost demo]$ ls

SQL00002.DATSQLTAG.NAM

我们还可以去查询表空间的情况，发现由于新建了一个文件，该表空间相应得多分配了一个页的空间。

list tablespaces show detail

Tablespace ID= 5

Name= TBS_SMS

Type= System managed space

Contents= All permanent data. Regular table space.

State= 0x0000

Detailed explanation:

Normal

Total pages= 2

Useable pages= 2

Used pages= 2

Free pages= Not applicable

High water mark (pages)= Not applicable

Page size (bytes)= 4096

Extent size (pages)= 32

Prefetch size (pages) = 32

Number of containers= 1

接下来，我们接着再创建一个索引，看看会出现什么样的情况：

db2 => create index index_emp_empno on emp(empno)

DB20000IThe SQL command completed successfully.

[db2inst1@localhost demo]$ ls

SQL00002.DATSQL00002.INXSQLTAG.NAM

再执行一下list tablespaces show detail，发现新生成了SQL00002.INX文件的同时，此时分配了3个页的空间。

Tablespace ID= 5

Name= TBS_SMS

Type= System managed space

Contents= All permanent data. Regular table space.

State= 0x0000

Detailed explanation:

Normal

Total pages= 5

Useable pages= 5

Used pages= 5

Free pages= Not applicable

High water mark (pages)= Not applicable

Page size (bytes)= 4096

Extent size (pages)= 32

Prefetch size (pages) = 32

Number of containers= 1

一个页是4k，我们查看下inx文件是否是12k：

[db2inst1@localhost demo]$ ls -lh

total 20K

-rw------- 1 db2inst1 db2iadm1 4.0K Jul 22 13:42 SQL00002.DAT

-rw------- 1 db2inst1 db2iadm112K Jul 22 13:57 SQL00002.INX

-rw------- 1 db2inst1 db2iadm1512 Jul 22 11:11 SQLTAG.NAM

问：我们再创建一个表，是放在之前创建好的dat文件上，还是新建一个文件呢？

db2 => create table emp_new(empno int)

DB20000IThe SQL command completed successfully.

[db2inst1@localhost demo]$ ls

SQL00002.DATSQL00002.INXSQL00003.DATSQLTAG.NAM

答案是新建一个文件。

可以发现，DB2的SMS管理表空间的模式，和MySQL的MyISAM存储引擎的文件比较类似，也是分不同文件来存放数据、索引的。

问：能否给系统管理的表空间增加容器？

db2 => alter tablespace TBS_SMS add('demo1')

DB21034EThe command was processed as an SQL statement because it was not a

valid Command Line Processor command.During SQL processing it returned:

SQL0281NTable space "TBS_SMS" cannot be altered with additional containers

because it is a system managed table space.SQLSTATE=42921

db2 => ? SQL0281N

SQL0281NTable space "" cannot be altered with

additional containers because it is a system managed table space.

Explanation:

Additional containers cannot be added to a system managed table space.

The exception to this is when a database partition group was modified to

add a node without table spaces, then containers can be added once on

the new node using the ALTER TABLESPACE command. In general, the table

space must be managed by the database in order to add additional

containers.

The statement cannot be processed.

User response:

To add more containers to a system managed table space, drop and

recreate the table space with more containers, ensuring that each

container is of the same size and less than the container size limit, or

change to a DMS table space.

因此，不能直接给表空间新增容器，只能是将原有的表空间删除，再新建的时候多指定容器。

实际上，多添加容器意义也不大，因为SMS可支持自动扩容。