DB2数据库基础

1、查看版本相关信息
db2ls
db21s -q -b /opt/ibm/db2/V9.7 #可查看安装组件相关信息2、在linux平台下创建实例
db2icrt 的用法：
-p：指定实例端口号
-s：指定实例类型
-u：指定fenced user
groupadd -g 1100 db2iadm1 #创建实例管理组
groupadd -g 1101 db2fadm1 #创建fenced组
useradd -g db2iadm1 -u 1100 -m -d /home/db2inst1 db2inst1 # 创建实例用户
useradd -g db2fadm1 -u 1101 -m -d /home/db2fenc1 db2fenc1 # 创建fence用户
切换至root目录
cd /opt/ibm/db2/V9.7/instance
./db2icrt -p 50000 -u db2fenc1 db2inst1 #创建实例
3、数据库参数配置
A. 配置实例：
查询：db2 get dbm cfg
设置：db2 update dbm cfg using <parameter_name> <value> B. 配置数据库：
查询：db2 get db cfg for <database_name>
设置：db2 update db cfg for <database_name> using
<parameter_name> <value>
应用实例：
db2 update db cfg for simudb using LOGPRIMARY 80
恢复默认设置：db2 reset db cfg
4、实例的基本操作
db2ilist #显示版本下面有哪些实例db2greg -dump #显示服务器上面有哪些实例db2 get instance #显示当前实例
5、数据库实例的开启
su - db2inst1
db2start #启动实例
6、数据库实例的关闭
db2stop #停止实例
如果当前实例下有数据库连接，db2stop 会报错。

db2 force applications all #断开所有应用连接db2stop #停止实例
db2stop force #强制停止
7、数据库实例的更新
cd /opt/ibm/db2/V9.7/instance
./db2iupdt db2inst1 #更新实例
8、数据库实例的删除
切换到root用户下
cd /opt/ibm/db2/V9.7/instance
./db2idrop db2inst1 #删除实例
9、列出当前实例中有哪些数据库
su - db2inst1
db2 list db directory
8、创建数据库
su - db2inst1
db2 “create database test”
#SQL1032N No start database manager command was issued.
SQLSTATE=57019
db2start
db2 “create database test”
#SQL0204N "SYSTEM_1386_US" is an undefined name. SQLSTATE=42704 db2 "create database test using codeset gbk territory cn"
db2 list db directory
使用UTF-8编码
db2"create database test on '/home/db2inst1' using codeset UTF-8 territory CN"
# on '/home/db2inst1' 表示数据库路径，一般情况下
'/home/db2inst1'为默认数据库路径
9、数据库的连接与断开
A.连接数据库
db2 connect to test
db2 connect to <database> user <username> using <password> db2 connect to test user username using password
B.断开数据库
db2 connect reset 或
db2 disconnect <database>
10、列出当前实例中所有激活的数据库
su - db2inst1
db2 list active databases
#SQL1032N No start database manager command was issued.
SQLSTATE=57019
db2 list db directory
db2start
db2 list active databases
db2 connect to test
db2 list active databases
11、查看表的空间
db2 list tablespaces
db2 list tablespace containers for 8 show detail #8是表空间id db2pd -d test -tablespaces #显示表空间配置信息使用情况和容器信息
12、列出数据库中所有用户表
db2 connect to amfedb
db2 list tables
13、在数据库test中创建表student
db2 connect to test
db2 "create table student (id int,fname varchar(30),age int)"
db2 list tables
14、向表student中添加数据信息
db2 "insert into student values (1,'Tom',22)"
db2 "insert into student values (2,'Jack',21)"
db2 "insert into student values (3,'Sunrier',25)"
15、显示表student所有的信息
db2 "select * from student
16、更改表student中的数据(如将Sunrier的年龄改为22)
db2 "select * from student"
db2 "update student set age=22 where fname='Sunrier'"
db2 "select * from student"
17、查看表student结构
db2 describe table student
或
db2 "describe select * from student"
18、创建一个新表(如people)与数据库中表(如student)结构相同
db2 list tables
db2 "select * from student"
db2 "create table people like student"
db2 list tables
db2 describe table people
19、将原来数据库中某个表的数据导入与它相同结构的新表中
db2 list tables
db2 describe table student
db2 describe table people
db2 "select * from student"
db2 "select * from people"
db2 "insert into people select * from student"
db2 "select * from people"
20、修改一个表的字段类型
如将表people中的fname字段把varchar(30)改为varchar(28)格式:db2 "alter table <tablename> alter column <columnname> set data type <datatype>"
db2 "select * from people"
db2 "alter table people alter column fname set data type
varchar(28)"
#SQL0668N Operation not allowed for reason code "7" on table
"DB2INST1.SANGUO". SQLSTATE=57016
解决方法：执行命令：reorg table xxx；对表进行优化重组。

