---

创建数据库的示例

---

1. create database jikedb1 on /jikedb1

   –自动存储路径：/jikedb1

   –数据库路径：/jikedb1

2. create database jikedb2 automaticstorage no on /jikedb2

   –未使用自动存储特性

   –数据库路径：/jikedb2

3. create database jikedb3 on /jikedauto3 dbpath on /jiked3

   –自动存储路径：/jikedauto3

   –数据库路径：/jikedb3

4. create database jikedb4 automatic storage yes on /jikedauto41,/jikedauto42,/jikedauto43 dbpath on /jikedb4

   –自动存储路径：/jikedauto41,/jikedauto42,/jikedauto43

   –数据库路径：/jikedb4

---

参数

• automatic storage： 自动存储，默认开启
• ALIAS：db-alias为数据库指定别名
• CODESET (字符集) 和 TERRITORY （地域） 用于指定代码页
• COLLATE：指定数据库的整理次序
• PAGESIZE：指定数据库的表空间和缓冲池所使用的默认数据页大小
• DFT_EXTENT_SZ：指定数据库的表空间默认使用的区的大小
• SYSCATSPACE：用CATALOG TABLESPACE指定，存储DB2系统编目，即“数据字典”
• TEMPSPACE1：用TEMPORARY TABLESPACE指定，是DB2放置分组、排序、连接和重组等操作中间结果集的临时存储区域
• USERSPACE1：用USER TABLESPACE指定，默认情况下存储所有用户对象的表空间
• with: 注释，用于数据库的注释

---

• 在数据库服务器上创建用户和组

  创建组db2iadm1（实例管理组）,db2fgrp1（DB2 fencing管理组）和dasadm1（数据库管理员用户组），其中dasadm1组在安装数据库时已经创建，我们只需要创建db2iadm1和db2fgrp组
  
      # groupadd -g 206 db2iadm1
    
      # groupadd -g 203 db2fgrp1
  

• 创建用户db2inst1(实例管理用户)、db2fenc1(DB2 fencing 管理用户)和dasusr1(数据库管理员用户)，其中dasusr1用户在安装数据库时已经创建，我们只需要创建db2inst1和db2fenc用户

      # useradd -g db2iadm1 -u 209 -d /home/db2inst1 db2inst1
  
      # useradd -g db2fgrp1 -u 210 -d /home/db2inst1 db2fenc1
  

• 在数据库服务器上更改目录权限

      # mkdir /db2data
  
      # chown -R db2inst1:db2iadm1 /home/db2inst1
  
      # chown -R db2fenc1:db2fgrp1 /home/db2fenc1
  
      # chown -R db2inst1:db2iadm1 /db2data
  

• 进入安装数据库目录

    cd /opt/ibm/db2/V10.5/instance
    chmod -R 777 *
  

• 创建实例 db2inst1

    ./db2icrt -p 50111 -u db2fenc1 db2inst1			 
  

• 创建数据库：

    db2 create db jiked1;
  

• 指定参数：

    db2 'create db jikedb2 on /jikedbauto21,/jikedbauto22 dbpath on /jikedb2 alias myjikedb pagesize 8192 dft_extent_sz 8 with "my jike db2"'; 
  

• 新建数据库:

    db2 create db jikedb2 using codeset gbk territory CN collate using identity
  
  注意：
  
  1.要用实例用户登录.
  
  2.记得创建相应的目录：
  
  3.目录要改为实例的权限.
  

查看是否创建成功：

db2 list db directory

查看表空间：

db2 list tablespaces

查看目录是否存在(dev为目录名称)：

ls -ltr | grep -i dev

修改数据库配置参数

db2 update db cfg for testdb using LOGPRIMARY 50
db2 update db cfg for testdb using LOGSECOND 20
db2 update db cfg for testdb using LOGFILSIZ 10240

启动数据库服务：

db2start

停止数据库服务：

db2stop 

强制应用程序与用户与数据库断开：

