常用的DB2管理命令

最新推荐文章于 2025-04-22 14:33:49 发布

cl1210930061

最新推荐文章于 2025-04-22 14:33:49 发布

阅读量222

点赞数

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"

-----------

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26217983/viewspace-1140146/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26217983/viewspace-1140146/