DB2数据库管理：启停、创建删除、权限、缓存池、表空间及日志操作

分类: 日博365.tv 📅 2025-07-18 19:51:06 👤 admin 👁️ 5195 ❤️ 510

1.1 数据库的启停

启动数据库： db2start

关闭数据库： db2stop（在关闭前先执行db2 force application all(关闭所有正在执行的连接)）或者直接执行db2stop force(不是优先选择))

# --有时候db2 force application all关闭不了一些active的连接时可以使用db2stop force

1.2 数据库的创建及删除

创建数据库： db2 "create database db_name using codeset UTF-8 territory CN"(指定数据库的字符集)

root权限新建用户 useradd user_name

修改用户密码: passwd user_name

输入密码:......

--给用户赋予数据库的权限:

---赋予用户连接数据库的权限:

db2 grant connect on database to user user_name

---赋予用户创建表的权限:

db2 grant createtab on database to user user_name

---赋予用户load的权限:

db2 grant load on database to user user_name

---赋予用户dbadm权限:

db2 grant dbadm on database to user user_name

创建schema: db2 create schema user_name

连接数据库:db2 connect to db_name or db2 connect to db_name user username using password

*用户可以拥有的数据库级特权有：

CREATETAB：用户可以在数据库中创建表。

BINDADD：用户可以使用 BIND 命令在数据库中创建包。

CONNECT：用户可以连接数据库。

CREATE_NOT_FENCED：用户可以创建 unfenced 用户定义函数（UDF）。

IMPLICIT_SCHEMA：用户可以在数据库中隐式地创建模式，而不需要使用 CREATE SCHEMA 命令。

LOAD：用户可以将数据装载进表中。

QUIESCE_CONNECT：用户可以访问处于静默（quiesced）状态的数据库。

CREATE_EXTERNAL_ROUTINE：用户可以创建供应用程序和数据库的其他用户使用的过程。

*数据库中的User和Schema到底什么关系呢？其实User就是每个Schema的主人，如果当前操作数据库的用户有默认的Schema（在创建用户的时候指定了），那么新表被创建在默认的Schema上。通过这样的方法，主要是方便管理。

在DB2中的schema的概念和ORACLE中的概念有着本质的区别：在ORACLE中schema和用户是同一个;在DB2中schema不一定是用户,因为db2内部没有用户的概念,连接用户必须是操作系统用户.

--------DB2数据库的删除----------------------

①：连接db2数据库

db2 connect to [dbname] user [username] using [password]

②：查看该数据库有多少应用

db2 list applications

③：杀掉这些应用

db2 force applications all

④：停止数据库

db2 deactivate database databasename

⑤：断开数据库

db2 terminate

⑥：删除db2数据库

db2 drop database databasename

注意：如果以上步骤执行成功，还报错SQL1035N，就执行以下操作：

-- 先强制停止数据库

db2stop force

-- 注意要连着写执行

db2start;db2 drop database databasename;

-----------db2删除schema------------

需要把schema下的所有对象都删除

DROP SCHEMA ERRORSCHEMA1 RESTRICT

--schema中不能含有任何对象

--RESTRICT 关键字强制实施以下规则：不能在指定的模式中为要从数据库中删除的模式定义对象。RESTRICT 关键字并非可选关键字

参考IBM官方文档:数据库 - IBM 文档

1.3 缓存池的创建修改和删除

--获取数据库中存在的缓冲池名称列表

SELECT BPNAME FROM SYSCAT.BUFFERPOOLS

--创建缓存池(重启数据库才生效)

CREATE BUFFERPOOL buffer-pool-name PAGESIZE 4096

--创建缓冲池(8K)：

create bufferpool ibmdefault8k IMMEDIATE SIZE 5000 PAGESIZE 8 K ;

--创建缓冲池(16K)(OA_DIVERTASKRECORD)：

create bufferpool ibmdefault16k IMMEDIATE SIZE 5000 PAGESIZE 16 K ;