db2stop force (强制关闭)

断开当前与数据库的连接：

db2 terminate  / db2 connect reset

切换用户 su - db2inst1 # 启动数据库实例

db2start  # 在实例中创建数据库 

db2sampl # 创建一个DB2SAMPL样板库# 或

db2 create db wellsdb # 用db2的命令来创建数据库,这里的wellsdb是数据库名，可改成自己的

启用日志归档模式

db2 update db cfg for testdb using LOGRETAIN ON 

设置日志归档目录

db2 update db cfg for testdb using LOGARCHMETH1 DISK:/db2/data/logs 

从包含日志的备份集恢复
恢复

db2 RESTORE db testdb FROM /db2/data/backup taken at 20100514001917 LOGTARGET /db2/data/logs/ 

前滚

			db2 "rollforward db testdb to end of logs and stop overflow log path(/db2/data/logs/)" 

从不包含日志的备份集恢复
恢复

db2 RESTORE db testdb FROM /db2/data/backup taken at 20100514001917

前滚
db2 “rollforward db testdb to end of logs and stop overflow log path(/归档日志目录/)”

查看实例参数：db2 get dbm cfg
修改实例参数：db2 update dbm cfg using 参数名 参数值
复位实例参数为默认值(重置参数)：db2 reset dbm cfg

删除实例
删除实例，必须具有root或者系统管理权限
删除实例前，要确保实例已经停止
删除实例的命令：db2idrop

查看实例：db2ilist
更新实例：db2iupdt
迁移实例：db2imigr
自动启动实例：db2iauto

管理实例变量的命令：db2set
查看实例设置的实例变量：db2set -all
查看所有可进行定义的实例变量：db2set -lr

---

db2查看表空间
连接db2数据库
Db2 connect to xxx user xxx using xxx
查看数据库下所有的表空间情况
Db2 LIST TABLESPACES SHOW DETAIL
查看表空间下的文件：
db2 list tablespace containers for 表空间对应的数字编号
增加表空间容器：
db2 “ALTER TABLESPACE 表空间名 ADD (file ‘文件路径’ 30G)”
修改表空间大小：
db2 ALTER TABLESPACE 表空间名 RESIZE(FILE ‘文件路径’ 10G)
文件路径就是表空间的具体存放地址，30g，10g就是增加或修改的表空间的内存大小
eg：表空间是MXZ，文件路径是D:/home/data/TBS_MXZ1
增加表空间：db2 “ALTER TABLESPACE MXZ ADD (file ‘D:/home/data/TBS_MXZ1’ 30G)”
修改表空间：db2 ALTER TABLESPACE MXZ RESIZE(FILE ‘D:/home/data/TBS_MXZ1’ 10G)
由于db2的特殊性，增加完表空间后需要进行平衡，可以通过以下命令进行查看进度。
查看重新平衡进度（在实际使用中，发现平衡需要一段实际，再平衡时期，新添加的表空间不能使用）
db2 List utilities show detail

---

数据库恢复场景：不完全恢复

---

1. 创建测试表： db2 “create table mytable(id int)”
2. 插入测试数据: db2 “insert into mytable values(1)”
3. 检查测试数据：db2 “select * from mytable”
4. 在线备份数据库（需要开启归档日志）：db2 backup database testdb online (备份时间戳：20220507172451)
5. 在测试表中插入测试数据: db2 “insert into mytable values(2)”
6. 检查测试数据：db2 “select * from mytable”
7. 查看备份信息：db2 list history all for testdb
8. 恢复数据库：db2 restore database testdb taken at 20220507172451
9. 前滚数据库：db2 rollforward database testdb to end of backup and complete
10. 检测测试数据：db2 “select * from mytable”

---

数据库恢复场景：完全恢复

---

