没有合适的资源?快使用搜索试试~ 我知道了~
牛新庄:DB2使用经验
需积分: 4 10 下载量 187 浏览量
2012-12-05
15:43:06
上传
评论 2
收藏 582KB DOC 举报
温馨提示
试读
64页
该文档包含了作者多年来的使用DB2的经验和学习方法,是提高使用DB2水平的良好文档。
资源推荐
资源详情
资源评论
1 前言......................................................................................................................................................12
2 DB2 专有名词解释.............................................................................................................................12
2.1 INSTANCE(实例)............................................................................................................................12
2.2 DB2 ADMINISTRATION SERVER(管理服务器)..............................................................................12
2.3 CONTAINER(容器).........................................................................................................................13
2.4 DRDA...........................................................................................................................................13
2.5 DARI.............................................................................................................................................13
2.6 SPM.............................................................................................................................................13
2.7 FCM..............................................................................................................................................13
2.8 ADSM..........................................................................................................................................13
2.9 DCE..............................................................................................................................................14
3 DB2 编程.............................................................................................................................................14
3.1 建存储过程时 CREATE 后一定不要用 TAB 键...........................................................................14
3.2 使用临时表..................................................................................................................................14
3.3 从数据表中取指定前几条记录..................................................................................................15
但下面这种方式不允许.........................................................................................................................15
选第一条记录的字段到一个变量以以下方式代替............................................................................15
3.4 游标的使用..................................................................................................................................15
注意
commit
和
rollback.................................................................................................................15
游标的两种定义方式
....................................................................................................................16
一种为.....................................................................................................................................................16
修改游标的当前记录的方法
........................................................................................................16
3.5 类似 DECODE 的转码操作............................................................................................................17
3.6 类似 CHARINDEX 查找字符在字串中的位置..............................................................................17
3.7 类似 DATEDIF 计算两个日期的相差天数...................................................................................17
3.8 写 UDF 的例子.............................................................................................................................17
3.9 创建含 IDENTITY 值(即自动生成的 ID)的表..............................................................................18
GENERATED ALWAYS AS IDENTITY...........................................................................................18
3.10 预防字段空值的处理................................................................................................................18
FROM DEPARTMENT.......................................................................................................................18
3.11 取得处理的记录数.....................................................................................................................18
3.12 从存储过程返回结果集(游标)的用法......................................................................................19
LANGUAGE SQL................................................................................................................................19
P1: BEGIN.............................................................................................................................................19
END P1...................................................................................................................................................19
- DB2 使用经验积累-牛新庄
-1--
2、建一 SP 调该 SP 且使用它的结果集..............................................................................................19
CREATE PROCEDURE DB2INST1.PROC2 (.................................................................................19
LANGUAGE SQL................................................................................................................................19
P1: BEGIN.............................................................................................................................................19
END P1...................................................................................................................................................20
3、动态 SQL 写法.................................................................................................................................20
DECLARE CURSOR C1 FOR STMT1;...........................................................................................20
PREPARE STMT1 FROM.................................................................................................................20
'ALLOCATE C2 CURSOR FOR RESULT SET ?';.........................................................................20
4、注意:...............................................................................................................................................20
3.13 类型转换函数............................................................................................................................20
3.14 存储过程的互相调用................................................................................................................20
3.15 C 存储过程参数注意.................................................................................................................20
DYNAMIC RESULT SETS 0..............................................................................................................20
LANGUAGE C......................................................................................................................................20
NO DBINFO..........................................................................................................................................21
FENCED................................................................................................................................................21
MODIFIES SQL DATA........................................................................................................................21
EXTERNAL NAME 'PR_CLEAR_TASK_CTRL!PR_CLEAR_TASK_CTRL'@.......................21
3.16 存储过程 FENCE 及 UNFENCE.....................................................................................................21
3.17 SP 错误处理用法.......................................................................................................................21
DB2 会自动发出一个 SQLWARNING。而在我们原来的处理中对于 SQLWARNING 都...........21
IF V_STATUS <> 1 THEN..................................................................................................................21
END IF;.................................................................................................................................................21
END;.......................................................................................................................................................21
3.18 VALUES 的使用...........................................................................................................................22
3.19 给 SELECT 语句指定隔离级别...................................................................................................22
3.20 ATOMIC 及 NOT ATOMIC 区别......................................................................................................22
3.21 C 及 SQL 存储过程名称都要注意长度....................................................................................22
- DB2 使用经验积累-牛新庄
-2--
DYNAMIC RESULT SETS 0..............................................................................................................23
LANGUAGE C......................................................................................................................................23
3.22 怎样获得自己的数据库连接句柄............................................................................................23
3.23 类似于 ORACLE 的 NAME PIPE................................................................................................23
3.24 类似于 ORACLE 的 TRUNCATE 清表但不记日志的做法....................................................23
3.25 用 CLI 编程批量的 INSERT..........................................................................................................24
SQL_ATTR_AUTOCOMMIT,...........................................................................................................26
SQL_ATTR_PARAMSET_SIZE ,......................................................................................................27
4 DB2 一些不好的限制.........................................................................................................................28
4.1 临时表不能建索引......................................................................................................................28
4.2 CURSOR 不能定义为 WITH UR(可以但…)....................................................................................28
4.3 CURSOR ORDER BY 以后不能 FOR UPDATE...................................................................................28
4.4 程序中间不能自由改变隔离级别..............................................................................................29
4.5 UPDATE 不能用一个表中的记录为条件修改另一个表中的记录。.........................................29
4.6 如果显示调用存储过程时传 NULL 值要注意............................................................................29
5 DB2 编程性能注意............................................................................................................................29
5.1 大数据的导表的使用(EXPORT,LOAD,IMPORT)(小心)..................................................................29
5.1.1 import
的用法
........................................................................................................................29
(COLUMN 的分割符号改为;)..............................................................................................................29
注意要加 SCHMA.................................................................................................................................30
5.1.2
性能比较
...............................................................................................................................30
5.1.3 export
用法
............................................................................................................................30
5.2 SQL 语句尽量写复杂 SQL..........................................................................................................30
5.3 SQL SP 及 C SP 的选择...............................................................................................................31
5.4 查询的优化(HASH 及 RR_TO_RS)............................................................................................31
5.5 避免使用 COUNT(*) 及 EXISTS 的方法.........................................................................................31
如果做这两中操作的目的是为.............................................................................................................31
如果是没有记录选择到的话,DB2 会将 SQLCODE=100 和 SQLSTATE=’20000’.....................31
程序可以创建 CONTINUE HANDLER FOR EXCEPTION...........................................................31
5.6 COMMIT 的次数要适当................................................................................................................32
5.7 INSERT 和 UPDATE 速度比较........................................................................................................32
5.8 使用临时表取代一条一条插入..................................................................................................32
5.9 循环次数很多时注意减少执行语句(附例子)............................................................................33
CREATE PROCEDURE PR_TEST1(................................................................................................33
- DB2 使用经验积累-牛新庄
-3--
DOWORK:BEGIN...............................................................................................................................33
PFETCH: WHILE V_NOBR_FLAG<100000...................................................................................33
CREATE PROCEDURE PR_TEST2(................................................................................................33
PFETCH: WHILE V_NOBR_FLAG<100000...................................................................................34
5.10 看程序执行时间及结果 DB2BATCH...........................................................................................34
5.11 看程序或语句具体的执行计划 SHELL(改写后的语句)......................................................35
5.12 两个表做 JOIN 的不同方式的区别............................................................................................35
5.12.1 not in
方式
...........................................................................................................................35
5.12.2 except
方式
..........................................................................................................................36
5.12.3 not exist
方式
.......................................................................................................................37
6 其他系统和 DB2 的交互....................................................................................................................38
6.1 DELPHI 中从 DB2 取 BIGINT 的数据...........................................................................................38
7 DB2 表及 SP 管理...............................................................................................................................38
7.1 权限管理......................................................................................................................................38
7.1.1
数据库权限控制
...................................................................................................................38
7.1.2 schema
权限控制
..................................................................................................................38
7.1.3 tablespace
权限控制
.............................................................................................................39
7.1.4 table
权限控制
......................................................................................................................39
7.1.5 package
权限控制
.................................................................................................................39
SYSCAT.PACKAGEAUTH.................................................................................................................39
7.2 建存储过程会占用很多的系统资源(特别是 IO)..................................................................40
7.3 看存储过程文本..........................................................................................................................40
7.4 看表结构......................................................................................................................................40
7.5 看表的索引信息..........................................................................................................................40
7.6 查看各表对 SP 的影响(被哪些 SP 使用).....................................................................................40
7.7 查看 SP 使用了哪些表.................................................................................................................40
7.8 查看 FUNCTION 被哪些 SP 使用...................................................................................................41
7.9 查 SP 的 ID 号...............................................................................................................................41
7.10 从 SP 的 ID 号查存储过程名称..................................................................................................41
7.11 创建及使用 SUMMARY TABLE....................................................................................................41
7.12 修改表结构................................................................................................................................42
7.13 给一个表改名............................................................................................................................42
7.14 得到一个表或库的相关脚本....................................................................................................42
7.15 在对表操作的性能下降后对表做整理....................................................................................43
7.16 查看语句的执行计划................................................................................................................44
7.17 查看 SP 的执行计划...................................................................................................................44
7.18 更改存储过程的隔离级别........................................................................................................44
7.19 取全部表的大小........................................................................................................................45
- DB2 使用经验积累-牛新庄
-4--
CREATE PROCEDURE PR_GETTBSIZE.......................................................................................45
LANGUAGE SQL................................................................................................................................45
PROC: BEGIN......................................................................................................................................45
OPEN C1;.............................................................................................................................................45
COMMIT;.............................................................................................................................................46
END PROC............................................................................................................................................46
8 DB2 系统管理.....................................................................................................................................46
8.1 DB2 EE 及 WORKGROUP 版本的区别.....................................................................................46
8.2 怎样判断 DB2 实例的版本号和修补级别?.............................................................................46
AIX:用 DUMP -H...............................................................................................................................47
HP: CAT 命令查看文件信息................................................................................................................47
8.3 DB2 客户端安装时选择语言.....................................................................................................47
8.4 DB2 安装......................................................................................................................................48
8.4.1 AIX
中自动启动
db2.............................................................................................................48
8.4.2 AIX
中用户使用
db2
的环境
.................................................................................................50
DOC_LANG=EN_US; EXPORT DOC_LANG.................................................................................50
8.4.3
在
win98
下安装
db2
报
Jdbc
错误
.......................................................................................51
REM [CD-ROM DRIVE].....................................................................................................................51
REM [MISCELLANEOUS].................................................................................................................51
REM [DISPLAY]..................................................................................................................................51
8.4.4
将一台机器上的数据库复制到另外一台机器
...................................................................52
8.4.5
在
WIN2000
下编译本地
sp
设置
.........................................................................................52
8.5 启动支持远程管理数据库服务(DB2ADMIN).........................................................................52
8.6 安装另一个 INSTANCE 要注意的地方.........................................................................................52
8.6.1
通讯配置
...............................................................................................................................52
8.6.2
更改文件权限
.......................................................................................................................53
8.7 DB2 的 C 编译报没有 LICSENCE..................................................................................................53
8.8 DB2 的进程管理...........................................................................................................................53
8.9 创建 DATABASE.............................................................................................................................54
8.10 DATABASE 的备份.......................................................................................................................54
8.11 TABLESPACE................................................................................................................................54
8.11.1
创建临时表空间
..................................................................................................................54
PAGESIZE 16 K...................................................................................................................................54
- DB2 使用经验积累-牛新庄
-5--
剩余63页未读,继续阅读
资源评论
fwt_1
- 粉丝: 0
- 资源: 9
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功