首先得开启 db manager

[db2inst1@localhost ~]$ db2 start db manager

DB20000I The START DATABASE MANAGER command completed successfully.

否则会有如下提示：

[db2inst1@localhost ~]$ db2 create database mydb

SQL1032N No start database manager command was issued. SQLSTATE=57019

开始创建数据库

[db2inst1@localhost ~]$ db2 create database mydb on '/xxx'

DB20000I The CREATE DATABASE command completed successfully.

注意以上命令没有指定数据库路径

可以用 [db2inst1@localhost V10.1]$ db2 get dbm cfg 查看数据库默认创建路径

我这边查到的是： Default database path (DFTDBPATH) = /home/db2inst1

把数据库创建在指定路径：

[root@localhost db2]# chown -R db2inst1:db2inst1 /db2/shijiaweidb

[root@localhost db2]# su - db2inst1

[db2inst1@localhost ~]$ db2 create database mydb1 on /db2/shijiaweidb

DB20000I The CREATE DATABASE command completed successfully.

数据库创建完成以后出现下面的文件：

[db2inst1@localhost shijiaweidb]$ du -h

129M ./db2inst1/NODE0000/MYDB1/T0000000

8.0K ./db2inst1/NODE0000/MYDB1/T0000001/C0000000.TMP

12K ./db2inst1/NODE0000/MYDB1/T0000001

33M ./db2inst1/NODE0000/MYDB1/T0000002

161M ./db2inst1/NODE0000/MYDB1

16K ./db2inst1/NODE0000/sqldbdir

4.0K ./db2inst1/NODE0000/SQL00001/MEMBER0000/HADR/TAKEOVER

4.0K ./db2inst1/NODE0000/SQL00001/MEMBER0000/HADR/LOGSPOOL

12K ./db2inst1/NODE0000/SQL00001/MEMBER0000/HADR

12K ./db2inst1/NODE0000/SQL00001/MEMBER0000/db2event/db2detaildeadlock

16K ./db2inst1/NODE0000/SQL00001/MEMBER0000/db2event

116K ./db2inst1/NODE0000/SQL00001/MEMBER0000

4.0K ./db2inst1/NODE0000/SQL00001/HADR/TAKEOVER

4.0K ./db2inst1/NODE0000/SQL00001/HADR/LOGSPOOL

12K ./db2inst1/NODE0000/SQL00001/HADR

12M ./db2inst1/NODE0000/SQL00001/LOGSTREAM0000

14M ./db2inst1/NODE0000/SQL00001

174M ./db2inst1/NODE0000

174M ./db2inst1

174M .

选择自动存储（默认设置）将允许DBA为数据库设置在创建所有表空间容器时可以使用的存储路径。BDA不必定义表空见的大小和位置，系统将自动的分配表空间。例如，下面的数据库创建语句将为数据库中的所有表空间设置自动存储。

[db2inst1@localhost db2]$ db2 create database mydb2 automatic storage yes on /db2/mydbpath001,/db2/mydbpath002,/db2/mydbpath003

DB20000I The CREATE DATABASE command completed successfully.

在ON选项后面，给出了3个文件目录（路径）。这3个路径是表空间容器的位置，数据库路径默认放在第一个路径下。当使用AUTOMATIC STORAGE定义表空间时，不需要提供其它参数：

若要显示所有已创建的数据库，执行：

[db2inst1@localhost ~]$ db2 list db directory

System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias = MYDB

Database name = MYDB

Local database directory = /home/db2inst1

Database release level = f.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

若要删除一个数据库可以用下面的命令

db2 drop database

选择Automatic storage（默认设置）允许DBA为数据库设置在创建所有表空间时可以使用

可以使用的存储路径。

创建表空间的时候出错，可以看到是没有连接到数据库

[db2inst1@localhost bin]$ db2 create tablespace test managed by automatic storage;

DB21034E The command was processed as an SQL statement because it was not a

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

SQL1024N A database connection does not exist. SQLSTATE=08003

用如下命令连接到 MYDB 数据库

[db2inst1@localhost bin]$ db2 connect to MYDB

Database Connection Information

Database server = DB2/LINUXX8664 10.1.0

SQL authorization ID = DB2INST1

Local database alias = MYDB

成功创建表空间

[db2inst1@localhost bin]$ db2 create tablespace test managed by automatic storage;

DB20000I The SQL command completed successfully.

d b2 force application all -- 强迫所有应用断开数据库连接

[db2inst1@localhost bin]$ db2 force application all

DB20000I The FORCE APPLICATION command completed successfully.

DB21024I This command is asynchronous and may not be effective immediately.

算开与数据库MYDB的连接

[db2inst1@localhost bin]$ db2 disconnect MYDB

DB20000I The SQL DISCONNECT command completed successfully.

假如要创建一个同时支持XML和SQL的数据库，执行下面的命令：

[db2inst1@localhost bin]$ db2 create database xmldb using codeset UTF-8 territory us

DB20000I The CREATE DATABASE command completed successfully.

[db2inst1@localhost bin]$ db2 connect to xmldb

Database Connection Information

Database server = DB2/LINUXX8664 10.1.0

SQL authorization ID = DB2INST1

Local database alias = XMLDB

[db2inst1@localhost bin]$ db2 connect to MYDB

Database Connection Information

Database server = DB2/LINUXX8664 10.1.0

SQL authorization ID = DB2INST1

Local database alias = MYDB

创建用户表空间

[db2inst1@localhost ~]$ db2 "create tablespace shijiawei managed by system using ('/db2/shijiawei')"

DB20000I The SQL command completed successfully.

创建DMS自动存储表空间

要使用命令行自动创建存储表空间，输入下面任意语句：

[db2inst1@localhost ~]$ db2 create tablespace test1

DB20000I The SQL command completed successfully.

[db2inst1@localhost ~]$ db2 create tablespace test2 managed by automatic storage

DB20000I The SQL command completed successfully.

创建系统临时表空间

[db2inst1@localhost ~]$ db2 "create system temporary tablespace tmp_tbsp managed by system using ('/data1/tmp_tbsp','/data2/tmp_tbsp')"

DB20000I The SQL command completed successfully.

创建用户临时表空间：

ITPub博客

DB2创建数据库，建立表空间

最新文章