首先得開啟 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.

建立使用者臨時表空間：

DB2建立資料庫，建立表空間

相關文章