db2 describe table people
db2 "select * from people"
21、向一个表添加字段(如向表people中添加备注信息字段notes)
格式:db2 "alter table <tablename> add <columnname>
<datatype>"
db2 describe table people
db2 "select * from people"
db2 "alter table people add notes varchar(100)"
db2 describe table people
db2 "select * from people"
db2 "alter table people add score integer"
db2 describe table people
db2 "select * from people"
给表中添加带默认值的字段(向表people中添加地址字段address 默认设置为shanghai)
格式:db2 "alter table <tablename> add column <columnname>
<datatype> not null with default <value> "
db2 "alter table people add column address varchar(30) not null with default 'ShangHai'"
db2 describe table people
db2 "select * from people"
22、删除表中的某个字段(如删除表people中的字段score)
格式:db2 "alter table <tablename> drop column <columnname>"
db2 describe table people
db2 "select * from people"
db2 "alter table people drop column score"
db2 describe table people
db2 "select * from people"
注:如果drop掉字段之后,可能会导致表查询/插入操作不能执行, 则需要执行一下reorg命令,优化数据结构,
格式如db2 reorg table <tablename>
db2 reorg table people
23、列出数据库中用户表
db2 list tables for user
24.、列出数据库中所有系统表
db2 list tables for system
25、列出数据库中所有表
su - db2inst1
db2start
db2 list active databases
db2 connect to test
db2 list tables for all
26、列出数据库中特定用户表
格式: db2 list tables for schema <username> su - db2inst1
db2start
db2 list active databases
db2 connect to test
db2 list tables for schema db2inst1
27、删除表中的数据
db2 delete from student where id=3
db2 "select * from student"
28、删除一个数据库中的某个表
db2 drop table student
db2 list tables
29、删除一个数据库test
db2 list db directory
db2 drop db test
#SQL1025N The database manager was not stopped because databases are still active.
db2 connect reset
db2 drop db test
db2 list db directory
注:删除数据库首先要断开数据库的连接
30、显示当前数据库连接有哪些应用程序
su - db2inst1
db2 list application
db2 connect to test
db2 list application
31、导出表中的数据
A. 以txt 格式导出
db2 "export to /home/db2inst1/teacher.txt of del select * from teacher"
ls
cat teacher.txt
B.以ixf格式导出
db2 "export to /home/db2inst1/teacher.ixf of ixf select * from teacher"
ls
cat teacher.ixf
32、导入数据到一个表中
db2 "select * from teacher"
db2 "import from teacher.txt of del insert into teacher"
ls
db2 "select * from teacher"
db2 "import from teacher.txt of del insert into teacher"
ls
db2 "select * from teacher"
33、利用脚本创建表
格式:db2 -tvf scriptName.sql(.txt)
teacher.sql为以下内容
----建立表teacher
create table teacher
(
id integer not null ,
fname varchar(20) not null,
address varchar(80) default '上海',
birth date,
primary key(id)
);
--建表结束
--以下为插入数据字段
db2 "insert into teacher values(1,'Lory','上海徐汇中','1978-08-06')"
db2 "insert into teacher values(2,'Sunrier','田林中学','1988-06-27')"
db2 describe table teacher
db2 "select * from teacher"
34、备份数据库
格式:db2 backup db <database name> to <dir name>
database name:表示数据库
to <dir name>:表示为备份到的目录路径,为可选项,默认在当前目录下
ls
db2 list db directory
db2 backup db test to /home/db2inst1
ls /home/db2inst1
db2 list active databases
注：执行备份命令时,如果出现无法执行,则先断开数据库的连接再执行备份命令.从上面可以看出没有断开也可以执行,但执行
完,发现数据库处于断开状态了。

