常用的DB2管理命令

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.