DB2数据库安装和管理
DB2 数据库安装
-
解压软件包
tar zxvf v11.5.7_linuxx64_server_dec.tar.gz -
检查安装环境
./db2prereqcheck -
开始安装DB2软件
./db2_install 安装server -
创建组和用户
groupadd db2adm4 groupadd db2fen4 useradd -d /home/db2inst4 -m db2inst4 -g db2adm4 useradd -d /home/db2fenc4 -m db2fenc4 -g db2fen4 passwd db2inst4 passwd db2fenc4 -
创建数据库实例
cd /opt/ibm/db2/V11.5/instance/ ./db2icrt -u db2fenc4 db2inst4 --创建名称为db2inst4的实例 -
测试是否创建实例成功并创建数据库
su - db2inst4 db2start ps -ef | grep db2 ##创建模板库 db2sampl ##手动创建数据库 db2 create db jikedb(数据库名) ##查看当前实例下面所存在的数据库 db2 list db directory ##连接数据库 db2 connect to jikedb ##断开数据库连接 db2 terminate db2 connect reset
数据库管理
创建实例和数据库参数：
##查看当前数据库的表空间
db2 list tablespaces;
##远程连接实例
db2 attach to {instance_NAME}
##断开实例
db2 detach
#停止实例(实例的拥有者账户)
db2stop
#强制应用程序与用户与数据库连接断开
db2stop force
#查看实例参数
db2 get dbm cfg
#修改实例参数
db2 update dbm cfg using {参数名} {参数值}
#复位实例参数为默认值
db2 reset dbm cfg
#删除实例(必须具有root或管理员权限)
cd install_path/
./db2idrop {实例名}
#查看当前db2软件创建了哪些实例
db2ilist
#更新实例
db2iupdt
#迁移实例
db2imigr
#自动启动实例
db2iauto
#管理实例变量的命令
db2set
#查看已经设置的实例变量
db2set -all
#查看所有可进行定义的实例变量
db2set -lr
----------------------------------------------管理部分------------------
#实例目录
User_home/sqllib
#实例目录下面的目录详解
db2dump：此目录中的db2diag.log 文件记录db2实例的错误信息即实例的警告日志文件
sqldbdir:系统数据库目录
db2nodes.cfg:节点配置文件(做分区数据库需要)
db2systm:数据库管理器配置文件即实例的参数文件
#数据库路径和自动存储路径
create database jikedb3 on /jikedbauto3 dbpath on /jikedb3
--数据库路径：/jikedb3
--自动存储路径：/jikedbauto3
#创建数据库标准语句
db2 'create db jikedb2 on /jikedbauto21,/jikedbauto22 dbpath on /jikedb2 alias myjikedb pagesize 8192 dft_extent_sz 8 with "my jike db2"';
#mkdir /jikedbauto21 /jikedbauto22 /jikedb2
#chown -R db2inst4:db2adm4 /jikedbauto21 /jikedbauto22 /jikedb2 --授权文件目录
管理参数：
------------分区全局目录
--表空间信息文件#互为备份
-SQLSPCS.1
-SQLSPCS.2
--存储器组控制文件#互为备份
-SQLSGF.1
-SQLSGF.2
--全局配置文件
-SQLDBCONF
--历史记录文件
-DB2RHIST.ASC 和DB2RHIST.BAK
--与日志记录相关的文件
-SQLOGCTL.GLFH.1和SQLOGCTL.GLFH.2
--锁定文件
-SQLINSLK和SQLTMPLK
----------------特定成员目录
--缓冲池信息文件
-SQLBP.1和SQLBP.2
--本地事件监控器文件
--与日志记录相关的文件
-SQLOGCTL.LFH.1
-SQLOGCTL.LFH.2
-SQLOGCTL.LFH
--本地配置文件
-SQLDBCONF
--表空间存储的类型#三种存储的表空间可以共存于同一个数据库中
#系统管理的表空间
#数据库管理的表间
#自动存储的表空间
-------------------------创建表空间##############################
#LARGE 创建大型表空间
#REGULAR 创建常规表空间
#SYSTEM TEMPORARY 创建系统临时表空间
#USER TEMPPORARY 用户临时表空间
#tablespace-name 指定表空间的名称，不能重名和以“SYS”开头
#DATABASE PARTITION GROUP db-partition-group-name 为表空间指定数据库分区组
#PAGESIZE 指定表空间使用的数据页大小
#MANAGED BY 指定表空间存储的管理方式
#EXTENTSIZE 指定表空间区的大小
#PREFETCHSIZE 指定预取页的个数
#BUFFERPOOL 指定表空间所使用的缓冲池
---用于确定查询优化期间的I/O成本，单位是毫秒
#OVERHEAD = 平均寻道时间+ (((1/磁盘转速)*60*1000)/2)
#TRANSFERRATE、Transrate = (1/传送速率) *1000/1024000*4096 (4096是指4k的页)
#NO FILE SYSTEM CACHING 不使用文件系统的缓存特性
#FILE SYSTEM CACHING 使用文件系统的缓存特性
#DROPPED TABLE RECOVERY 设置是否启用已删除表的恢复特性
----创建表空间的标准语句
#创建表空间最简单命令
db2 create tablespace jikesimple;
#创建一个大型表空间
db2 create large tablespace jikelarge;
#创建一个用户临时表空间
db2 create user temporary tablespace jikeusertemp;
#创建一个8K页大小的表空间
db2 create tablespace jike8k pagesize 8k; --需要先创建一个8K的缓冲池并指定这个8K缓冲池
#创建一个数据库管理的表空间
db2 "create tablespace jikedms managed by database using (file '/dbpath/jikedms01.dbf' 10m, file '/dbpath/jikedms02.dbf' 10m)"; -- mkdir /dbpath,chown -R db2inst4:db2adm4 /dbpath/
常规表空间
| 数据页大小 | 表空间大小 |
|---|---|
| 4k | 64G |
| 8k | 128G |
| 16k | 256G |
| 32k | 512G |
大型表空间
| 数据页大小 | 表空间大小 |
|---|---|
| 4k | 8T |
| 8k | 16T |
| 16k | 32T |
| 32k | 64T |
表空间维护：
#查看表空间的详细信息
db2 list tablespaces [show detail];
#查看表空间的容器
db2 list tablespace containers for tablespace_id [show detail]
#修改表空间
alter tablespace tablespace_name
#添加表空间的容器
db2 alter tablespace tablespace_name add (device '/dev/rhd1' 1000,device '/dev/rhd2' 1000);
--例：db2 "alter tablespace JIKEDMS add (file '/dbpath/jikedms03.dbf' 10m)";
#扩展表空间的容器
db2 alter tablespace tablespace_name extend (all 1000);
--缩减容器的大小-RESIZE
db2 "alter tablespace JIKEDMS resize (file '/dbpath/jikedms03.dbf' 5m)";
#删除容器
db2 alter tablespace tablespace_name drop (file '/db2path1/datafile1');
#重命名表空间
db2 rename tablespace_old to tablespace_new
存储器组：根据数据读取写入的频率来分配存储介质的
#查看存储器组
db2 "select varchar(storage_group_name,20) as storage_group,
storage_group_id,
varchar(db_storage_path,20) as storage_path,
db_storage_path_state,
(fs_total_size/1000000) as total_path_MB,
(sto_path_free_size/1000000) as path_free_MB from table(admin_get_storage_paths('',-1)) as T1
";
#创建存储器组
db2 "create stogroup sg1 on '/home/db2inst4/sgpath1'" --mkdir:chown /home/db2inst4/sgpath1
#查看创建的存储器组
db2pd -db jikedb1 -storagegroup
#删除存储器组
db2 drop stogroup sg1
#创建表空间指定存储器组
db2 create tablespace tablespace_name using stogroup storagegroup_name;
#修改表空间使用存储器组
db2 alter tablespace tablespace_name using stogroup storagegroup_name;
#查看表空间与存储器组的关系
db2 "select TBSPACE,SGNAME,SGID from syscat.tablespaces";
表的操作：
#创建表并指定表空间[和索引]
db2 "create table mytable(id int)" in TEST_TBS [INDEX IN jeewx_idx;]
#删除表
db2 drop table mytable
DB2数据库的备份与恢复
备份
#备份文件的命名惯例
--备份类型
-0 表示完整的数据库级别备份
-3 表示表空间级别的备份
-4 表示由LOAD COPY YES 命令生成的备份映像
#查看数据库是否有应用连接
db2 list applications
#离线备份
db2 BACKUP DATABASE testdb
#在线备份
db2 BACKUP DATABASE testdb online
#在线备份包含日志文件
db2 BACKUP DATABASE testdb online include logs
#指定备份文件目录
db2 BACKUP DATABASE testdb [online] to /home/db2inst4/db1,/home/db2inst4/db2,/home/db2inst4/db3
#备份指定表空间
db2 "BACKUP DATABASE testdb tablespace [online] (syscatspace,userspace1) to /home/db2inst4/db"
#增量备份
db2 backup database testdb [online] incremental to /home/db2inst4/dbinr
#delta备份
db2 backup database testdb [online] incremental delta to /home/db2inst4/dbinr
#备份数据库所有分区
db2 backup database testdb ON ALL DBPARTITIONNUMS TO /dev3/backup
#使用TSM备份即磁带备份
db2 backup database testdb use tsm open 2 sessions with 4 buffers
#TSM备份即磁带备份并指定数据库分区中的表空间
db2 backup database testdb ON DBPARTITIONNUMS(1,2) TABLESPACE (USERSPACE1) ONLINE USE TSM
###检查备份文件的完整性
db2ckbkp -h [备份集文件名]
---------------------------------查看恢复历史记录文件的示例
#查看数据库自19980201时间点到现在的更新历史记录信息
db2 list history since 19980201 for testdb
#查看包含某个表空间的数据库历史备份信息
db2 list history backup containing usespace1 for testdb
#查看数据库历史的删表记录
db2 list history dropped table all for db testdb
#查看数据库历史的所有操作
db2 list history all for testdb
#查询分区数据库历史信息 YYMMDDhhmmss
db2_all "db2 list history since 20210101 for testdb"
#db2查看错误码
[db2inst4@linux140 ~]$ db2 backup database jikedb
SQL1035N The operation failed because the specified database cannot be
connected to in the mode requested. SQLSTATE=57019
[db2inst4@linux140 ~]$ db2 ? SQL1035N
恢复
##查看数据库归档设置
db2 get db cfg for testdb | grep -i LOGARCHMETH logprimary (主日志数)和 logsecond（辅日志数）
###db2数据库在线备份和增量备份以
db2 get db cfg for testdb | grep -i trackmod
db2 update db cfg using trackmod on; 启用增量备份功能
##配置数据库归档路径
db2 update db cfg for sample using LOGARCHMETH1 disk:/home/db2inst4/sample_arch/
如果报
SQL1024N A database connection does not exist. SQLSTATE=08003
原因:需要备份数据库一次,然后在修改主日志文件和此日志文件的归档路径
解决方法：（需要离线备份一次，再连接数据库）
--查看归档日志目录有没有数据生成
--连接数据库即可做在线备份
#恢复方法
db2_all '<<+0(第几个分区)< db2 RESTORE DATABASE testdb FROM /dev3/backup TAKEN AT 20210101111111 INTO testdb REPLACE EXISTING'
#增量备份的还原方法
restore db testdb incremental automatic taken at (时间戳) --如果是多个增量的话可根据时间戳一个个去追
#前滚恢复示例
restore db testdb from /dev3/backup logtarget /dev3/logs --先整库恢复再通过日志前滚
rollforward db testdb to end of logs and stop overflow log path /dev3/logs
-------------------------------------不完全恢复
##在线备份数据库包含归档日志
db2 backup database testdb online 20221010120110 --记录时间戳或者通过db2 list history all for testdb 查看最近一次备份的时间戳
##通过备份时间戳恢复数据库
db2 restore database testdb taken at 20221010120110
##前滚数据库
db2 rollforward database testdb to end of backup and complete --恢复完成
-------------------------------------完全恢复
##在线备份数据库包含归档日志
db2 backup db testdb online [to /home/db2inst4/backup/] include logs --记录时间戳或者通过db2 list history all for testdb 查看最近一次备份的时间戳
##通过备份时间戳恢复数据库
db2 restore database testdb taken at 20221010120110
##前滚数据库
db2 rollforward database testdb to end of logs and complete --恢复完成
-------------------------------------表空间恢复
##恢复数据库中的某个表空间
db2 "restore database testdb tablespace(TEST_TBS) online" taken at 20221010120110
--此时TEST_TBS表空间处于Roll forward pending 状态
##前滚表空间
db2 "rollforward database testdb to end of logs and complete tablespace (TEST_TBS)"
##再次检查表空间状态
db2 list tablespaces
-------------------------------------数据库的增量恢复操作
#db2 backup database testdb online include logs --全备 20221010144215
#db2 backup database testdb online incremental include logs ---增量备份 20221010144231
##恢复数据库并自动使用增量备份
db2 connect to testdb
db2 restore database testdb incremental automatic taken at 20221010144231
##前滚数据库
db2 rollforward database testdb to end of logs and complete --恢复完成
历史查询信息结果分析
--例子
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20221010092907001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 6 tablespace(s):
00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 JIKESIMPLE
00005 JIKELARGE
00006 JIKE8K
----------------------------------------------------------------------------
Comment: DB2 BACKUP JIKEDB OFFLINE
Start Time: 20221010092907
End Time: 20221010092915
Status: A
----------------------------------------------------------------------------
EID: 17 Location: /home/db2inst4
查询历史记录信息结果表分析：
| Op(Operation) | Obj (Object) | Type |
|---|---|---|
| A - Create table space | D - Database | Alter table space operation types: |
| B - Backup | I - Index | -C -Add container |
| C - Load copy | P - Table space | -R - Rebalance |
| D- Drop table | T - Table | Archive log operation types: |
| F - Rollforward | R - Partitioned table | -F -Failover archive path |
| G - Reorganize | -M -Secondary(mirror) log path | |
| L - Load | -N -Archive log command | |
| N - Rename table space | Backup and restore operation types: | |
| O - Drop table space | -D -Delta offline | |
| Q - Quiesce | --E -Delta online | |
| R - Restore | --F -Offline | |
| T - Alter table space | --I -Incremental offline | |
| U - Unload | --M -Merged | |
| X - Archive log | --N -Online | |
| --O -Incremental online | ||
| --R - Rebuild | ||
| --Z -Quiesce reset |
db2导出工具： DB2 EXPORT
- EXPORT可以使用SQL SELECT 语句将数据从数据库表提取到文件中
- 数据可以被导出到DEL、IXF或WSF文件中，不能导出到ASC中
- 在EXPORT中需要包括MESSAGES子句，以捕获导出过程中遇到的错误、警告等有用的消息
- 调用EXPORT实用程序，用户需要拥有SYSADM或DBADM权限，或者拥有EXPORT命令中所访问的表或视图上的CONTROL或SELECT特权
常用命令
--查看表所在的表空间
db2 "select TABNAME,TBSPACE from syscat.tables where TABNAME='EMPLOYEE1'"
--修改表结构或者列中的数据类型
alter table employee add depart_id integer
alter table employee alter name set data type varchar(20) alter gender set not null
--查看表结构
db2 describe table employee
--创建索引
create index index_name on tablename(列名)
--升序和降序索引
create index index_name on tablename(id DESC,age ASC)
--查看DB2数据库的版本
db2licm -l
---查看实例参数
db2 get dbm cfg
---修改实例参数配置
修改实例配置参数，用 db2 update dbm cfg using 参数名 新值
