【免费】DB2，Oracle使用经验积累资源-CSDN文库

需积分: 0 83 浏览量 2008-03-27 11:52:21 上传评论收藏 721KB DOC 举报

资源详情

资源评论

资源推荐

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..............................................................................................................................................13
3  DB2 编程.............................................................................................................................................14
3.1  建存储过程时 CREATE 后一定不要用 TAB 键...........................................................................14
3.2  使用临时表..................................................................................................................................14
3.3  从数据表中取指定前几条记录..................................................................................................15
 但下面这种方式不允许.........................................................................................................................15
 选第一条记录的字段到一个变量以以下方式代替............................................................................15
3.4  游标的使用..................................................................................................................................15
 
注意
commit
和
rollback.................................................................................................................15
 
游标的两种定义方式
....................................................................................................................15
 一种为.....................................................................................................................................................15
 
修改游标的当前记录的方法
........................................................................................................16
3.5  类似 DECODE 的转码操作............................................................................................................16
3.6  类似 CHARINDEX 查找字符在字串中的位置..............................................................................17
3.7  类似 DATEDIF 计算两个日期的相差天数...................................................................................17
3.8  写 UDF 的例子.............................................................................................................................17
3.9  创建含 IDENTITY 值(即自动生成的 ID)的表..............................................................................17
  GENERATED ALWAYS AS IDENTITY...........................................................................................17
3.10  预防字段空值的处理................................................................................................................17
 FROM DEPARTMENT.......................................................................................................................18
3.11 取得处理的记录数.....................................................................................................................18
3.12  从存储过程返回结果集(游标)的用法......................................................................................18
  LANGUAGE SQL................................................................................................................................18
 P1: BEGIN.............................................................................................................................................18
 END P1...................................................................................................................................................18