1. 在测试表中插入测试数据: db2 “insert into mytable values(2)”
2. 在线备份数据库：db2 backup database testdb online include logs (备份时间戳：20220507202547)
3. 检查测试数据：db2 “select * from mytable”
4. 查看备份信息：db2 list history all for testdb
5. 恢复数据库：db2 restore database testdb taken at 20220507202547
6. 前滚数据库：db2 rollforward database testdb to end of logs and complete
7. 检测测试数据：db2 “select * from mytable”

---

数据库表空间的恢复

---

1. 恢复某个表空间: db2 “restore database testdb tablespace(USERSPACE1) on online taken at 20220507202547”
2. 查看表空间状态：db2 list tablespace
3. 前滚表空间：db2 “rollforward database testdb to end of logs and complete tablespace(USERSPACE1)”
4. 查看表空间状态：db2 list tablespace

---

数据库的增量恢复

---

1. 对数据库进行在线的全备份：db2 backup database testdb online include logs (备份时间戳：20220507202547)
2. 对数据库进行增量备份：db2 backup database testdb online incremental include logs
3. 检查数据库的备份数据：db2 list history all for testdb
4. 恢复数据库：db2 restore database testdb incremental automatic taken at 20220507202547
5. 前滚数据库：db2 rollforward database testdb to end of logs and complete

查看恢复的状态：db2 list utilities show detail

DB2开启归档日志

---

1. 查看是否开启归档

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

2. 为了保证归档顺利，先停止应用连接

    [db2inst1@db01 ~]$ db2 force applications all
    
    	DB20000I  The FORCE APPLICATION command completed successfully.
    
    	DB21024I  This command is asynchronous and may not be effective immediately.
   

3. 开启归档

    [db2inst1@db01 ~]$ db2 update db cfg for testdb using LOGARCHMETH1 "DISK:/home/db2inst1/archivelog"
   
    	DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
   

4. 要使得归档生效，需要重启db2

    [db2inst1@db01 ~]$ db2stop force
    
    	08/03/2019 20:00:18     0   0   SQL1064N  DB2STOP processing was successful.
    
    	SQL1064N  DB2STOP processing was successful.
    
    [db2inst1@db01 ~]$ db2start 
    
     	08/03/2019 20:00:27     0   0   SQL1063N  DB2START processing was successful.
    
     	SQL1063N  DB2START processing was successful.
    
    [db2inst1@db01 ~]$ db2 connect to testdb
    
     	SQL1116N  A connection to or activation of database "TESTDB" cannot be made 
    
     	because of BACKUP PENDING.  SQLSTATE=57019
   

5. 第四步连接的时候报错，需要对数据库进行一次全备

    [db2inst1@db01 ~]$ mkdir /db2data/backup
    
    [db2inst1@db01 ~]$ db2 backup db testdb to /db2data/backup/ 
    
     	Backup successful. The timestamp for this backup image is : 20190803200150
   

6. 验证是否可以连接成功

    [db2inst1@db01 ~]$ db2 connect to testdb
    
     	Database Connection Information
    
     	Database server        = DB2/LINUXX8664 10.1.0
    
     	SQL authorization ID   = DB2INST1
    
     	Local database alias   = TESTDB 
   

7. 验证是否可以成功归档

    [db2inst1@db01 ~]$ db2 terminate
   
    	DB20000I  The TERMINATE command completed successfully.
   
    [db2inst1@db01 ~]$ db2 archive log for db testdb 
   
    	DB20000I  The ARCHIVE LOG command completed successfully.
   

---

查看各组件#

---

• 切换到实例用户:
  su db2inst1
• 查看创建的实例:
  db2ilist
• 查看当前实例下管理了哪些数据库:
  db2 list db directory
• 查看数据库版本:
  db2level
• 连接数据库:
  db2 connect to 数据库名
• 查看表空间:
  db2 list tablespaces
• 连接实例的命令：
  db2 attach db2inst1
• 断开实例的命令：
  db2 detach
• 搜索文件所在目录
  locate 文件名
• 停止应用连接
  db2 force applications all
• 查看应用连接
  db2 list applications [for test]
