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