db2 force application all
35、恢复数据库
格式:db2 restore db <database name> from <dir name> taken at
<timestamp>
database nam：表示恢复的数据库名
from <dir name>：表示为从哪个目录路径下恢复,为可选项,默认在
当前目录下
taken at <timestamp>：指定回复那个备份文件
例：db2 restore db amfedb from /home/db2inst1 taken at
20150203094124
db2 connect to test
db2 list tables
db2 "select * from teacher"
db2 drop table teacher
db2 list tables
db2 connect test
ls /home/db2inst1
db2 restore db test from /home/db2inst1
db2 list tables
db2 connect to test
db2 list tables
db2 "select * from teacher"
注:如果想把恢复的数据库更改为新的数据库名,则格式如下db2 restore db <database name> from <dir name> into <new
database name>
例:db2 restore db test from /home/db2inst1 into testdb
36、查看test数据库备份的历史记录
格式：db2 list history backup all for <database name>
db2 list history backup all for test
37、查看数据库的配置
db2 connect to <database> user <username> using <password> db2 get db cfg for <database>
database:数据库名
username:用户名
password:表示密码
38、设置数据库参数
db2 connect to <database> user <username> using <password> db2 get db cfg for <database>
db2 update db cfg for <database> using 参数名参数值
39、设置实例参数
db2 get dbm cfg
db2set DB2COMM=TCPIP
db2 update dbm cfg using 参数名参数值
如果将某个参数设为空值，可采用如下命令：
db2 update dbm cfg for sample using 参数名NULL
40、数据库的连接
A. 编目节点：
CATALOG TCPIP NODE node_name REMOTE hostname SERVER service_name
应用实例：
db2 catalog tcpip node acse remote 192.168.1.99 server 50000
B. 编目数据库：
CATALOG DB db_name AT NODE node_name
应用实例：
db2 catalog db ACSEDB at node db2inst1
C. 连接数据库
DB2 CONNECT TO database_name USER user_name
USING passwd
D. 在有些时候，在编目的时候可能会输入错误信息，比如敲错
ip或端口号，这就需要重新编目，在重新编目之前需要删除之前的编目配置，这就是反编目(uncatalog)。

DB2 UNCATALOG DATABASE database _name
# 反编目数据库DB2 UNCATALOG NODE node_name # 反编目节点E. 综合实例：
服务端：
db2set db2comm=tcpip # 配置监听协议db2 update dbm cfg using svcename 50000 # 配置实例端口db2stop
db2start
客户端：
db2 catalog tcpip node db2inst1 remote 192.168.1.99 server 50000 db2 catalog db ACSEDB at node db2inst1
db2 terminate # 使操作生效
db2 list node directory # 查看节点信息
db2 list db directory # 查看数据库信息db2 connect to ACSEDB user db2inst1 using 123456
41、设置日志归档模式
日志模式的设置通过LOGARCHMETH1 参数来实现。

当参数值等于OFF时，表示循环日志模式，当参数值为OFF之外的其他值时，表示归档模式。

db2 connect to amfedb
db2 get db cfg
cd /amfe/dbarchlog
ls
db2 update db cfg for amfedb using logarchmeth1 disk:/amfe/
dbarchlog #设置归档路径
若修改数据库LOGRETAIN参数，从循环日志模式改为归档日志模式，则会导致数据库backup pending状态
db2 connect to amfedb
#SQL1116N A connection to or activation of database "AMFEDB" cannot be made because of BACKUP PENDING. SQLSTATE=57019
此时，需要对数据库做离线全库备份，使状态恢复正常，以便正常访问。

db2 force applications all
db2 backup db amfedb /home/db2inst1
db2 list history archive log all for amfedb #查看归档日志历史记录
通过Type值识别日志历史类型：
P：表示创建主日志文件
M：表示镜像日志
F：表示归档失败
1：表示通过logarchmeth1 指定归档日志方法
2：表示通过logarchmeth2指定归档日志方法
42、事务日志满的处理方法
事务日志太大，日志空间太小，更改日志参数。

