[DB2使用小结

一、基本概念
也许很多人对oracle比较熟，但oracle毕竟以数据库为主打产品，而db2不一样，它有很多工具配套，如IBM的WebSphere MQ/Applicatioin Server等产品，也许一个项目采用哪个数据库需要根据不同的环境来选择而不是数据库的强大，因为对于大多数应用oracle，db2，sqlserver，sybase可能都适用。
1.实例和数据库
实例是内存和进程（处理数据，处理日志，处理系统．处理进程，分配系统资源（cpu,内存等等），管理系统资源（cpu,内存等等），备份，等等）．　数据库是一堆文件（日志文件数据库文件控制文件．参数文件密码文件等等）。oracle 一般情况　一个实例只能用来管理一个数据库，一对一关系．rac 是一个数据库可以被多个实例管理．（如果数据库是你身体，实例就是使用运动思考的大脑．没有大脑来管理，你就是一个死人，数据库就是一个死数据库，没有启动）。db2 不同一个实例可以管理多个数据库．当你创建一个实例，可以不建立任何数据库．具体你要用实例来管理那些数据库在你实例的参数和配置文件都记录．如果你有多个实例．你要是用某个实例　．可以set db2instance='实例名'(window)在你linux-unix系统下：一个操作系统用户对应一个实例．也只能对应一个实例（值得考究自己是这么理解的可能有错误） 如：db2icrt -u db2inst1 db2inst1用户必须是系统db2iadm组的．（db2 实例管理创建组）。
2.分区（节点）
分区是DB2物理上组织存储的机制，分区由从属于它的数据库对象所组成，它的实质在于物理的将数据分布到不同的文件、设备甚至是物理主机上，充分利用I/O和多CPU计算能力，从而提高存取性能；一个DB2数据库可以由一个分区组成，也可以由多个分区组成。
3.模式
模式是DB2逻辑上组织存储的机制，模式由它组织的数据库对象所组成，它的实质在于有效的组织数据、分割数据，规定了外部存取DB2数据的路径（模式名.表名）。分区和模式，是针对数据存储在不同级别上的管理机制，一个是在物理级别，一个是在逻辑级别。由前边的叙述，存在如下的情形：多个模式可能共用一个节点或节点组实现数据的物理存储；一个模式可能用到多个节点实现数据的物理存储。
4.表空间
表空间是物理存储空间的表述或命名，DB2中，先要建立节点组或节点，才能建立表空间，从这个角度看，节点（或分区）其实是对表空间的组织。Tablespace是一个物理对象，实际上就是VSAM数据集，存放数据的物理空间，一个表空间可以含有一个或多个数据集；一个表空间可以用来存放一个或多个表的数据，即一个Tablespace可以定义一个或多个Tables。
Simple tablespace：一个表空间中可以有多个表；表空间由pages组成，并且，每个page可以存放多个表的rows。
Segment tablespace：一个表空间中可以有多个表；表空间由segments组成，每个segments为固定的page数（必须为4的倍数个pages，且范围是4--64个pages），并且，每个segments只能存放专一地存放一个表的rows。
Partition tablespace：一个表空间中只能有一个表；表空间根据key的范围划分为多个partitions，每个partitions是一个无名称的分区index。并且，每个partitions都能由DB2 unitility或SQL语句同时处理。
二、基本管理
1.sql语句环境下使用临时表
-- 定义一个全局临时表SESSION.tmptb
DECLARE GLOBAL TEMPORARY TABLE SESSION.tmptb
(
TMP_id       VARCHAR(10),
TMP_name    VARCHAR(300)
)
WITH REPLACE
NOT LOGGED;
-- 插入数据到临时表
INSERT INTO  SESSION.tmptb
SELECT id,name FROM user where id like ‘0%’;
-- 查询临时表数据
SELECT * FROM SESSION.tmptb;
注：在SQL语句中，当SQL语句执行处于自动提交模式下时，执行后就COMMIT了，而一旦执行了COMMIT语句，临时表将从内存中清除。所以可能查询该临时表后可能无数据显示。那么自动提交模式怎样确定呢？在默认情况下，自动提交特性是打开的（-c）。这个选项指定每个语句是否自动提交或回滚。如果一个语句成功了，它就和它前面执行的关闭了自动提交（+c 或 -c-）的所有成功语句一起提交。但是，如果它失败了，它就和它前面执行的关闭了自动提交的所有成功语句一起回滚。如果这个语句关闭了自动提交，就必须显式地执行提交或回滚命令。
示例：
[maps@localhost ~]$ db2 +c "create table T(c1 int)"
DB20000I  The SQL command completed successfully.
[maps@localhost ~]$ db2 +c "select * from T"
C1
-----------
0 record(s) selected.
[maps@localhost ~]$ db2  rollback
DB20000I  The SQL command completed successfully.
[maps@localhost ~]$ db2 +c "select * from T"
SQL0204N  "MAPS.T" is an undefined name.  SQLSTATE=42704
2.存储过程环境下使用临时表
CREATE PROCEDURE SP_TEST_TMEP (  )
DYNAMIC RESULT SETS 1
P1: BEGIN
-- 定义一个全局临时表SESSION.TMPTB
DECLARE GLOBAL TEMPORARY TABLE SESSION. TMPTB
(
TMP_ID   VARCHAR(10),
TMP_NAME   VARCHAR(300)
)
WITH REPLACE       -- 如果存在此临时表，则替换
NOT LOGGED;    -- 不在日志里纪录
-- 插入数据到临时表
INSERT INTO  SESSION.TMPTB
SELECT ID,NAME FROM USER;
P2: BEGIN
-- 游标对客户机应用程序保持打开
DECLARE CUR_RES CURSOR WITH RETURN FOR
SELECT * FROM SESSION.TMPTB;
OPEN CUR_RES;
END P2;
END P1
注：在SQL过程中，临时表定义后，如果没有显式执行到COMMIT语句，则临时表一直存在。并且临时表支持INSERT INTO ... SELECT ... 的语句。
临时表总结：在DB2下，临时表的模式必须为SESSION，SESSION模式下表是一个内存表，这个SESSION是DB2特有的SCHEMA，SESSION对象的声明周期仅仅限于一次数据连接“会话”，一旦会话结束，SESSION对象就被从内存中清除了，这和JSP中的内置对象SESSION类似。
3.查看端口号
切换到db2inst1用户 $su - db2inst1
查找TCP/IP Service name $db2 get dbm cfg | grep -i service
通过上一句命令的输出（如db2c_db2inst1）在/etc/services文件中找对应的端口号 grep db2c_db2inst1 /etc/services
这是你将会看到这么一行从上一句的输出 db2c_db2inst1   50000/tcp
其中50000就是db2数据库所占用的端口号
4.远程访问
如果需要在本地应用程序中访问远程数据库服务器，只需要在本地应用程序环境下安装db2客户端后做如下操作就可以跟直接访问数据库服务器一样进行操作了：
编目一个TCP/IP节点:
db2 catalog tcpip node host191 remote 10.0.7.191 SERVER 60000
查看本地节点目录:
db2 list node directory
编目数据库:
db2 catalog  DATABASE cisdb AS cisdb191 AT NODE HOST191
查看系统数据库目录:
db2 list database directory
测试远程连接:
db2 connect to cisdb191 user db2 using passwd
5.常用命令
启动和停止实例：db2start/db2stop
查看表结构：db2 describe table user1.department
查看表索引：db2 describe indexes for table user1.department
显示当前用户所有表：list tables
列出所有的系统表：list tables for system
显示当前活动数据库：list active databases
查看命令选项：list command options
信息帮助 (? XXXnnnnn ) ：例:? SQL30081
SQL 帮助(说明 SQL 语句的语法) ：help statement 例如，help SELECT
SQLSTATE 帮助(说明 SQL 的状态和类别代码) ：? sqlstate 或 ? class-code    update dbm cfg using
将数据库管理器配置参数更新为值
三、备份和恢复
1.db2move备份和导入数据
db2move YOURDB  export
db2move NEWDB  load
2.备份和恢复数据库
备份：$DB2 BACKUP DB SAMPLES
除去数据库：再建立同名数据库时候，会提示错误，需要删除数据库
$db2 catalog db dbname on /opt
SQL1005N在本地数据库目录或系统数据库目录中已经存在数据库别名dbname
$db2 drop db dbname
DB20000I DROP DATABASE 命令成功完成
恢复：恢复到一个新数据库，恢复过程自动创建数据库
$DB2 RESTORE DB dbname INTO dbname WITHOUT ROLLING FORWARD WITHOUT PROMPTING
恢复成功，但是有错误   （57019）
$db2   connect   to   dbname   user   db2admin   using   xxxxxx
SQL1117N 由于ROLL-FORWARD PENDING，不能连接或激活数据库   dbname.SQLSTATE=57019
$db2   rollforward   db   dbname   to   end   of   logs
前滚状态
输入数据库别名=dbname
节点数已返回状态=1
节点号=0
前滚状态= DB   工作
下一个要读取的日志文件=S0000000.LOG
已处理的日志文件 =     -
上次提交的事务=2005-03-14-17.47.33.000000
DB20000I     ROLLFORWARD   命令成功完成。
$db2   connect   to   dbname   user   db2admin   using   xxxxx
SQL1117N 由于ROLL-FORWARD PENDING，不能连接或激活数据库dbname，SQLSTATE=57019
$db2   rollforward   db   dbname   complete
前滚状态
输入数据库别名=dbname
节点数已返回状态=1
节点号=0
前滚状态=未暂挂
下一个要读取的日志文件=
已处理的日志文件= -
上次提交的事务=2005-03-14-17.47.33.000000
DB20000I     ROLLFORWARD   命令成功完成。
$db2   connect   to   dbname   user   db2admin   using   xxxxxx
Database Connection Information
3.export/load备份恢复数据
export to tempfile of del select * from TABLENAME where not 清理条件；
load from tempfile of del modified by delprioritychar replace into TABLENAME nonrecoverable；
说明：
在不相关的数据表export数据时，可以采取并发的形式，以提高效率；
modified by delprioritychar防止数据库记录中存在换行符,导致数据无法装入的情况;
replace into对现数据库中的内容进行替换，即将现行的数据记录清理，替换为数据文件内容；
nonrecoverable无日志方式装入；
四、常用技巧
1.导出建库脚本
db2look -d  YOURDB  -a -e -x -o creatab.sql
2.取前Ｎ条数据
select * from tab_name where expression fetch first n rows only
3.关于日志
1)Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
这两项这样设，当一个事务结束后，日志会自动清，但是你设置的日志大小将占用相应的硬盘空间，总大小为单个日志大小×主日志文件数！一般够用！
db2diag.log：太大的话就删除或者移走，系统在需要的时候会自己建立新的。
2)系统日志（log），如果logretain参数是设定为off的，为循环日志，总的大小是不会增加的。如果设定为recovery，需要用db2 PRUNE LOGFILE PRIOR TO Sxxx.LOG 的指令来删除非活动的日志文件，具体编号可以从db2 get db cfg for xxx取得。
五、异常处理
1.数据库挂起
在进行一些数据库操作时可能因为种种原因发生中断，会使数据库暂挂 ：
db2 　list　 tablespaces   show   detail
状态更改对象标识 = 59
db2 select tabname,tableid from syscat.tables where tableid=59
查看是哪张表挂起，表名知道后到db2move.lst(在db2move  YOURDB  export的目录中)中找到相应的.ixf文件
db2 load from tab11.ixf of ixf terminate into db2admin.xxxxxxxxx
tab11.ixf对应的是xxxxxxxxx表，数据库会恢复正常，可再用db2 list tablespaces show detail查看
2.应用长时间未提交导致日志空间不可用
即在日志空间并未用尽的情况下，当某个占有最旧活动日志的应用长时间未作提交操作，阻止了日志的 LSN 的分配，造成日志空间无法使用，同样会引发这一日志满的报错。对于这种情况，可以提交该交易或利用 FORCE 命令来终止此应用程序，以便释放它所占用的日志空间，使 LSN 可以继续分配，空闲的日志空间可用。
首先检查 DB2 诊断日志文件 db2diag.log，在其中查找类似如下信息：
2003-01-16-02.53.54.935308 Instance:db2inst1 Node:016
PID:144252(db2agntp (SAMPLE) 16) Appid:*.*
data_protection sqlpgrsp Probe:50 Database:SAMPLE
Log Full -- active log held by appl. handle 787273
End this application by COMMIT, ROLLBACK or FORCE APPLICATION.
由此，可以找到最早持有日志空间的应用程序，其句柄为 787273。如果使用 DB2 的快照工具，通过从快照的输出中查找类似以下信息：
Appl id holding the oldest transaction = 787273
同样可以找到这个应用程序的句柄。这时使用以下命令可以在无需断开数据库其它应用程序的连接的情况下强行终止该应用程序：
db2 force application (787273)
DB20000I FORCE APPLICATION 命令成功完成。
DB21024I 该命令为异步的，可能不会立即生效。
根据提示，由于该命令是异步操作，可再次使用：
db2 list applications
验证应用是否已被真正停止，如果输出中已没有该应用，它所占有的日志空间会因应用程序被回滚而立即释放，而 DB2 日志因此重新可用。
3.日志满的解决办法
DB2 使用的活动日志的最大空间是由下面公式：
(logprimary + logsecond) * logfilsiz * 4096
计算出的大小来决定的（logprimary，logsecond，logfilsiz是数据库配置参数）。若该空间已全部被分配，而应用仍试图请求更多活动日志空间时，就会发生日志满的情况，此时，用户的更新、删除或插入操作都会使 DB2DIAG.LOG 中写入以下信息：
SQL0964C 数据库的事务日志已满。
DB2 活动日志满通常是由于存在大量未提交事务的数据，使得活动日志的空间不能及时释放，使新的事务无法申请到可用日志空间，而最终报出 SQL0964C 的错误所致。为使应用程序成功运行，而不是被回滚，通常会考虑根据情况选择增大以上公式中的某些数据库参数，以增大活动日志空间来解决这一问题。
db2 => get db cfg for testdatabase　查看数据库配置信息
db2 => update db cfg for testdatabase using logfilsiz 6000
db2 => update db cfg for testdatabase using logprimary 4
db2 => update db cfg for testdatabase using logsecond 25
六、资料推荐
1.DB2开发与应用社区：http://www.db2china.net/
2.DB2信息中心：http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
3.DB2管理资料：http://blog.csdn.net/gubaohua/archive/2005/07/01/409153.aspx
4.DB2数据库编程与开发：http://focus.it168.com/200809/db2develop/index.html
5.DB2新手入门：http://www-128.ibm.com/developerworks/cn/db2/newto/
6.DB2数据库管理开发性能优化：http://publish.itpub.net/lists/7231/0/7231.shtml