DB2操作手册

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
1
2
3
4
5
6
设置客户端字符集
​
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",则表明为归档日志模式．
这些默认是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，并且 <psfig.tex> 必须在您的 LaTeX 输入路径中
            还将随 LaTeX 文件一起生成 <filename.ps> 文件
        -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 语句
 -- db2look 输出被发送到名为 db2look.sql 的文件中
​
示例: db2look -d DEPARTMENT -z myscm1 -e -o db2look.sql
​
 -- 这将为模式名为 MYSCM1 的所有表生成 DDL 语句
 -- 还将生成 $USER 创建的所有联合对象的 DDL。
 -- db2look 输出被发送到名为 db2look.sql 的文件中
​
示例: db2look -d DEPARTMENT -u walid -m -o db2look.sql
​
 -- 这将生成 UPDATE 语句以捕获关于用户 WALID 创建的表／昵称的统计信息
 -- db2look 输出被发送到名为 db2look.sql 的文件中
​
示例: db2look -d DEPARTMENT -u walid -e -wrapper W1 -o db2look.sql
​
 -- 这将生成由用户 WALID 创建的所有表的 DDL 语句
 -- 还将生成适用于包装器 W1 的用户 WALID 所创建所有联合对象的 DDL
 -- db2look 输出被发送到名为 db2look.sql 的文件中
​
示例: db2look -d DEPARTMENT -u walid -e -server S1 -o db2look.sql
​
 -- 这将生成由用户 WALID 创建的所有表的 DDL 语句
 -- 还将生成适用于服务器 S1 的用户 WALID 所创建所有联合对象的 DDL
 -- db2look 输出被发送到名为 db2look.sql 的文件中

1.8 DB2 日志

1.8.1 DB2 事务日志满了如何解决

概念：
事务日志满指当前事务无法写入到活动日志中（主日志文件和辅助日志文件已全部用完或者没有足够当前事务写入的空间）;
日志磁盘空间已满指辅助日志文件还未使用完，磁盘空间已经满了。
db2数据库事务日志文件分为主日志文件和辅助日志文件，主日志文件已分配空间，辅助日志文件使用时再分配。
--查看事务日志配置（mid为数据库名称）：
db2 get db cfg for mid
​
运行结果：
​
日志文件大小（4KB）                         (LOGFILSIZ) = 1024
主日志文件的数目                           (LOGPRIMARY) = 13
辅助日志文件的数目                          (LOGSECOND) = 4
已更改的至日志文件的路径                   (NEWLOGPATH) =
日志文件路径                                            = D:\DB2\NODE0000\SQL00003\SQLOGDIR\
总事务日志容量大小=(LOGPRIMARY+LOGSECOND)*LOGFILSIZ*4k
ps:(13+4)*1024*4kb=68mb
--事务日志满解决办法：
（1） 增大日志文件大小：
db2 udpate db cfg for mid using LOGFILSIZ 8192
或者增大主日志文件个数：
db2 update db cfg for mid using LOGPRIMARY 15
或者增大辅助日志文件个数：
db2 update db cfg for mid using LOGSECOND 10 
（2）停掉引起这个错误的应用程序或者停掉所有的应用程序，再重启数据库
db2 force applications all
db2 force application (应用程序句柄 通过db2 list applications获取)
db2stop
db2start

1.8.2 db2 事务日志占满磁盘空间如何清除日志

PRUNE HISTORY 命令用于从恢复历史记录文件中删除条目(暂未实践)

参考官方文档:PRUNE HISTORY/LOGFILE command using the ADMIN_CMD procedure - IBM Documentation