DB2数据库常用命令

MapLover2020

已于 2025-01-25 16:42:53 修改

阅读量1.9k

点赞数 20

文章标签：数据库 oracle

于 2025-01-18 15:32:23 首次发布

DB2 数据库常用命令

一.数据库管理

1.1启停数据库：

启动db2服务：db2start
激活数据库实例：db2 activate database <db_name>
查看激活状态的数据库：db2 list active databases

关闭数据库：
失效数据库实例：db2 deactivate database <db_name>
关闭数据库服务：db2stop

1.2 创建数据库：

语法：db2 create database <数据库名> [其他选项]
示例：
db2 create database sampledb
db2 create database mydb codeset utf-8 territory cn

此命令用于创建一个名为sampleDB的数据库，使用默认配置。
若要指定数据库使用的代码页、国家 / 地区代码等，可以添加相应选项，如创建一个使用 UTF-8 编码且地区为中国的数据库

1.3.连接数据库：

语法：connect to <数据库名> [user <用户名> using <密码>]
示例：connect to sampledb user admin using password123

使用admin用户和密码password123连接到名为sampleDB的数据库。
若当前用户已具有数据库访问权限且不需要指定用户名和密码，可以简化为

connect to sampledb

1.4.查看数据库：

查看数据库：db2 list db directory

[db2inst1@rocky8 data]$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 3

Database 1 entry:

 Database alias                       = RPTDB
 Database name                        = RPTDB
 Local database directory             = /data/rptdb
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

查看数据库应用：db2 list applications

[db2inst1@rocky8 data]$ db2 list active databases

                           Active Databases

Database name                              = RPTDB
Applications connected currently           = 4
Database path                              = /data/rptdb/db2inst1/NODE0000/SQL00001/MEMBER0000/

[db2inst1@rocky8 data]$ 
[db2inst1@rocky8 data]$ 
[db2inst1@rocky8 data]$ db2 list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 DBeaver        29         ::ffff:192.168.0.10.61515.250125075818                         RPTDB    1    
DB2INST1 DBeaver        28         ::ffff:192.168.0.10.61514.250125075817                         RPTDB    1    
DB2INST1 dbvis          8          ::ffff:192.168.0.10.61469.250125075623                         RPTDB    1    
DB2INST1 db2bp          73         *LOCAL.db2inst1.250125083528                                   RPTDB    1    

[db2inst1@rocky8 data]$

查看数据库应用和进程号：db2 list applications show detail
查看数据库表空间：db2pd -db <db_name> -tablespace

[db2inst1@rocky8 data]$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.8
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

[db2inst1@rocky8 data]$ 
[db2inst1@rocky8 data]$ db2pd -db sample -tablespace

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:00:06 -- Date 2025-01-25-16.41.38.328030

Tablespace Configuration:
Address            Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg RSE  Name
0x00007F2CE3920D40 0     DMS  Regular 8192   4        Yes  4        1     1         Def 1        0          3            Yes  SYSCATSPACE
0x00007F2CE392DEE0 1     SMS  SysTmp  8192   32       Yes  32       1     1         On  1        0          31           No   TEMPSPACE1
0x00007F2CE393B080 2     DMS  Large   8192   32       Yes  32       1     1         Def 1        0          31           Yes  USERSPACE1
0x00007F2CE3948220 3     DMS  Large   8192   32       Yes  32       1     1         Def 1        0          31           Yes  IBMDB2SAMPLEREL
0x00007F2CE39553C0 4     DMS  Large   8192   32       Yes  32       1     1         Def 1        0          31           Yes  IBMDB2SAMPLEXML
0x00007F2CE39710C0 5     DMS  Large   8192   4        Yes  4        1     1         Def 1        0          3            Yes  SYSTOOLSPACE

Tablespace Statistics:
Address            Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        Max HWM    State      MinRecTime NQuiescers PathsDropped TrackmodState    
0x00007F2CE3920D40 0     16384      16380      16100      0          280        16100      16100      0x00000000 0          0          No           n/a              
0x00007F2CE392DEE0 1     1          1          1          0          0          -          -          0x00000000 0          0          No           n/a              
0x00007F2CE393B080 2     4096       4064       1824       0          2240       1824       1824       0x00000000 0          0          No           n/a              
0x00007F2CE3948220 3     4096       4064       608        0          3456       608        608        0x00000000 0          0          No           n/a              
0x00007F2CE39553C0 4     4096       4064       1440       0          2624       1440       1440       0x00000000 0          0          No           n/a              
0x00007F2CE39710C0 5     4096       4092       108        0