1.常用命令

1.1.列出所有实例

[db2inst1@localhost ~]$ db2ilist
db2inst1

1.2.列出当前实例

[db2inst1@localhost ~]$ db2 get instance

The current database manager instance is: db2inst1

1.3.查看数据库信息（SCV testTEST testDB 等）

[db2inst1@localhost ~]$ db2 list db directory

System Database Directory

Number of entries in the directory = 5

Database 1 entry:

Database alias = SCV
Database name = SCV
Local database directory = /home/db2inst1
Database release level = 14.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

Database 2 entry:

Database alias = testTEST
Database name = testTEST
Local database directory = /home/db2inst1
Database release level = 14.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

1.4.查看数据库应用信息

[db2inst1@localhost ~]$ db2 list applications

Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-
test db2jcc_applica 131 ::ffff:172.19.6.49.33129.210603102321 testTEST 1
test db2jcc_applica 51 ::ffff:172.19.6.49.33128.210603095759 testTEST 1
test db2jcc_applica 257 ::ffff:172.19.6.49.33133.210603120320 testTEST 1
test db2jcc_applica 132 ::ffff:172.19.6.49.33130.210603102320 testTEST 1

1.5.查看数据库应用与进程号

[db2inst1@localhost ~]$ db2 list applications show detail

CONNECT Auth Id Application Name Appl. Application Id Seq# Number of Coordinating Coordinator Status Status Change Time DB Name DB Path
Handle Agents member number pid/thread
- -
test db2fw2 59 *LOCAL.DB2.210603095808 00001 1 0 285 Connect Completed Not Collected testTEST /home/db2inst1/db2inst1/NODE0000/SQL00005/MEMBER0000/

1.6.查看数据库配置信息

[db2inst1@localhost ~]$ db2 get db cfg for testtest

   Database Configuration for Database testtest

Database configuration release level = 0x1400
Database release level = 0x1400

1.7.查看归档日志路径

[db2inst1@localhost ~]$ db2 get db cfg for testtest|grep -i “LOGARCHMETH1”
First log archive method (LOGARCHMETH1) = OFF
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =

1.8.查看首活动日志

[db2inst1@localhost ~]$ db2 get db cfg for testtest|grep -i “First active log file”
First active log file

1.9.连接到数据库1

[db2inst1@localhost ~]$ db2 connect to testtest

1.10.连接到数据库2

[db2inst1@localhost ~]$ db2 connect to testtest user test
Enter current password for test:

Database Connection Information

Database server = DB2/LINUXX8664 11.1.3.3
SQL authorization ID = test
Local database alias = testTEST

1.11.连接到数据库3

[db2inst1@localhost ~]$ db2 connect to testtest user test using Shfwpt@123

Database Connection Information

Database server = DB2/LINUXX8664 11.1.3.3
SQL authorization ID = test
Local database alias = testTEST

1.12.断开数据库连接

[db2inst1@localhost ~]$ db2 connect reset
DB20000I The SQL command completed successfully.
[db2inst1@localhost ~]$

1.13.断开数据库连接2

[db2inst1@localhost ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@localhost ~]$

1.14.列出所有表数据

[db2inst1@localhost ~]$ db2 list tables

Table/View Schema Type Creation time

---

FEE_TYPE test T 2021-05-27-16.43.40.274489
FEE_TYPE_MER test T 2021-05-27-16.43.41.208940
GROUP_MER_INFO test T 2021-05-27-16.43.41.757482
PT_APP_ENU_DETAIL test T 2021-05-27-16.43.42.317547

1.15.列出系统表

[db2inst1@localhost ~]$ db2 list tables for system

Table/View Schema Type Creation time

---

ATTRIBUTES SYSCAT V 2021-05-27-16.31.20.513169
AUDITPOLICIES SYSCAT V 2021-05-27-16.31.20.596772

1.16.列出用户表

[db2inst1@localhost ~]$ db2 list tables for user

Table/View Schema Type Creation time

---

FEE_TYPE test T 2021-05-27-16.43.40.274489
FEE_TYPE_MER test T 2021-05-27-16.43.41.208940

1.17.列出test用户下的表

[db2inst1@localhost ~]$ db2 list tables for schema test

Table/View Schema Type Creation time

---

FEE_TYPE test T 2021-05-27-16.43.40.274489

1.18.查看表结构

[db2inst1@localhost ~]$ db2 describe table test.wx_public_infos

1.19.复制表

[db2inst1@localhost ~]$ db2 create table test.test2 like test.wx_public_infos
DB20000I The SQL command completed successfully.

1.20.查看正在执行的sql

[db2inst1@localhost ~]$ db2 list application show detail |grep -i exe

1.21.查看表空间

[db2inst1@localhost ~]$ db2pd -db bpstest -tablespace

1.22.查看死锁1

[db2inst1@localhost ~]$ db2 get snapshot for locks on testtest
[db2inst1@localhost ~]$ db2 get snapshot for locks on testtest|grep Locks
Locks held = 0
Locks held = 0
Locks held = 0
Locks held = 0

        Database Lock Snapshot

Database name = testTEST
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00005/MEMBER0000/
Input database alias = testTEST
Locks held = 0
Applications currently connected = 5
Agents currently waiting on locks = 0
Snapshot timestamp = 2021-06-04 09:34:20.657933

1.23.查看消耗资源最多的sql

[db2inst1@localhost ~]$ db2top -d testtest >l

Application Handle(Stat) 最大的消耗资源最多
1250(i) 0.00% 0.00% 3.59% UOW Waiting in the application db2bp 0 0 0 0 0 448.0K 1 0 0 0 0

1.24. 导出指定表结构

db2look -d db2Name -e -t tableName-o ddl.sql

1.25. 导出多张指定表数据

db2move db2Name export -tn tableName1,tableName2-sn schemaName(多张表以，间隔分开)
（导出的DB2数据会以ixf以及msg文件保存下来）

1.26.导出整个tableSpace表结构和数据

db2look -d db2Name -z tableSpaceName -e -o tableSpaceDDL.sql
（导出的数据会以ixf以及msg文件保存下来，DDL以.sql文件保存）

1.27.导入表结构

db2 -tvf ddl.sql -Z ddl.log
(-Z是对导入表结构输出log文件)

1.29.单表导出导入

导出
export to tabname.ixf of ixf messages msg.out select * from tabname;

1.30.查看表结构（显示DDL语句，包括列、索引、主键等）

db2look -d testdb -e -t tablename
-d 后面的参数 数据库名称
-e 生成DDl
-t 表名