- DB2 使用经验积累－牛新庄血与泪的教训
-1--

 2、建一 SP 调该 SP 且使用它的结果集..............................................................................................18
 CREATE PROCEDURE DB2INST1.PROC2 (.................................................................................19
  LANGUAGE SQL................................................................................................................................19
 P1: BEGIN.............................................................................................................................................19
 END P1...................................................................................................................................................19
 3、动态 SQL 写法.................................................................................................................................19
  DECLARE CURSOR C1 FOR STMT1;...........................................................................................19
  PREPARE STMT1 FROM.................................................................................................................19
  'ALLOCATE C2 CURSOR FOR RESULT SET ?';.........................................................................19
 4、注意：...............................................................................................................................................19
3.13  类型转换函数............................................................................................................................19
3.14  存储过程的互相调用................................................................................................................19
3.15  C 存储过程参数注意.................................................................................................................20
 DYNAMIC RESULT SETS 0..............................................................................................................20
 LANGUAGE C......................................................................................................................................20
 NO DBINFO..........................................................................................................................................20
 FENCED................................................................................................................................................20
 MODIFIES SQL DATA........................................................................................................................20
 EXTERNAL NAME 'PR_CLEAR_TASK_CTRL!PR_CLEAR_TASK_CTRL'@.......................20
3.16  存储过程 FENCE 及 UNFENCE.....................................................................................................20
3.17  SP 错误处理用法.......................................................................................................................20
 DB2 会自动发出一个 SQLWARNING。而在我们原来的处理中对于 SQLWARNING 都...........20
  IF V_STATUS <> 1 THEN..................................................................................................................21
  END IF;.................................................................................................................................................21
 END;.......................................................................................................................................................21
3.18  VALUES 的使用...........................................................................................................................21
3.19  给 SELECT 语句指定隔离级别...................................................................................................21
3.20  ATOMIC 及 NOT ATOMIC 区别......................................................................................................21
3.21  C 及 SQL 存储过程名称都要注意长度....................................................................................21
- DB2 使用经验积累－牛新庄血与泪的教训
-2--

 DYNAMIC RESULT SETS 0..............................................................................................................22
 LANGUAGE C......................................................................................................................................22
3.22  怎样获得自己的数据库连接句柄............................................................................................22
3.23  类似于 ORACLE 的 NAME PIPE................................................................................................22
3.24  类似于 ORACLE 的 TRUNCATE 清表但不记日志的做法....................................................22
3.25  用 CLI 编程批量的 INSERT..........................................................................................................23
  SQL_ATTR_AUTOCOMMIT,...........................................................................................................25
  SQL_ATTR_PARAMSET_SIZE ,......................................................................................................26
4  DB2 一些不好的限制.........................................................................................................................27
4.1  临时表不能建索引......................................................................................................................27
4.2  CURSOR 不能定义为 WITH UR(可以但…)....................................................................................27
4.3  CURSOR ORDER BY 以后不能 FOR UPDATE...................................................................................27
4.4  程序中间不能自由改变隔离级别..............................................................................................27
4.5  UPDATE 不能用一个表中的记录为条件修改另一个表中的记录。.........................................27
4.6  如果显示调用存储过程时传 NULL 值要注意............................................................................28
5   DB2 编程性能注意............................................................................................................................28
5.1  大数据的导表的使用(EXPORT,LOAD,IMPORT)(小心)..................................................................28
5.1.1  import
的用法
........................................................................................................................28
 (COLUMN 的分割符号改为;)..............................................................................................................28
 注意要加 SCHMA.................................................................................................................................28
5.1.2  
性能比较
...............................................................................................................................29
5.1.3  export
用法
............................................................................................................................29
5.2  SQL 语句尽量写复杂 SQL..........................................................................................................29
5.3  SQL SP 及 C SP 的选择...............................................................................................................29
5.4  查询的优化(HASH 及 RR_TO_RS)............................................................................................29
5.5  避免使用 COUNT(*) 及 EXISTS 的方法.........................................................................................30
 如果做这两中操作的目的是为.............................................................................................................30
 如果是没有记录选择到的话，DB2 会将 SQLCODE=100 和 SQLSTATE=’20000’.....................30
 程序可以创建 CONTINUE HANDLER FOR EXCEPTION...........................................................30
5.6  COMMIT 的次数要适当................................................................................................................30
5.7  INSERT 和 UPDATE 速度比较........................................................................................................30
5.8  使用临时表取代一条一条插入..................................................................................................31
5.9  循环次数很多时注意减少执行语句(附例子)............................................................................31
 CREATE PROCEDURE PR_TEST1(................................................................................................31
- DB2 使用经验积累－牛新庄血与泪的教训
-3--

 DOWORK:BEGIN...............................................................................................................................31
 PFETCH: WHILE V_NOBR_FLAG<100000...................................................................................31
 CREATE PROCEDURE PR_TEST2(................................................................................................32
 PFETCH: WHILE V_NOBR_FLAG<100000...................................................................................32
5.10  看程序执行时间及结果 DB2BATCH...........................................................................................33
5.11  看程序或语句具体的执行计划 SHELL（改写后的语句）......................................................33
5.12  两个表做 JOIN 的不同方式的区别............................................................................................33
5.12.1  not in
方式
...........................................................................................................................33
5.12.2  except
方式
..........................................................................................................................34
5.12.3  not exist
方式
.......................................................................................................................35
6  其他系统和 DB2 的交互....................................................................................................................36
6.1  DELPHI 中从 DB2 取 BIGINT 的数据...........................................................................................36
7  DB2 表及 SP 管理...............................................................................................................................36
7.1  权限管理......................................................................................................................................36
7.1.1  
数据库权限控制
...................................................................................................................36
7.1.2  schema
权限控制
..................................................................................................................36
7.1.3  tablespace
权限控制
.............................................................................................................36
7.1.4  table
权限控制
......................................................................................................................37
7.1.5  package
权限控制
.................................................................................................................37
 SYSCAT.PACKAGEAUTH.................................................................................................................37
7.2  建存储过程会占用很多的系统资源（特别是 IO）..................................................................37
7.3  看存储过程文本..........................................................................................................................37
7.4  看表结构......................................................................................................................................38
7.5  看表的索引信息..........................................................................................................................38
7.6  查看各表对 SP 的影响(被哪些 SP 使用).....................................................................................38
7.7  查看 SP 使用了哪些表.................................................................................................................38
7.8  查看 FUNCTION 被哪些 SP 使用...................................................................................................38
7.9  查 SP 的 ID 号...............................................................................................................................38
7.10  从 SP 的 ID 号查存储过程名称..................................................................................................39
7.11  创建及使用 SUMMARY TABLE....................................................................................................39
7.12  修改表结构................................................................................................................................39
7.13  给一个表改名............................................................................................................................40
7.14  得到一个表或库的相关脚本....................................................................................................40
7.15  在对表操作的性能下降后对表做整理....................................................................................40
7.16  查看语句的执行计划................................................................................................................41
7.17  查看 SP 的执行计划...................................................................................................................41
7.18  更改存储过程的隔离级别........................................................................................................42
7.19  取全部表的大小........................................................................................................................42
- DB2 使用经验积累－牛新庄血与泪的教训
-4--

 CREATE PROCEDURE PR_GETTBSIZE.......................................................................................42
  LANGUAGE SQL................................................................................................................................42
 PROC: BEGIN......................................................................................................................................42
  OPEN C1;.............................................................................................................................................42
  COMMIT;.............................................................................................................................................43
 END PROC............................................................................................................................................43
8  DB2 系统管理.....................................................................................................................................43
8.1  DB2 EE 及 WORKGROUP 版本的区别.....................................................................................43
8.2  怎样判断 DB2 实例的版本号和修补级别？.............................................................................43
 AIX：用 DUMP -H...............................................................................................................................44
 HP: CAT 命令查看文件信息................................................................................................................44
8.3   DB2 客户端安装时选择语言.....................................................................................................44
8.4  DB2 安装......................................................................................................................................45
8.4.1  AIX
中自动启动
db2.............................................................................................................45
8.4.2  AIX
中用户使用
db2
的环境
.................................................................................................47
 DOC_LANG=EN_US; EXPORT DOC_LANG.................................................................................47
8.4.3  
在
win98
下安装
db2
报
Jdbc
错误
.......................................................................................47
 REM [CD-ROM DRIVE].....................................................................................................................48
 REM [MISCELLANEOUS].................................................................................................................48
 REM [DISPLAY]..................................................................................................................................48
8.4.4  
将一台机器上的数据库复制到另外一台机器
...................................................................49
8.4.5  
在
WIN2000
下编译本地
sp
设置
.........................................................................................49
8.5  启动支持远程管理数据库服务（DB2ADMIN）.........................................................................49
8.6  安装另一个 INSTANCE 要注意的地方.........................................................................................49
8.6.1  
通讯配置
...............................................................................................................................49
8.6.2  
更改文件权限
.......................................................................................................................49
8.7  DB2 的 C 编译报没有 LICSENCE..................................................................................................50
8.8  DB2 的进程管理...........................................................................................................................50
8.9  创建 DATABASE.............................................................................................................................50
8.10  DATABASE 的备份.......................................................................................................................50
8.11  TABLESPACE................................................................................................................................51
8.11.1  
创建临时表空间
..................................................................................................................51
  PAGESIZE 16 K...................................................................................................................................51
- DB2 使用经验积累－牛新庄血与泪的教训
-5--