db2 update db cfg for amfedb using logsecond 20
db2 update db cfg for amfedb using logprimary 40
db2 update db cfg for amfedb using logfilsiz 65536
43、数据的备份
1、离线备份
db2 backup db <database name> to <path>
2、在线备份
db2 backup db <database name> online to <path> include logs
例：db2 backup db yhpzdb online to /home/db2inst1 include logs
注：在线备份要求日志设为归档模式
3、表空间备份
对于表空间备份，将两个相关联的表同时备份：
db2 backup db <database name> tablespace (<tbs1>,<tbs2>)
online to <path>
例：db2 list tablespaces show detail
#SQL1024N A database connection does not exist. SQLSTATE=08003
db2 connect to yhpzdb
#SQL1117N A connection to or activation of database "YHPZDB" cannot
be made because of ROLL-FORWARD PENDING. SQLSTATE=57019
db2 rollforward db yhpzdb to end of logs and complete
db2 connect to yhpzdb
db2 list tablespaces show detail
db2 "backup db yhpzdb tablespace
(SYSCATSPACE,USERSPACE1) online to /home/db2inst1"
4、增量备份
启用增量备份，需要将数据库参数trackmod 改为ON
db2 update db cfg for yhpzdb using trackmod on
db2 backup db yhpzdb #离线完全备份
db2 backup db yhpzdb online incremental delta #在线增量迭
代备份
db2 backup db yhpzdb online incremental #在线增量备份44、数据的恢复
1、数据库恢复
格式:db2 restore db <database name> from <dir name> taken
at <timestamp>
database name：表示恢复的数据库名
from <dir name>：表示为从哪个目录路径下恢复,为可选项,
默认在当前目录下
taken at <timestamp>：指定回复那个备份文件
例：db2 restore db yhpzdb from /home/db2inst1 taken at
20150203094124
2、表空间恢复
db2 “restore db <database name> tablespace (tablespacename)
online from <dir name> taken at <timestamp>”
例：db2 "restore db yhpzdb tablespace
(SYSCATSPACE,USERSPACE1) online from /home/db2inst1 taken at 20150203101401"
表空间恢复以后，会处于rollforward-pending 的状态，需要前滚到日志结尾。

db2 connect to yhpzdb
#SQL0290N Table space access is not allowed. SQLSTATE=55039
db2 rollforward db yhpzdb to end of logs and complete
3、日志恢复
db2 backup db yhpzdb online include logs
db2 restore db yhpzdb from /home/db2inst1 taken at
20150203161625 logtarget /yhpz/logs #通过logtarget 可以将日志恢复到指定的目录
db2 "rollforward db yhpzdb to end of logs and stop overflow log path (/yhpz/logs)"
4、前滚恢复
db2 backup db yhpzdb online to /home/db2inst1 include logs
db2 restore db yhpzdb from /home/db2inst1 taken at
20150204100634
db2 rollforward db yhpzdb to end of backup and complete
5、删除表恢复
drop table recovery 利用删除表恢复历史和表空间前滚机制(仅在归档模式下)分别获取删除表的定义和数据。

该机制要求表空间的drop table recovery 选项必须打开。

db2 "alter tablespace <tbspacename> dropped table recovery on"
例：db2 "create table tb8 (id int,name char(32))"
db2 "insert into tb8 values(1,'zhouyu')"
db2 "insert into tb8 values(2,'caocao')"
db2 "insert into tb8 values(3,'lusu')"
db2 "insert into tb8 values(4,'xiaoqiao')"
db2 backup db yhpzdb online #在线备份数据库
db2 connect to yhpzdb
db2 "drop table tb8"
db2 list history dropped table all for yhpzdb #历史查找删除表tb8的定义
db2 "restore db yhpzdb tablespace (USERSPACE1) taken at 20150204121545"
mkidr data
db2 "rollforward database yhpzdb to end of logs tablespace (USERSPACE1) recover dropped table 000000000000371700020117 to /home/db2inst1/data" # 前滚恢复表，将表数据导入到指定目录，0000......0117 是list
dropped table 中的backup id,每个backup id 对应一张删除表。

db2 connect to yhpzdb
db2 "CREATE TABLE "DB2INST1"."TB8" ( "ID" INTEGER ,
"NAME" CHAR(32) ) IN "USERSPACE1"" #通过历史
文件获取删除表的定义，将其复制重建。