• 修改服务器端口
  db2 update dbm cfg using SVCENAME 50000
• 查看服务器端口
  db2 get dbm cfg | grep -i SVCENAME
• 查看备份历史记录
  db2 list history backup all for testdb

---

数据库备份

---

1.离线备份

db2 backup database 数据库名

2.在线备份

db2 backup database 数据库名 online

3.在线备份包含日志

db2 backup database 数据库名 online include logs

4.备份指定路径

db2 backup database 数据库名 online to 路径

表空间备份

db2 “backup database 数据库名 tablespace(表空间1，表空间2) online to 路径”

增量备份

db2 backup database 数据库名 online incremental to 路径

---

数据库还原

---

• 查看执行命令顺序

    db2ckrst -d testdb -t 20220510205308 -r database
  

• 备份数据库 （不同数据库名使用重定向redirect）

  db2 restore db 旧数据库名 incremental from 备份路径 taken at 备份时间戳 to 数据库路径 into 新数据库名 redirect LOGTARGET 旧数据库归档日志路径

  示例

    db2 restore db testdb incremental FROM /home/backup taken at 20220510160047 to /home/db2inst2 into testdb LOGTARGET /home/archivelog/
  

• 前回滚日志

  db2 “rollforward db 新数据库名 to end of logs and [stop|complete] overflow log path (归档日志路径)”

  db2 RESTORE db testdb continue;

  示例：

    db2 "rollforward db testdb to end of logs and stop overflow log path (/home/archivelog/)"
  
    db2 "rollforward db testdb to end of logs and complete overflow log path (/home/archivelog/)"
  

• 显示恢复详情

    db2 list utilities show detail
  

• 显示回滚进度

    db2 rollforward db test query status
  

整理总结一下db2相关的用法以及见到的错误与解决方式
（如果命令执行有问题，可能是因为在服务器端没有加db2 “” 或者在可视化工具中加了造成的，之前遇到过一次怎么百度都没有找到解决方式，后来自己试着加了db2 “”，命令就可以了，因为笔者一直在数据库服务器进行的操作= =）

查看表空间：

 db2 list tablespaces show detail

增加表空间(修改)

db2 "alter tablespace table_name extend (all 50000)"    resize

查看字符集

 locale 

修改字符集

export LANG=en_US.UTF-8 

查看数据库配置

db2 get dbm cfg

链接数据库

db2 connect to  database

查看表

db2 list tables

操作数据库

db2 "SQL" >>a.log

查看所有连接

db2 list application

查看日志信息

db2 get db cfg | grep LOG

创建一样的表

create table table_temp like table

查看表结构

db2 describe table cm_sys_para

分组查询，聚合函数

select tran_date,msg_tp, row_number() over(partition by tran_date,msg_tp order by tran_date,msg_tp) as row_num  from table

删除重复数据

delete from (select * from (select PROTOCOLNO,PAYERACCNO,PAYEEACCCLEARBANK,row_number() over(partition by PROTOCOLNO,PAYERACCNO,PAYEEACCCLEARBANK order by PROTOCOLNO,PAYERACCNO,PAYEEACCCLEARBANK) as row_num from table) as e where row_num > 1)

第二种方式

db2 "declare tmpcursor  cursor for select * from table"
declare atmycursor cursor for select distinct PROTOCOLNO,PAYERACCNO,PAYEEACCCLEARBANK,STARTDATE from table where PROTOCOLSTATUS = '1' and EFFECTFLAG = '1';
load from tmpcursor  of cursor insert into  table;

导出数据：

db2 "export to 全路径.del of del select * from table"

导入数据

db2 "load from 全路径.del of del insert into table"

删除

select distinct length(SIGN_NO) from table
delete from table where length(SIGN_NO) = 16

问题：