--创建缓冲池(32K)(OA_TASK)：

create bufferpool ibmdefault32k IMMEDIATE SIZE 5000 PAGESIZE 32 K ;

**定义用于缓冲池的页的大小。不带后缀 K 的整数的有效值为 4096、8192、16384 或 32768。后缀为 K 的整数的有效值为 4、8、16 或 32。

--数据库分区上创建缓冲池(如果指定了此参数，那么仅在这些数据库分区组中的数据库分区上创建缓冲池。每个数据库分区组当前必须存在于数据库中。如果未指定 DATABASE PARTITION GROUP 子句，那么将在所有数据库分区上（以及后来添加至数据库的任何数据库分区上）创建此缓冲池。)

CREATE BUFFERPOOL buffer-pool-name PAGESIZE 4096

DATABASE PARTITION GROUP db-partition-group-name

--修改缓存池大小

ALTER BUFFERPOOL buffer_pool_name SIZE number of pages

*number of pages 是要分配给此特定缓冲池的新页数

--删除缓存池

DROP BUFFERPOOL buffer-pool-name

参考IBM官方文档：缓冲池 - IBM 文档

1.4.表空间的创建及删除

**DB2支持三种表空间:

--系统管理的空间（System-Managed Space，SMS）：

在这里，由操作系统的文件系统管理器分配和管理空间。在 DB2 9 之前，如果不带任何参数创建数据库或表空间，就会导致所有表空间作为 SMS 对象创建。

--数据库管理的空间（Database-Managed Space，DMS）：

在这里，由数据库管理程序控制存储空间。这种表空间本质上是一种特殊用途的文件系统实现，可以最好地满足数据库管理程序的需要。

--DMS的自动存储（Automatic Storage With DMS）：

自动存储实际上不是一种单独的表空间类型，而是一种处理 DMS 存储的不同方式。DMS 容器需要比较多的维护，在 DB2 V8.2.2 中引入了自动存储，作为简化空间管理的方式。

SMS 表空间需要的维护非常少。但是，与 DMS 表空间相比，SMS 表空间提供的优化选项少而且性能不好。

--默认表空间

当创建一个新的数据库，数据库管理器会创建一些默认表空间数据库。这些表被用作存储为用户和临时数据。这里给出每个数据库必须至少包含三个表空间：

目录表空间

用户表

临时表空间

--目录表空间：它包含系统目录表的数据库。它被命名为SYSCATSPACE并且它不能被丢弃。

--用户表空间：此表中包含用户定义的表。在数据库中，我们有一个默认的用户表，命名为USERSPACE1。如果创建的时候不为表指定用户定义的表空间，那么数据库管理器选择默认的用户表空间为您服务。

--临时表空间：临时表空间包含临时表中的数据。该表空间包含系统临时表空间或用户临时表空间。

系统临时表空间持有在执行操作的数据库管理器所需的临时数据，如排序或联接。一个数据库必须至少有一个系统临时表空间，它被命名为TEMPSPACE1。这是在创建该数据库的时间创建。用户临时表空间拥有表的临时数据。它与DECLARE GLOBAL TEMPORARY TABLE创建或CREATE GLOBAL TEMPORARY TABLE语句。该临时表空间不是默认在创建数据库时创建的。

----创建DMS表空间

CREATE TABLESPACE name MANAGED BY DATABASE USING (FILE 'path' size)

eg.

db2 "create regular tablespace tablespace_name pagesize 32k managed by database using(file 'file_path' 60g) bufferpool bufferpool_name"

--bufferpool bufferpool_name 为表空空间指定缓存池

----为用户赋予表空间的权限

db2 "grant use of tablespace tablespace_name to user user_name"

----修改表空间设置自增(生产环境尽量不要设置自增)

db2 alter tablespace tablespace_name autoresize yes

----表空间的删除

