DB2管理命令用於對DB2資料庫進行管理，下面就為您詳細介紹常用的DB2管理命令，如果您對此方面感興趣的話，不妨一看。

下面介紹的常用DB2管理命令，這些命令可以透過開啟“命令列處理器”輸入執行，也可以透過”db2控制中心“裡的“命令編輯器”圖形介面管理器輸入執行。

db2的管理命令包括sql命令，與db2系統命令不同。

DB2管理命令

例項級：

1)啟動資料庫管理器例項

db2start

2)停止資料庫管理器例項

db2stop

3)獲取資料庫管理器配置設定

get dbm cfg

4)顯示資料庫管理器引數的當前值和延遲值

get dbm cfg show detail

5)返回 DB2INSTANCE 環境變數的值

get instance

6) 斷開所有應用程式與資料庫的連線

force application all

7) 以使用者透過使用密碼與標識為的遠端例項連線

attach to user using

資料庫級：

8)顯式地以使用者和密碼與資料庫連線

connect to [ [user ] using ]

9)顯式地啟用資料庫

activate database

10)顯式地使資料庫失效

deactivate database

11)斷開與當前資料庫的連線

connect reset

12) 顯示資料庫配置引數的當前值和延遲值

get db cfg show detail

13)顯示資料庫的資料庫配置設定

get db cfg for

14)將資料庫的資料庫配置引數

更新為值

update db cfg for using

15)列出資料庫中的表

list tables[for {user | all | system | schema }][show detail]

如果沒有指定任何引數，則預設情況是列出當前使用者的表。

16)顯示一個表或檢視的列資訊

describe table

17)顯示錶空間的標識、名稱、型別、內容和狀態

list tablespaces [show detail]

18)顯示用指定的表空間的容器資訊

list tablespace containers for [show detail]

檢視資料庫伺服器中有幾個資料庫。包括網路中資料庫的引用

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 list database directory

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias = DB2

Database name = DB2

Local database directory = /home/db2inst1

Database release level = d.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

Database 2 entry:

Database alias = TEST1

Database name = TEST1

Local database directory = /home/db2inst1

Database release level = d.00

Comment =

Directory entry type = Indirect

Catalog database partition number = 0

Alternate server hostname =

Alternate server port number =

檢視執行的資料庫伺服器中關聯了多少個引用程式對資料庫的訪問

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 list applications

Auth Id Application Appl. Application Id DB # of

Name Handle Name Agents

-------- -------------- ---------- -------------------------------------------------------------- -------- -----

DB2INST1 db2bp 53 *LOCAL.db2inst1.101207141154 DB2 1

停止資料庫的伺服器

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 terminate

DB20000I The TERMINATE command completed successfully.

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> cd ~/sqllib/adm

db2inst1@linux-o154:~/sqllib/adm> ./db2stop

SQL1064N DB2STOP processing was successful.

建立資料庫

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 create db db3

DB20000I The CREATE DATABASE command completed successfully.

連線資料庫

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 connect to db3

Database Connection Information

Database server = DB2/LINUX 9.7.5

SQL authorization ID = DB2INST1

Local database alias = DB3

建表

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 "create table a(id int)"

DB20000I The SQL command completed successfully.

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 "insert into a values(1)"

DB20000I The SQL command completed successfully.

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 "select * from a"

ID

-----------

1

1 record(s) selected.

檢視資料庫中有多少表或檢視

db2inst1@linux-o154:/opt/ibm/db2/V9.7/bin> ./db2 list tables;

Table/View Schema Type Creation time

------------------------------- --------------- ----- --------------------------

A DB2INST1 T 2010-12-07-11.39.33.043901

1 record(s) selected.

檢視錶或檢視的結構

> ./db2 describe table a;

Data type Column

Column name schema Data type name Length Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

ID SYSIBM INTEGER 4 0 Yes

1 record(s) selected.

查詢表空間

> ./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 = 1

Name = TEMPSPACE1

Type = System managed space

Contents = System Temporary data

State = 0x0000

Detailed explanation:

Normal

Tablespace ID = 2

Name = USERSPACE1

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

Detailed explanation:

Normal

Tablespace ID = 3

Name = SYSTOOLSPACE

Type = Database managed space

Contents = All permanent data. Large table space.

State = 0x0000

Detailed explanation:

Normal

刪除資料庫

> ./db2 drop db test1

DB20000I The DROP DATABASE command completed successfully.

常用的DB2管理命令

相關文章