Category Timestamp Duration Message Line PositionError 2019/6/6
10:08:57 0:00:00.100 DB2 Database Error: ERROR [42601]
[IBM][DB2/AIX64] SQL0104N An unexpected token “CREATE” was found
following “”. Expected tokens may include: “RECLAIM”.
SQLSTATE=42601 10 0 reorg table table_name CREATE INDEX I_UPD_TIME
ON table_name ( UPD_TIME ASC );

解决：
不是有效字符，检查sql发现缺少分号；

问题：

db2 the transaction log for the database is null

解决：
db2日志文件满了的缘故

db2 get db cfg | grep LOG

db2 update db cfg using LOGFILSIZ 10240
db2 update db cfg using LOGPRIMARY 100
db2 update db cfg using LOGSECOND 100

db2stop force
db2start

问题：

Category Timestamp Duration Message Line PositionError 2019/6/6
11:18:23 0:00:00.029 : ERROR [57016] [IBM][DB2/AIX64] SQL0668N
Operation not allowed for reason code “7” on table
“”. SQLSTATE=57016 2 0

解决：
需要重构一次表，如果对表字段进行了修改

reorg table tablename

DB2基础学习

DB2简介
1.IBM公司的数据库系统
2.关系型数据系统
3.适用于大型应用系统，如：某企业门户网站，商务智能系统
4.支持大多数主流操作系统，如：Windows，Linux，AIX
5.与平台无关的基本功能和支持标准SQL语句

DB2版本
1.DB2 Express-C (社区免费版，提供了基本的数据库能力，启动速度快，适合企业做测试开发以及入门学习的人使用）
2.DB2 Express (是一种功能全面的支持Web 的客户机/服务器RDBMS，主要用于小型企业，收费)
3.DB2 Workgroup Server(DB2工作组版，适用于部门或者中型企业环境，也是全功能数据库)
4.DB2 Enterprise Server(企业服务器版，企业使用的最多，适合任何不同负载的工作环境，没有任何CUP，内存，数据库大小的限制)
5.DB2 Advanced Enterprise Server Edition(高级企业服务器版(包含企业服务器所有功能，额外还有一组工具组件)

DB2体系结构简图

1.一个数据库实例可以管理一到多个数据库
2.数据库由一到多个表空间组成
3.表存储在表空间中
注：
1.数据库必须受实例的管理
2.在非集群的情况下，一个数据库只能受一个实例的管理
3.每个数据库都有自己的配置文件
4.表空间用于存储数据库对象，比如表，索引等

查看各组件
切换到实例用户db2inst1
查看创建的实例
db2ilist
查看当前实例下管理了哪些数据库
db2 list db directory
查看数据库版本
db2level
连接数据库
db2 connect to 数据库名
查看表空间
db2 list tablespaces

实例的概念
实例为数据库提供了运行时环境
实例是由一组后台进程和共享内存组成的
数据库是物理的，而实例是逻辑的
实际生产系统中，实例的个数选择要适情况而定
比如生产环境和开发测试环境在同一个系统中，那么可以创建两个实例，这样生产环境和开发测试环境互不影响

管理实例
实例与操作系统用户的关系
Windows：不需要创建用户，默认实例名为DB2
Linux&Unix：实例名与用户名一样

实例目录

表空间、容器、区与数据页


数据库和自动存储的位置

默认的表空间

数据库的目录

数据库备份
离线备份
db2 BACKUP DATABASE 数据库名
在线备份
db2 BACKUP DATABASE 数据库名 online
在线备份包含日志
db2 BACKUP DATABASE 数据库名 online include logs
备份指定路径
db2 BACKUP DATABASE 数据库名 online to 路径

表空间备份
db2 ''backup database 数据库名 tablespace(表空间1,表空间2) online to 路径"

增量备份
db2 backup database 数据库名 online incremental to 路径

DB2的数据类型

数字数据类型——整数

数字数据类型——小数

数字数据类型——浮点数

字符串数据类型——单字节字符串

字符串数据类型——双字节字符串

字符串数据类型——长数据类型

日期时间数据类型

XML数据类型

用户定义的数类型