cd /home/db2inst1/data
db2 "load from data of del insert into tb8 copy yes
to/home/db2inst1/data/NODE0000" #通过表空间前
滚获取删除表的数据，通过load将数据加载到重建的表中。

db2 "select * from tb8"
45、归档日志模式下的备份恢复
db2 backup db yhpzdb online
db2 "insert into sanguo values (1,'caocao')"
db2 "insert into sanguo values (2,'zhugeliang')"
date
db2 "insert into sanguo values (3,'zhouyu')"
db2 "insert into sanguo values (4,'lusu')"
date
db2 restore db yhpzdb taken at 20150204154551
db2 rollforward db yhpzdb to 2015-02-04-15.48.48.000000 using local time
db2 rollforward db yhpzdb stop
db2 "select * from sanguo" #当前滚恢复结束后，连接数据库，查看表数据，发现只有前2条数据。

一旦恢复完成，就会生成新的日志链，后2条记录将会永久丢失，再也无法找回。

以下我们来进行重新恢复实验。

db2 restore db yhpzdb taken at 20150204154551
db2 rollforward db yhpzdb to end of logs and complete
db2 connect to yhpzdb
db2 "select * from sanguo"
重新恢复，并前滚到日志结尾，观察结果，依然只有前2条记录，这就提示我们前滚操作的时间一定要谨慎，一旦完成前滚，之后的数据再也无法找回使用。

load 表状态
load 的copy选项：copy支持3种方式：copy no，copy yes，nonrecoverable。

Copy只适用于归档模式。

copy no 是默认方式，对于数据库可恢复来说，copy no会将load 表所属的表空间置于backup pending 状态，意思是提醒DBA要
在load后对表空间做备份，否则无法恢复。

目标表可读，但不能增删改。

例：db2 "create table sanguo (id int,name char(32))"
db2 update db cfg for yhpzdb using logarchmeth1 disk:/yhpz/logs db2 backup db yhpzdb
db2 connect to yhpzdb
db2 "load from sanguo.del of del insert into sanguo"
Sanguo数据可以查看，但无法增删改。

db2 "select * from sanguo"
db2 "update sanguo set name='liubei' where id =4"
#SQL0290N Table space access is not allowed. SQLSTATE=55039
db2 list tablespaces show detail #检查表空间，发现sanguo 处于backpending 状态。

db2 backup db sanguo #对整个数据库做备份，问题就解决了。

copy yes 选项会在load结束时，自动对表所属的表空间做一次备份，load结束后，表所在的表空间不会处于backup pending 状态，而为正常状态，但由于要备份，所需时间较长。

例：db2 connect to yhpzdb
db2 "load from sanguo.del of del insert into sanguo copy yes to
/home/db2inst1"
db2 "select * from sanguo"
ls
load 产生备份介质，其中备份第二个文件字段“4”表示load产生的备份。

这个备份将在数据库进行前滚恢复操作时，用于创建load操作对数据库的更改。

db2 restore db yhpzdb taken at 20150205104924
db2 rollforward db yhpzdb to end of logs and complete
db2 connect to yhpzdb
db2 "select * from sanguo"
当使用nonrecoverable时，表和空间都可以使用，但load之后，如果执行前滚命令恢复数据库，前滚操作将跳过load，将load 标记为无效，对该表的任何操作都不能进行。

此时只有删除表，重新构建。

例：db2 "load from sanguo.del of del insert into sanguo nonrecoverable"
db2 "select * from sanguo"
db2 restore db yhpzdb taken at 20150205104924
db2 rollforward db yhpzdb to end of logs and complete
db2 connect to yhpzdb
db2 "select * from sanguo"
注：在归档日志模式下，选择几种方法时，需要深入了解他们
的优缺点。

COPY NO会使表空间置于back pending ,在这个状态中，只能查询数据，而无法更改、删除，因此不建议在日常交易期间使用该方法；NONRECOVERABLE 选项使用更要小心，当前滚时，目标数据无法恢复，而必须删除。

对于有些系统，用户在做load业务时，应用程序必须停止，为了快速完成load，用户可以考虑选择使用NONRECOVERABLE，但在加载数据之后，最好在启动业务业务应用程序以后对数据进行在线备份，确保被加载的表可以被恢复；COPY YES 会导致加载的时间变长。