/**

如果不删除与某个表空间相关联的所有表空间，那么不能删除该表空间。例如，如果您在一个表空间中创建了表并在另一个表空间中创建了其索引，那么必须在一个 DROP TABLESPACE 语句中同时删除索引和数据表空间。

DROP TABLESPACE TABLESPACE_NAME

官方文档:表空间 - IBM 文档

1.5 字符集设置

--字符集设置

在DB2中，字符集主要为三个部分，分别是：操作系统、实例（客户端）、数据库等；

其中操作系统、实例（客户端）两个级别的可以根据需要进行自定义修改；

数据库级别的只能在创建数据库时指定。

--操作系统字符集

通过export LANG进行字符集的设置

export LANG=en_US.UTF-8 #utf8字符集

export LANG=zh_CN --gbk字符集

--客户端字符集

查看客户端字符集

db2set -all --查看db2codepage的值

[db2iadm@Linux ~]$ db2set -all

[i] DB2COMM=tcpip

[i] DB2CODEPAGE=1208

[g] DB2FCMCOMM=TCPIP6

[g] DB2SYSTEM=localhost.localdomain

[g] DB2INSTDEF=db2iadm

设置客户端字符集

db2set db2codepage=1208 --utf8字符集

db2set db2codepage=1386 --gbk字符集

--数据库字符集

示例：创建utf8字符集数据库

db2 "CREATE DATABASE databasename USING CODESET UTF-8 TERRITORY CN"

注意：数据库字符集创建后不能修改，务必注意

1.6 DB2 基本命令

--显示当前实例：db2 get instance

--列出当前数据库：db2 list db directory

--列出所有实例(db2inst1):db2ilist

--创建数据库： db2 create database db_name

--连接数据库： db2 connect to db_name or db2 connect to db_name user username using password

--断开数据库连接：db2 connect reset or db2 terminate

--查看回滚进度（条数）：db2pd -db hkcas(dbbase_name) -reco -alldbp

--查看当前主节点回滚情况 db2pb -db DBNAME -reco

--查看当前主机所有分区的回滚情况 db2pd -db DBNAME -reco -alldbp

--查看是否有活动的链接：db2 list applications for db db_name

如果此时，发现连接不了数据库，莫慌，需要激活目标数据库

首先查看是否有活跃的数据库：db2 list active databases

如果没有，需要对目标数据库进行激活设置：db2 activate database db_name

--db2退出当前数据库连接 : db2 connect reset

--db2执行脚本:db2 -vf tmp.sql

--db2获取日志路径:db2 get db cfg for database_name

/**

在DB2数据库中查看与日志有关的主要参数:

userexit 是否启用用户出口

logretain 是否启用归档日志

trackmod 是否启用增量备份功能

这些默认是off，即循环日志模式，如为"Yes"或"Recovery",则表明为归档日志模式．

Unix/Linux下的查看命令:

DB2 v9及以前版本:

db2 get db cfg for sample|grep "USEREXIT"

db2 get db cfg for sample|grep "LOGRETAIN"

db2 get db cfg for sample|grep "TRACKMOD"

DB2 v10:

db2 get db cfg for sample |grep "LOGARCHMETH1"

--列出所有表空间的详细信息:db2 list tablespaces show detail

--查看表空间下的文件：

db2 list tablespace containers for 表空间对应的数字编号

--查看所有表空间状态:

db2pd -tablespaces -db DB_NAME

--查看DMS表空间是否启用了自动存储：

db2 get snapshot for tablespaces on database_name

1.6.1 缩小db2表空间

示例 1：降低高水位标记并将所有容器减小 5 兆字节。以下示例降低表空间 ts 的高水位标记并将该表空间中所有容器的大小减小 5 兆字节。

ALTER TABLESPACE ts LOWER HIGH WATER MARK

ALTER TABLESPACE ts REDUCE (ALL CONTAINERS 5 M)

--注一般上面这个就够用了

示例 2：降低高水位标记并将容器“Container1”缩小 2000 页。以下示例降低表空间 ts 的高水位标记并将“Container1”的大小缩小 2000 页。

ALTER TABLESPACE ts LOWER HIGH WATER MARK

ALTER TABLESPACE ts REDUCE (FILE "Container1" 2000)

1.6.2 db2查看索引是否有效

① 可以使用db2pd来看，比如

db2pd -db sample -tcbstats index

② 使用sql语句查看syscat.indexes，对比下创建时间和使用时间

SELECT substr(INDSCHEMA, 1, 12) idxschema

,substr(INDNAME, 1, 12) idxname

,substr(TABNAME, 1, 15) table_name

,to_char(create_time, 'yyyy-mm-dd') created

,to_char(LASTUSED, 'yyyy-mm-dd') lastused

FROM syscat.indexes

WHERE INDSCHEMA = 'schema_name'

备注：

1. 在字段用了函数或者表达式，是不走索引的

2. 定期维护，做runstats和reorg

下面这条SQL可以查看所有表模式名 "E105Q5A"下所有表上所有需要重建的索引

db2 "SELECT substr(TABNAME,1,20) as TABNAME, substr(INDSCHEMA,1,20) as INDSCHEMA, substr(INDNAME,1,20) as INDNAME, INDEX_REQUIRES_REBUILD FROM TABLE(sysproc.admin_get_index_info('','E105Q5A','')) AS t where INDEX_REQUIRES_REBUILD= 'Y' "

TABNAME INDSCHEMA INDNAME INDEX_REQUIRES_REBUILD

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

T1 E105Q5A IDX1 Y

T1 E105Q5A IDX2 Y

2 record(s) selected.

1.6.3 DB2查询表空间大小及使用情况

--SQL 查询表空间大小及使用情况（选择Schema为SYSPROC）

SELECT SUBSTR(TABLESPACE_NAME, 1, 20) AS TBSPC_NAME

,BIGINT (TOTAL_PAGES * PAGE_SIZE) / 1024 / 1024 AS "TOTAL(MB)"

,used_pages * PAGE_SIZE / 1024 / 1024 AS "USED(MB)"

,free_pages * PAGE_SIZE / 1024 / 1024 AS "FREE(MB)"

FROM TABLE (snapshot_tbs_cfg('FJDC2', - 2)) AS snapshot_tbs_cfg

1.6.4 DB2查看表状态

查看表状态:

SELECT tabname

,colcount

,STATUS

FROM syscat.tables

WHERE tabschema NOT LIKE 'SYS%'

ORDER BY tabname

status=n-正常 c-暂时挂起(set integrity pending) x-不起作用(inoperative)

--解决表挂起

1）可以解除每一个表的check pendding状态

db2 set constraints for tabname

2）可以使表解除挂起状态

db2 reorg table tabname

1.6.4 DB2 查询执行慢的sql

--查询执行慢的sql:

select * from sysibmadm.top_dynamic_sql order by average_execution_time_s desc

--kill掉lockwait

db2 "force application(47914)"

1.7 DB2 look

DB2的db2look命令诠释如下：

db2look 版本 8.2

db2look：生成 DDL 以便重新创建在数据库中定义的对象

语法:

--db2look -d DBname [-e] [-u Creator] [-z Schema] [-t Tname1 Tname2...TnameN] [-tw Tname] [-h] [-o Fname] [-a][-m] [-c] [-r] [-l] [-x] [-xd] [-f] [-fd] [-td x] [-noview] [-i userID] [-w password][-v Vname1 Vname2 ... VnameN][-wrapper WrapperName] [-server ServerName] [-nofed]

--db2look -d DBname [-u Creator] [-s] [-g] [-a] [-t Tname1 Tname2...TnameN][-p] [-o Fname] [-i userID] [-w password]

--db2look [-h]

-d: 数据库名称：这必须指定

-e: 抽取复制数据库所需要的 DDL 文件

此选项将生成包含 DDL 语句的脚本

可以对另一个数据库运行此脚本以便重新创建数据库对象

此选项可以和 -m 选项一起使用

-u: 创建程序标识：若 -u 和 -a 都未指定，则将使用 $USER

如果指定了 -a 选项，则将忽略 -u 选项

-z: 模式名：如果同时指定了 -z 和 -a，则将忽略 -z

联合部分的模式名被忽略

-t: 生成指定表的统计信息

可以指定的表的数目最多为 30

-tw: 为名称与表名的模式条件（通配符）相匹配的表生成 DDL

当指定了 -tw 选项时，-t 选项会被忽略

-v: 只为视图生成 DDL，当指定了 -t 时将忽略此选项

-h: 更详细的帮助消息

-o: 将输出重定向到给定的文件名

如果未指定 -o 选项，则输出将转到 stdout

-a: 为所有创建程序生成统计信息

如果指定了此选项，则将忽略 -u 选项

-m: 在模拟方式下运行 db2look 实用程序

此选项将生成包含 SQL UPDATE 语句的脚本

这些 SQL UPDATE 语句捕获所有统计信息

可以对另一个数据库运行此脚本以便复制初始的那一个

当指定了 -m 选项时，将忽略 -p、-g 和 -s 选项

-c: 不要生成模拟的 COMMIT 语句

除非指定了 -m 或 -e，否则将忽略此选项

将不生成 CONNECT 和 CONNECT RESET 语句

省略了 COMMIT。在执行脚本之后，需要显式地进行落实。

-r: 不要生成模拟的 RUNSTATS 语句

缺省值为 RUNSTATS。仅当指定了 -m 时，此选项才有效

-l: 生成数据库布局：数据库分区组、缓冲池和表空间。

-x: 如果指定了此选项，则 db2look 实用程序将生成授权 DDL

对于现有已授权特权，不包括对象的原始定义器

-xd: 如果指定了此选项，则 db2look 实用程序将生成授权 DDL

对于现有已授权特权，包括对象的原始定义器

-f: 抽取配置参数和环境变量

如果指定此选项，将忽略 -wrapper 和 -server 选项

-fd: 为 opt_buffpage 和 opt_sortheap 以及其它配置和环境参数生成 db2fopt 语句。

-td: 将 x 指定为语句定界符（缺省定界符为分号（;））

应该与 -e 选项一起使用（如果触发器或者 SQL 例程存在的话）

-p: 使用明文格式

-s: 生成 postscript 文件

此选项将为您生成 postscript 文件

当设置了此选项时，将除去所有 latex 和 tmp ps 文件

所需的（非 IBM）软件：LaTeX 和 dvips

注意：文件 psfig.tex 必须在 LaTeX 输入路径中

-g: 使用图形来显示索引的页访存对

必须安装 Gnuplot，并且必须在您的 LaTeX 输入路径中

还将随 LaTeX 文件一起生成文件

-i: 登录到数据库驻留的服务器时所使用的用户标识

-w: 登录到数据库驻留的服务器时所使用的密码

-noview: 不要生成 CREATE VIEW ddl 语句

-wrapper: 为适用于此包装器的联合对象生成 DDL

生成的对象可能包含下列各项：

包装器、服务器、用户映射、昵称、类型映射、

函数模板、函数映射和索引规范

-server: 为适用于此服务器的联合对象生成 DDL

生成的对象可能包含下列各项：

包装器、服务器、用户映射、昵称、类型映射、

函数模板、函数映射和索引规范

-nofed: 不要生成 Federated DDL

如果指定此选项，将忽略 -wrapper 和 -server 选项

LaTeX 排版：latex filename.tex 以获得 filename.dvi

示例: db2look -d DEPARTMENT -u walid -e -o db2look.sql

-- 这将生成由用户 WALID 创建的所有表和联合对象的 DDL 语句