DB2 Express-C
快 速 入 门
源 于 社 区 贡 献 社 区
R A U L C H O N G, I A N H A K E S, R A V A H U J A 著
D R . A R V I N D K R I S H N A 序
第 二 版
2 DB2 Express-C 快速入门
第二版 (2008 年 4 月)
本书适用于 Linux®, 、UNIX®、Windows®上的 IBM® DB2® Express-C Version 9.5
© Copyright IBM Corporation, 2007, 2008. 版权所有.
目录
关于本书 ...................................................................................................................................8
声明......................................................................................................................................8
本书的读者对象....................................................................................................................8
本书的架构...........................................................................................................................8
一本属于社区的书 ................................................................................................................9
本书的贡献者 .......................................................................................................................9
致谢......................................................................................................................................9
序 .......................................................................................................................................10
PART I – 概览.........................................................................................................................11
第 1 章 – DB2 Express-C 是什么?..........................................................................................13
1.1 免费开发、部署和分发… 无限制!...............................................................................13
1.2 用户帮助和技术支持.....................................................................................................14
1.3 DB2 服务器 ..................................................................................................................14
1.4 DB2 客户端和驱动........................................................................................................14
1.5 应用程序开发的自由性 .................................................................................................15
1.6 DB2 版本号与 DB2 版本分类........................................................................................16
1.7 升级到其它的 DB2 版本................................................................................................16
1.8 DB2 Express-C 的维护.................................................................................................16
1.9 相关免费软件................................................................................................................17
1.9.1 IBM 数据工作室(Data Studio) ...........................................................................17
1.9.2 DB2 Net Search Extender.....................................................................................17
1.9.3 Starter Toolkit for DB2 on Rails.............................................................................17
1.9.4 Web 2.0 Starter Toolkit for DB2 ............................................................................17
1.9.5 WebSphere Application Server – Community Edition............................................18
第 2 章 – DB2 相关特性和产品 ................................................................................................19
2.1 DB2 Express-C 订购中包含的功能 ...............................................................................22
2.1.1 Fix packs 补丁包 ...................................................................................................22
2.1.2 高可用性灾难恢复(HADR) ................................................................................22
2.1.3 数据复制(Data Replication)...............................................................................22
2.2 DB2 Express-C 所不具备的功能...................................................................................23
2.2.1 数据库分区 ............................................................................................................23
2.2.2 连接集中器(Connection Concentrator ).............................................................23
2.2.3 Geodetic Extender ................................................................................................23
2.2.4 工作负载管理(Workload Management, WLM) .......................................................24
2.3 DB2 相关收费产品........................................................................................................24
2.3.1 DB2 连接(DB2 Connect)...................................................................................24
2.3.2 WebSphere Federation Server..............................................................................24
2.3.3 WebSphere Replication Server .............................................................................25
第 3 章 – 安装 DB2..................................................................................................................27
3.1 安装前提条件................................................................................................................27
3.2 操作系统中的安装权限 .................................................................................................27
3.3 安装向导.......................................................................................................................27
3.4 自动安装.......................................................................................................................31
实验 #1 安装 DB2 Express-C,创建 SAMPLE 数据库........................................................32
第 4 章 – DB2 的应用环境 .......................................................................................................35
实验 #2 - 创建一个新的数据库............................................................................................43
4 DB2 Express-C 快速入门
4.1 DB2 配置......................................................................................................................44
4.1.1 环境变量................................................................................................................44
4.1.2 数据库管理器配置文件(dbm cfg) ...........................................................................44
4.1.3 数据库配置文件(db cfg) ....................................................................................46
4.1.4 DB2 概要文件注册表 .............................................................................................47
4.2 DB2 管理服务器 ...........................................................................................................48
实验 #3 – 实例、数据库和配置管理....................................................................................49
第 5 章 – DB2 工具..................................................................................................................51
5.1 控制中心(Control Center).........................................................................................52
5.2 命令编辑器(Command Editor) .................................................................................55
5.3 SQL 帮助向导(SQL Assist Wizard ).........................................................................57
5.4 显示 SQL 按钮..............................................................................................................58
实验 #4 使用脚本填充 EXPRESS 数据库 ...........................................................................59
5.5 脚本..............................................................................................................................60
5.5.1 SQL 脚本...............................................................................................................60
5.5.2 操作系统(shell)脚本 ..........................................................................................61
实验 #5 为 EXPRESS 数据库创建一个安装脚本.................................................................62
5.6 任务中心(Task Center ) ...........................................................................................65
5.6.1 工具目录(Tools Catalog)数据库 ........................................................................65
5.7 日志(Journal)...........................................................................................................66
5.8 运行状况监视器(Health Monitor ).............................................................................67
5.8.1 运行状况中心(Health Center )...........................................................................68
PART II – DB2 Express-C 数据库管 ......................................................................................71
第 6 章 – DB2 体系结构...........................................................................................................73
6.1 DB2 进程模型...............................................................................................................73
6.2 DB2 内存模型...............................................................................................................74
6.3 DB2 存储模型...............................................................................................................75
6.3.1 数据页和扩展数据块 ..............................................................................................75
6.3.2 缓冲池 ...................................................................................................................76
6.3.3 表空间 ...................................................................................................................77
第 7 章 – DB2 客户端的连接....................................................................................................81
7.1 DB2 目录......................................................................................................................81
7.2 配置助手(Configuration Assistant )..........................................................................82
7.2.1 服务器端的安装要求 ..............................................................................................82
7.2.2 Setup required at the client 客户端的安装要求......................................................84
7.2.3 建立客户端与服务器端概要文件 ............................................................................87
实验 #6 使用配置助手 ........................................................................................................90
第 8 章 – 数据库对象 ...............................................................................................................93
8.1 模式..............................................................................................................................93
8.2 表 .................................................................................................................................93
8.2.1 数据类型................................................................................................................93
8.2.2 标识列 ...................................................................................................................96
8.2.3 序列对象................................................................................................................96
8.2.4 系统目录表 ............................................................................................................97
8.2.5 已声明临时表.........................................................................................................97
实验 #7 创建一个数据表.....................................................................................................99
目录 5
8.3 视图............................................................................................................................101
8.4 索引............................................................................................................................101
8.4.1 Design Advisor....................................................................................................101
8.5 参照完整性 .................................................................................................................102
第 9 章 – 数据迁移工具..........................................................................................................105
9.1 导出(EXPORT)工具...............................................................................................106
9.2 导入(IMPORT)工具................................................................................................106
9.3 使用 LOAD 来导入......................................................................................................107
9.4 db2move 工具 ...........................................................................................................108
9.5 db2look 工具 ..............................................................................................................109
实验 #8 导出 EXPRESS 数据库的 DDL............................................................................111
第 10 章 – 数据库安全 ...........................................................................................................115
10.1 认证..........................................................................................................................116
10.2 授权..........................................................................................................................116
10.3 DBADM 权限 ............................................................................................................118
10.4 PUBLIC 组 ...............................................................................................................119
10.5 GRANT 和 REVOKE 语句 ........................................................................................119
10.6 查看授权和特权........................................................................................................119
10.7 关于组特权...............................................................................................................121
实验 #9 授予和撤销用户的权限 ........................................................................................122
第 11 章 – 备份和恢复 ...........................................................................................................125
11.1 数据库的日志记录 ....................................................................................................125
11.2 日志的类型...............................................................................................................126
11.3 日志记录的类型.......................................................................................................126
11.3.1 循环日志记录 ....................................................................................................126
11.3.2 档案日志记录和日志保留...................................................................................127
11.4 从控制中心进行数据库日志记录 ...............................................................................127
11.5 日志记录的参数........................................................................................................129
11.6 数据库备份...............................................................................................................129
实验 #10 – 安排一个备份计划 ..........................................................................................131
11.7 数据库恢复...............................................................................................................133
11.7.1 恢复类型............................................................................................................133
11.7.2 数据库恢复........................................................................................................133
11.8 其他关于备份和恢复的操作 ......................................................................................134
第 12 章 – 维护任务...............................................................................................................135
12.1 重组(REORG)、运行统计(RUNSTATS)、重绑定(REBIND).......................135
12.1.1 重组(REORG)命令........................................................................................135
12.1.2 运行统计(RUNSTATS)命令 ..........................................................................136
12.1.3 绑定/重新绑定.................................................................................................136
12.1.4 在控制中心执行维护工作...................................................................................137
12.2 维护方式...................................................................................................................139
实验#11 – 配置自动维护 .................................................................................................141
第 13 章 – 并行与锁定 ...........................................................................................................143
13.1 事务(Transactions) ..............................................................................................143
13.2 并行(Concurrency) ..............................................................................................143
13.3 无并行控制导致的问题 .............................................................................................144
6 DB2 Express-C 快速入门
13.3.1 丢失更新(Lost update) ..................................................................................145
13.3.2 未落实的读(Uncommitted read) ....................................................................145
13.3.3 不可重复读(Non-repeatable read).................................................................146
13.3.4 幻象(Phantom read)......................................................................................146
13.4 隔离级别(Isolation Levels) ...................................................................................147
13.4.1 未落实的读 ........................................................................................................147
13.4.2 游标稳定性 ........................................................................................................147
13.4.3 读稳定性............................................................................................................148
13.4.4 可重复读............................................................................................................148
13.4.5 隔离级别对比.....................................................................................................148
13.4.6 设定隔离级别.....................................................................................................149
13.5 锁定升级...................................................................................................................150
13.6 锁定监视...................................................................................................................151
13.7 锁定等待...................................................................................................................151
13.8 死锁的引发与侦测.....................................................................................................152
13.9 并行与锁定的最佳实践:..........................................................................................153
PART III – DB2 Express-C 应用程序开发 ............................................................................155
第 14 章 –SQL PL 存储过程..................................................................................................157
14.1 IBM 数据工作室(Data Studio) ..............................................................................158
14.1.2 在 Data Studio 中创建一个存储过程 ..................................................................159
14.2 SQL PL 存储过程基础 ..............................................................................................161
14.2.1 存储过程的结构 .................................................................................................161
14.2.2 可选的存储过程属性..........................................................................................162
14.2.3 参数...................................................................................................................162
14.2.4 SQL PL 存储过程中的注释 ................................................................................163
14.2.5 复合语句............................................................................................................163
14.2.6 变量声明............................................................................................................163
14.2.7 赋值语句............................................................................................................164
14.3 游标..........................................................................................................................164
14.4 流控制 ......................................................................................................................164
14.5 调用存储过程 ...........................................................................................................165
14.6 错误和情况处理器 ....................................................................................................166
14.7 动态 SQL..................................................................................................................168
第 15 章 – 直接插入 SQL 过程语言、触发器、用户定义函数(UDF)..................................169
15.1 直接插入 SQL PL .....................................................................................................169
15.2 触发器(Trigger)....................................................................................................170
15.2.1 触发器的类型 ....................................................................................................170
实验 #12 从控制中心创建一个触发器 ...............................................................................174
15.3 用户定义函数 (UDF).................................................................................................177
15.3.1 标量函数(Scalar function) .............................................................................177
15.3.2 表函数(Table function)..................................................................................178
实验 #13 使用 IBM Data Studio 创建用户定义函数(UDF) ............................................179
第 16 章 – DB2 pureXML......................................................................................................181
16.1 在数据库中使用 XML................................................................................................181
16.2 XML 数据库..............................................................................................................182
16.2.1 启用 XML 的数据库............................................................................................182
16.2.2 原生 XML 数据库 ...............................................................................................182
目录 7
16.3 DB2 中的 XML..........................................................................................................183
16.3.1 pureXML 技术优势 ............................................................................................184
16.3.2 XPath 基础 ........................................................................................................185
16.3.3 XQuery 的定义 ..................................................................................................188
16.3.4 插入 XML 文档...................................................................................................189
16.3.5 查询 XML 数据...................................................................................................191
16.3.6 使用 SQL/XML 执行联合操作 ............................................................................196
16.3.7 使用 XQuery 执行联合操作................................................................................196
16.3.8 更新与删除操作.................................................................................................197
16.3.9 XML 索引 .........................................................................................................198
实验 #14 - SQL/XML 和 XQuery.......................................................................................200
第 17 章 – 使用 Java、PHP 和 Ruby 进行数据库应用开发 ...................................................201
17.1 Java 应用程序开发 ...................................................................................................201
17.1.1 JDBC 类型 2 驱动程序.......................................................................................201
17.1.2 JDBC 类型 4 驱动程序.......................................................................................202
17.2 PHP 应用程序开发 ...................................................................................................203
17.2.1 DB2 为 PHP 提供的连接选项.............................................................................203
17.2.2 Zend Core for IBM.............................................................................................204
17.3 Ruby on Rails 应用程序开发.....................................................................................206
17.3.1 Startup Toolkit for DB2 on Rails ........................................................................206
附录 A — 排除故障 ...............................................................................................................207
A.1 查找错误代码的更多信息 ...........................................................................................207
A.2 SQLCODE 与 SQLSTATE .........................................................................................208
A.3 DB2 管理通知日志 .....................................................................................................208
A.4 db2diag.log................................................................................................................209
A.5 CLI 追踪.....................................................................................................................209
A.6 DB2 缺陷与补丁.........................................................................................................209
参考资源 ...............................................................................................................................210
网站..................................................................................................................................210
书籍..................................................................................................................................211
关于本书
声明
© Copyright IBM Corporation 2007,2008
All Rights Reserved.
IBM Canada
8200 Warden Avenue
Markham, ON
L6G 1C7
Canada
未经上面提到的所有版权所有人事先同意,禁止以任何形式或任何手段对本文档或其中的任何部
分进行复印或复制,或者翻译成其他语言。
IBM 对此处的内容不作任何保证或陈述,并明确声明免除任何关于适销性和适用于某种特定用途
的暗含保证。IBM 对本文档中可能出现的错误不承担任何责任,包括(但不限于)翻译错误。本文档
中包含的信息可随时更改而不另行通知。IBM 保留进行任何更改的权利,而不负责通知任何人这些修
订或更改。IBM 不作出使此处包含的信息保持最新的承诺。
本文档中涉及非 IBM 产品的信息是从这些产品的供应商处获取的。IBM 没有对这些产品进行测
试,也无法确认其性能的精确性、兼容性或任何其他关于非 IBM 产品的声明。有关非 IBM 产品性能
的问题应当向这些产品的供应商提出。
IBM,IBM 徽标,AIX,DB2, DB2 Connect,DB2 Universal Database,i5/OS,Informix,
pureXML,Tivoli,WebSphere,z/OS 均为 International Business Machines Corporation 在美国和/
或其他国家或地区的商标或注册商标。
JavaTM 和所有基于 Java 的商标是 Sun Microsystems,Inc. 在美国和/或其他国家或地区的商
标。
的商标。
它们。
Microsoft®、Windows®和 Windows 徽标是 Microsoft Corporation 在美国和/或其他国家或地区
Linux® 是 Linus Torvalds 在美国和其他国家或地区的注册商标。
Unix®是 Open Group 在美国和其他国家或地区的注册商标。
其他公司、产品或服务名称可能是其他公司的商标或服务标记。
本出版物中所提到的 IBM 产品或服务并不暗示 IBM 将在所有 IBM 开展业务的国家或地区中提供
本书的读者对象
本书面向于那些从事或准备从事数据库相关工作的读者,比如数据库管理员(DBA)、数据库相
关的开发人员、咨询人员、软件架构设计人员、产品管理人员、决策人员以及学生。
本书的架构
Part I,概览和安装。这一部分解释什么是 DB2 Express-C,介绍 DB2 产品家族的不同产品和各
自的特性,并介绍 DB2 Express-C 的安装和数据库的创建,最后介绍 DB2 提供的各种工具。
Part II,阐述 DB2 的数据库管理,使您掌握关于 DB2 的环境、体系结构、远程连接、数据对
象、数据迁移(import/export/load)、数据库安全、备份和恢复、并行和锁定以及其它常见的维护任
务。
Part III,阐述 DB2 的应用开发。涵盖存储过程、用户定义函数、触发器、SQL/XML、XQuery
以及在使用 Java™, PHP 和 Ruby 开发过程中的 DB2 配置。
附录提供排除故障的有用信息。
许多章提供了实验,实验所需要的资料打包成压缩文件(expressc_book_quicklabs_9.5.zip)随
书发布,也可以从 IBM® DB2 Express-C 网站上获得:http://www.ibm.com/db2/express。
书中的内容也用作为 DB2 on Campus 项目的课程材料,并与www.channelDB2.com/oncampus
上的学习视频相一致。同时,本书可以作为 DB2 on Campus 的考试复习资料,DB2 on Campus 考