DB2 重点解析 —— DBA 篇，第 3 章
DB2 中的数据对象
3.1 DB2 中的系统结构
3.2 系统参数
3.3 实例
3.4 数据库
3.5 缓冲池
3.6 表空间
3.7 模式
3.8 表
3.9 视图
3.10 索引
3.11 数据类型
3.12 约束
3.13 序列
3.14 存储过程
3.15 触发器
3.16 用户自定义函数
3.17 专用寄存器
3.18 本章小结
3.19 习题
参考资料
作者简介
对本文的评价
2009 年 12 月 17 日
DB2 是 IBM 公司开发的关系型数据库管理系统产品。本书从初学者学习的角度出发，在帮助读者建立数据库基本概念的基础上，介绍了 DB2 通用数据库及其典型应用，带领读者完成 DB2 数据库的操作和应用之旅。本章作者将为我们介绍 DB2 数据库的对象创建及其管理。
在此我们推出了本书的 前言、第 1 章、第 2 章和第 3 章供大家在线浏览。更多推荐书籍请访问 developerWorks 图书频道。
DB2 作为一个成熟的关系数据库管理系统，在传统的关系数据库理论的基础上，提供了更加复杂的层次结构，每个层次中都含有若干种对象，在各个层次都提供了增强的管理功能，从而使 DB2 能够适用于各个领域。
本章将结合大量实例对 DB2 中各种数据对象进行详细讨论。
 |
| |||||||
本章内容
- DB2 的系统结构
- DB2 各个级别的数据对象
难点与重点
- DB2 的系统结构
- 系统结构和配置参数的层次
- 实例的管理
- 实例的切换方法，启动与停止
- 数据库的管理
- 数据库的创建路径
- 缓冲池与表空间
- 二者与性能相关的设置
- 数据库对象
- 表、视图、索引的管理
- DB2 开发相关对象
- 存储过程的创建方法
- 触发器的含义与创建方法
- 用户自定义函数的创建方法
在 DB2 数据库服务器中，从逻辑的角度，分成若干个层次，详见图 3.1。
图 3.1 DB2 中的体系结构
其中，系统（System）代表主机，是最高的层次，在这个层次可以配置对该主机上所有实例和数据库都产生影响的系统参数。实例是一个逻辑上的数据库管理环境，可以独立管理和控制分配给它的资源。每个实例当中可以创建多个数据库，每个数据库中可以包含多个表空间，表空间中可以包含表、索引、大对象等数据库对象。实例和数据库都拥有自己的配置参数，通过设置它们，可以实现对实例和数据库的定制化管理。
在后面的几个小节中，我们将按照从高到低的顺序，对 DB2 中各个级别数据对象的概念和管理方法进行简单的介绍。
本章中只介绍各个对象的最基本和常用的操作，完整的语法或者特殊领域的操作方法还需要读者参考 IBM 的红皮书和 DB2 信息中心。
本章中的示例如果是 Windows 环境下的，将采用中文的 DB2 进行讲解，便于初学者熟悉 DB2 产品，如果是 UNIX 或 Linux 环境下的，将采用英文的 DB2，这样更接近于真实的生产环境。
 |
|
在系统级别，系统环境变量和注册表变量能够影响整个 DB2 的环境。在 Windows 环境下，使用 SET 命令设置环境变量，或者直接在图形界面“系统属性”中进行设置，如图 3.2 和图 3.3 所示。
图 3.2 系统属性对话框
图 3.3 环境变量对话框
在 UNIX 和 Linux 环境下，使用 export 命令设置环境变量。
除了环境变量，还可以通过设置 DB2 的全局注册表变量来对系统环境进行修改，查看全局注册表变量的命令为：
| db2set-g |
重新设置注册表变量的命令为：
| db2set 注册表变量名 = 新值 |
在系统级别，还记录了 DB2 的编目信息，其中记录了当前系统有哪些实例、远程实例、数据库等信息。
例 3 ‑1 编目数据库。
在 Windows 环境下第一次安装完 DB2 之后，创建了 SAMPLE 样本数据库，后来因为某种原因把 DB2 卸载后进行了重新安装，再次创建样本数据库时，系统提示：SAMPLE 数据库已经存在，无法创建，但是到控制中心又看不到 SAMPLE 数据库，这是为什么？如何解决？
答：数据库和 DB2 软件是两个不同的概念，DB2 是 IBM 公司生产的一种数据库管理系统软件产品，通过它可以在硬盘上创建存储和管理数据的数据库，数据库相当于一个容器，而数据库管理系统是管理这个容器的软件。
在 DB2 中创建数据库时，会自动在系统目录中记录相应的信息，这个过程叫做编目。当 DB2 卸载时，系统目录也被删除，但是原来创建的 SAMPLE 数据库并没有被一起删除，仍然存放在硬盘上，当 DB2 重新安装之后，再次创建 SAMPLE 数据库时会与硬盘上现有的数据库重名产生错误，但重新生成的系统目录中已经没有 SAMPLE 数据库的信息了，所以在控制中心中无法看到 SAMPLE 数据库。
要想解决这个问题，只要把 SAMPLE 数据库的信息重新写入到系统目录中即可，在命令行处理器中使用如下的数据库编目命令：
| CATALOG DATABASE sample |
CATALOG 命令还可以编目节点（CATALOG LOCAL NODE 或 CATALOG TCPIP NODE），以实现对本地实例或远程实例的连接，从而进行管理。在 3.4.4 小节会进行简单的介绍，更详细的命令语法请参见信息中心。
|
|
实例（Instance）是逻辑数据库管理器环境，可以在实例中创建数据库、对数据库进行编目（参见 3.4.4 小节）和设置配置参数。根据需要，可以在同一台物理服务器上创建多个实例，每个实例拥有唯一的数据库服务器环境。通常情况下，一套系统在研发阶段处于一个实例中，在测试阶段使用另一个实例，这样开发和测试可以隔离开，相互不产生干扰。当系统可以正式发布时，直接从测试的实例进行升迁即可。
使用实例的另外一个好处是可以从实例和数据库两个层次进行权限管理。通常 DBA 都拥有对数据库很高的权限，但是当系统正式上线之后，考虑到某些业务的保密性，进行数据库维护的 DBA 不应该看到数据库中具体的数据。在 DB2 中，给这样的 DBA 分配 SYSCTRL 权限（实例级别的权限），而不分配 DBADM 权限（数据库级别的权限）（关于权限的内容参见第 5 章），就可以实现让该 DBA 对数据库进行正常的维护，而无法看到数据库内部的数据。
实例是一个逻辑环境，每个实例对应于一个进程，每个实例都拥有独立的系统资源。
DB2 在安装的时候会创建一个默认的实例，在 Windows 环境下为 DB2，在 UNIX 或 Linux 环境下为 db2inst1。
要创建实例必须拥有相应的权限，Windows 中属于 Administrators 组的用户，UNIX/Linux 中具有 root 权限的用户，可以创建新的实例。
创建实例的系统级命令为：
| db2icrt < 实例名 > |
创建一个新的实例之后，在系统中就会产生对应的目录和进程。需要注意的是，在 UNIX 或 Linux 环境下，还需要提供受防护的用户标识（即 UNIX 和 Linux 识别的用户 ID），具体命令格式为：
| db2icrt – u < 用户标识 > < 实例名 > |
删除实例的系统命令为：
| db2idrop < 实例名 > |
列示实例的系统级命令为：
| db2ilist |
可以使用 DB2 命令：
| get instance |
来查看当前实例。其实在 UNIX 和 Linux 环境下实例就是当前用户。
当系统中包含多个实例时，可以在不同的实例之间进行切换。
Windows 环境下，使用系统级命令：
| set DB2INSTANCE=< 实例名 > |
来切换实例。需要注意的是，在当前命令窗口中该命令不会生效，要先执行 exit 退出命令窗口，重新打开一个命令窗口执行该命令。还可以直接在控制面板中修改环境变量 DB2INSTANCE 的值。
在 UNIX 或 Linux 环境下，实例对应于一个用户，只要切换到该用户也就切换到了该实例中：
| su - < 实例名 > |
例 3‑2 在 Linux 中创建一个名为 newinst1 的新实例。
创建新用户
使用 useradd 命令创建新用户，使用 passwd 命令修改密码，如图 3.4 所示。
图 3.4 新建用户并修改密码
切换到 DB2 默认的实例 db2inst1 中：
| su – db2inst1 |
然后运行 db2level 命令，可以得到 DB2 的安装目录，本例中为 /opt/ibm/db2/V9.5。
切换到 root 账户下：
| su – root |
进入到安装目录中的 instance 子目录中：
| cd /opt/ibm/db2/V9.5/instance |
运行 db2icrt 命令创建新的实例：
| ./db2icrt – u newinst1 newinst1 |
切换回 db2inst1 用户，运行 db2ilist 命令列示所有实例：
| su – db2inst1 db2ilist |
可以看到新建的 newinst1 实例。
有两点需要注意，一是 Linux 下用户名必须全部小写，二是 DB2 的实例名长度不能超过 8 位。
每个实例对应于一个进程，默认实例会自动启动，而在安装完成之后新建的实例不会自动启动。要启动一个实例，需要切换到该实例中，然后运行如下系统级命令：
| db2start |
要停止当前实例，使用系统级命令：
| db2stop |
如果想让一个实例自动启动（即让对应的进程自动执行）或者取消自动启动，在 Windows 环境中，可以通过在服务面板中进行设置，在 UNIX 或 Linux 环境中，可以使用如下系统级命令自动启动一个实例：
| db2iauto – on < 实例名 > |
使用如下系统级命令取消一个实例的自动启动：
| db2iauto – off < 实例名 > |
如果要管理远程的实例，需要先进行实例的附接（ATTACH），相应的语法为：
| attach to < 实例名 > user < 用户名 > using < 密码 > |
断开与远程实例的连接使用如下命令：
| detach |
关于实例附接后进行的详细管理操作，参见 3.4.4 小节。
DB2 中有一个特殊的实例，叫做 DB2 管理服务器（DB2 Administration Server，简称 DAS）。它能够调度执行在任务中心中创建的任务。DB2 创建的各种任务保存在一个称为“工具目录数据库”的特定数据库中，这些任务可以在任务中心或者控制中心中创建，然后由 DAS 进行调度。
DAS 在 Windows 中对应于控制面板中的“DB2DAS00”服务，在 UNIX 或 Linux 中，安装时会创建一个 dasadm1 用户，就是 DAS。
启动和停止 DAS 服务分别使用如下系统级命令：
| db2admin start db2admin stop |
通过设置各种数据库管理器参数，可以实现对整个实例环境都有效的配置，使用命令 db2 get dbm cfg，可以得到数据库管理器的配置参数：
数据库管理器配置
| 节点类型 = 带有本地和远程客户机的数据库服务器数据库管理器配置发行版级别 = 0x0c00 最大打开文件数 (MAXTOTFILOP) = 16000 CPU 速度（毫秒 / 指令） (CPUSPEED)= 2.597893e-007 最大并发活动数据库数 (NUMDB) = 8 联合数据库系统支持 (FEDERATED) = NO 事务处理器监视器名 (TP_MON_NAME) = 缺省对方付费账户 (DFT_ACCOUNT_STR) = Java Development Kit 安装路径（JDK_PATH）= D:PROGRA~1IBMSQLLIBjavajdk 诊断错误捕获级别 (DIAGLEVEL) = 3 通知级别 (NOTIFYLEVEL) = 3 诊断数据目录路径 (DIAGPATH) = 缺省数据库监视开关 缓冲池 (DFT_MON_BUFPOOL) = OFF 锁定 (DFT_MON_LOCK) = OFF 排序 (DFT_MON_SORT) = OFF 语句 (DFT_MON_STMT) = OFF 表 (DFT_MON_TABLE) = OFF 时间戳记 (DFT_MON_TIMESTAMP) = ON 工作单元 (DFT_MON_UOW) = OFF 监视实例和数据库的运行状况 (HEALTH_MON) = ON SYSADM 组名 (SYSADM_GROUP) = SYSCTRL 组名 (SYSCTRL_GROUP) = SYSMAINT 组名 (SYSMAINT_GROUP) = SYSMON 组名 (SYSMON_GROUP) = 客户机用户标识 - 密码插件 (CLNT_PW_PLUGIN) = 客户机 Kerberos 插件 (CLNT_KRB_PLUGIN) = IBMkrb5 组插件 (GROUP_PLUGIN) = 本地授权的 GSS 插件 (LOCAL_GSSPLUGIN) = 服务器插件方式 (SRV_PLUGIN_MODE)= UNFENCED GSS 插件的服务器列表 (SRVCON_GSSPLUGIN_LIST) = 服务器用户标识 - 密码插件 (SRVCON_PW_PLUGIN) = 服务器连接认证 (SRVCON_AUTH)= NOT_SPECIFIED 集群管理器 (CLUSTER_MGR) = 数据库管理器认证 (AUTHENTICATION) = SERVER 没有权限就允许编目 (CATALOG_NOAUTH) = NO 信赖所有客户机 (TRUST_ALLCLNTS) = YES 可信的客户机认证 (TRUST_CLNTAUTH) = CLIENT 绕过联合认证 (FED_NOAUTH) = NO 缺省数据库路径 (DFTDBPATH) = D: 数据库监视器堆大小（4KB） (MON_HEAP_SZ) = AUTOMATIC “Java 虚拟机”堆大小（4KB） (JAVA_HEAP_SZ) = 2048 审计缓冲区大小（4KB） (AUDIT_BUF_SZ) = 0 实例共享内存（4KB）的大小 (INSTANCE_MEMORY) = AUTOMATIC 备份缓冲区缺省大小（4KB） (BACKBUFSZ) = 1024 复原缓冲区缺省大小（4KB） (RESTBUFSZ) = 1024 代理程序的堆栈大小 (AGENT_STACK_SZ) = 16 最小已落实专用内存（4KB） (MIN_PRIV_MEM) = 32 专用内存阈值（4KB） (PRIV_MEM_THRESH) = 20000 排序堆阈值（4KB） (SHEAPTHRES) = 0 目录高速缓存支持 (DIR_CACHE) = YES 应用程序支持层堆大小（4KB） (ASLHEAPSZ) = 15 最大请求者 I/O 块大小（以字节计） (RQRIOBLK) = 32767 查询堆大小（4KB） (QUERY_HEAP_SZ) = 1000 已调速实用程序对工作负载的影响 (UTIL_IMPACT_LIM) = 10 代理程序的优先级 (AGENTPRI) = SYSTEM 代理程序池大小 (NUM_POOLAGENTS) = AUTOMATIC 池中的初始代理程序数 (NUM_INITAGENTS) = 0 最大协调代理程序数 (MAX_COORDAGENTS) = AUTOMATIC 最大客户机连接数 (MAX_CONNECTIONS) = AUTOMATIC 保留受防护的进程 (KEEPFENCED) = YES 合用受防护的进程的数目 (FENCED_POOL) = AUTOMATIC 受防护的进程的初始数目 (NUM_INITFENCED) = 0 索引重新创建时间和重做索引构建 (INDEXREC) = RESTART 事务管理器数据库名称 (TM_DATABASE) = 1ST_CONN 事务再同步时间间隔（秒） (RESYNC_INTERVAL) = 180 SPM 名称 (SPM_NAME) = GREENWI1 SPM 日志大小 (SPM_LOG_FILE_SZ) = 256 SPM 再同步代理程序限制 (SPM_MAX_RESYNC) = 20 SPM 日志路径 (SPM_LOG_PATH) = NetBIOS 工作站名 (NNAME) = TCP/IP 服务名称 (SVCENAME) = 发现方式 (DISCOVER) = SEARCH 发现服务器实例 (DISCOVER_INST) = ENABLE 最大查询并行度 (MAX_QUERYDEGREE) = ANY 启用分区内并行性 (INTRA_PARALLEL) = NO 内部通信缓冲区数（4KB） (FCM_NUM_BUFFERS)=AUTOMATIC 内部通信信道数 (FCM_NUM_CHANNELS)=AUTOMATIC db2start/db2stop 超时（分钟） (START_STOP_TIME) = 10 |
针对每个参数的详细解释，请参见 IBM 的官方手册。
在控制中心中，展开到实例级别，右键弹出菜单中选择“配置参数…”，可以通过图形界面查看和修改数据库管理器的配置参数，如图 3.5 所示。
图 3.5 数据库管理器配置参数
实例级别的常用命令如表 3.1 所示。
表 3.1 实例级别常用命令
| 命令名称 | 描述 |
|---|---|
| GET DBM CFG [SHOW DETAIL] | 返回数据库管理器的参数设置 |
| UPDATE DBM CFG USING < 参数 > < 值 > | 将数据库管理器的某个参数设置为新的值 |
| db2icrt < 实例名 > | 创建实例 |
| db2idrop < 实例名 > | 删除实例 |
| db2ilist | 列示实例 |
| GET INSTANCE | Windows 中获取当前实例名称 |
| set DB2INSTANCE=< 实例名 > | Windows 中切换实例 |
| db2start | 启动当前实例 |
| db2stop | 停止当前实例 |
| db2iauto -<on|off> < 实例名 > | UNIX 或 Linux 下设置某个实例是否自动启动 |
| ATTACH TO < 实例名 > [USER < 用户名 >] [USING < 密码 >] | 附接到指定的实例进行远程操作 |
| DETACH | 断开与当前实例的附接 |
| LIST ACTIVE DATABASES | 列出活动的数据库和连接数量 |
| LIST APPLICATION [SHOW DETAIL] | 返回当前连接的应用程序的信息 |
| FORCE APPLICATION[S] ALL | 断开与所有应用程序的连接 |
| FORCE APPLICATION (< 句柄 1>[,< 句柄 2>, … ,< 句柄 n>]) | 断开指定句柄的应用程序的连接 |
| db2admin start | 启动管理服务器实例 |
| db2admin stop | 停止管理服务器实例 |
| CATALOG LOCAL NODE < 节点名 > INSTANCE < 实例名 > | 编目实例作为本地节点 |
| CATALOG TCPIP NODE < 节点名 > REMOTE < 主机名 > SERVER < 端口号 > | 编目远程实例作为本地节点 |
注：表格中大写的命令表示该命令是 DB2 级的，可以直接在命令行处理器中执行，小写的命令表示该命令是系统级的，需要在命令窗口中执行，而且在 UNIX 或 Linux 下必须小写，而在 Windows 下不区分大小写。
|
|
数据库（Database）是有组织的、可共享的数据集合。DB2 是关系型数据库，基本的数据结构是二维表格，二维表由一组已定义的列和任意数目的行组成，表与表之间可以存在关联，可以通过 SQL 语句（参见第 4 章）定义、修改、查询表结构和表中的数据。从 DB2 V9 开始还增加了 XML 数据类型，同时提供了 XQuery 查询方法，这种新特性适用于非关系型的数据存储，是对现有关系型数据库的良好补充。
数据库中除了包含表之外，还包括视图、索引、触发器、存储过程等对象，这些对象都能够在特定领域方便数据的管理。
数据库可以是本地的，也可以是远程的。物理上位于本地计算机的数据库为本地数据库，物理上位于另一台计算机的数据库称为远程数据库。
在实际的生产环境中，还经常使用分布式数据库、分区数据库和联邦数据库。分布式数据库指的是分布在不同服务器上的数据库，通过网络连接，各个数据库管理器之间可以进行协作。分区数据库指的是安装在多台服务器上的一个数据库（服务器可以是逻辑上的），每个服务器为一个数据库分区。联邦数据库指的是数据来自于不同种类数据源（如 DB2，Oracle 和 SQL Server 等）的数据库。关于这些数据库的高级特性，不在本书的讨论范围之内，有兴趣的读者请参考 DB2 红皮书或者信息中心。关于分区数据库的部分内容，将在本丛书的第二本《 DB2 深度解析——高级 DBA 和开发者篇》中介绍。
本小节介绍 DB2 数据库的基本特性和管理方法。
创建数据库使用 CREATE DATABASE 语句，该语句包含很多参数，在此介绍常用的参数，其他参数的使用方法请参见 DB2 信息中心。
| CREATE DATABASE < 数据库名称 > [AUTOMATIC STORAGE 自动存储开关 ] [ON 存储路径 ] [DBPATH ON 数据库路径 ] [USING CODESET 字符集 TERRITORY 区域码 ] |
下面对这些参数进行简单介绍。
（1）自动存储开关：取值为 YES 或者 NO。在 DB2 V9 中，默认的选项为 YES，即打开了自动存储功能，新创建的使用自动存储功能的表空间（参见 3.6 节），其容器（参见 3.6 节）和空间分配完全由数据库管理器决定。
自动存储功能只能在数据库创建的时候指定，当数据库创建完成之后，不能更改存储方式。但如果创建了自动存储的数据库，还可以创建不使用自动存储功能的表空间。
（2）存储路径：数据库的控制文件、事务日志文件存放的位置。
（3）数据库路径：数据库中具体的数据文件存放的位置。
如果 AUTOMATIC STORAGE 设置为 YES，可以指定多个存储路径。如果设置为 NO，则只能指定一个存储路径。如果没有明确指定存储路径和数据库路径，则二者都使用实例级别的配置参数 DFTDBPATH 参数所给出的路径。如果只给出了存储路径，没有给出数据库路径，则使用第一个存储路径作为数据库路径。在 UNIX 或 Linux 环境下，存储路径和数据库路径可以指定目录，而在 Windows 环境下，只能指定驱动器名称，不能给出具体目录。在发出创建数据库命令之前，必须事先在操作系统中创建好存储路径和数据库路径。
（4）字符集：在 V9.5 之后，默认的字符集为 UTF-8，还可以指定其他字符集；区域码为 US，CN 和 UK 等，代表国家。需要注意的是，在创建数据库的时候一旦指定了字符集，就无法再修改了。如果业务需要用到多个数据库，应该使这些数据库的字符集相同。
例 3-3 创建数据库。
在 Linux 环境下创建一个名为 newdb 的数据库，使用自动存储功能，其存储路径有两个：/db2/database/newdb1 和 /db2/database/newdb2，数据库路径为 /db2/database/newdb1，字符集为 UTF-8，区域码为 CN。
首先创建两个目录：
| mkdir /db2/database/newdb1 mkdir /db2/database/newdb2 |
然后给这两个目录分配操作系统级别的权限：
| chmod a+rwx /db2/database/newdb1 chmod a+rwx /db2/database/newdb2 |
接着，在命令行处理器中运行如下命令：
| CREATE DATABASE newdb ON /db2/database/newdb1, /db2/database/newdb2 USING CODESET UTF-8 TERRITORY CN |
删除数据库的语句为：
| DROP DATABASE < 数据库名称 > |
与删除实例不同，数据库一旦删除，其中所包含的各种对象以及其对应的容器和文件都将被删除。
数据库被创建之后，大多数内容无法再修改，对于自动存储的所有表空间可以增加存储路径，通过 ALTER DATABASE 语句实现，详细语法请参见信息中心。
也可以在图形界面下创建数据库，以下是在控制中心创建数据库的关键步骤。
首先，在“All Databases”节点上单击右键，弹出菜单中选择“Create Database …”，会打开创建数据库向导，如图 3.6 所示。
图 3.6 在控制中心中创建数据库
接着，在弹出的对话框中选定新建数据库所在的系统和实例，如图 3.7 所示。
图 3.7 选择数据库所在的系统和实例
然后，创建数据库向导就会一步一步指导用户填写相关参数，直到最终创建数据库，如图 3.8 到图 3.11 所示。
图 3.8 创建数据库向导第一步：指定数据库名称
图 3.9 创建数据库向导第二步：指定数据库路径和存储路径
图 3.10 创建数据库向导第三步：指定字符集和区域代码
>
图 3.11 创建数据库向导第四步：创建步骤各个阶段参数总结
在第四步，可以点击“Show Command”按钮，查看对应的命令，如图 3.12 所示。
图 3.12 创建数据库命令
要查看系统中存在哪些数据库，可以使用 DB2 命令：
| LIST DB DIRECTORY |
该命令实际上就是查看系统目录中数据库的信息（参见 3.2 节）。
用户必须连接到数据库中，才能在数据库中进行操作。连接到数据库的命令为：
| CONNECT TO < 数据库名称 > [user 用户名 ] [using 密码 ] |
如果没有提供用户名和密码，则使用操作系统的用户名和密码进行连接。如果提供了用户名但没有提供密码，会在连接的时候要求用户输入密码。
如果连接成功，会提示当前 DB2 的版本、用户名和所连接的数据库别名（Alias，给数据库起的另外一个名称，一旦指定了别名，在后续其他操作中就都要使用别名）。
要断开与数据库的连接，有三种 DB2 命令：
（1）CONNECT RESET
（2）DISCONNECT < 数据库名称 >
（3）TERMINATE
其中 CONNECT RESET 能够断开与当前数据库的连接，经常被使用；DISCONNECT 命令可以指定数据库名称，用来与指定的数据库断开连接；而 TERMINATE 不仅断开与当前数据库的连接，同时还初始化客户端。如果担心客户端出现了某些未知错误，可以使用这个命令。
数据库的状态分为活动的和非活动的两种。用户操作的数据库必须是活动的。可以使用激活数据库的命令来使非活动的数据库变为活动的。激活数据库可以启动所有必要的数据库服务，来为数据库连接和应用程序做准备。
激活数据库的 DB2 命令为：
| ACTIVATE DATABASE < 数据库名称 > |
发出 CONNECT 语句进行数据库连接时，如果数据库是非活动的，会先隐式地激活数据库，然后再进行连接，在这个过程中，用户必须等待；而如果数据库是活动的，连接数据库的速度就会非常快，不需要等待。
把活动的数据库切换成非活动的，可以使用取消激活命令，这个命令的主要作用是停止数据库的活动，其格式为：
| DEACTIVATE DATABASE < 数据库名称 > |
通常情况下，如果所有用户都断开了与数据库的连接，该数据库就会转入非活动状态。但是如果显式地使用了 ACTIVATE DATABASE 命令来激活数据库，则当所有用户断开与数据库的连接时，数据库也不会转入非活动状态，而是仍然保持活动状态。此时就需要使用 DEACTIVATE DATABASE 命令将数据库转入非活动状态，或者使用 db2stop 命令停止当前实例，同时也会停止当前实例下的所有数据库。
需要注意的是，如果某个数据库上还存在连接，则 DEACTIVATE DATABASE 命令不会执行成功，可以使用 FORCE APPLICATIONS ALL 这个 DB2 命令断开所有连接，然后再停止数据库。
实例和数据库的信息存放在 DB2 的系统目录中，在本地机器上创建实例或者数据库时，会自动把这些信息写入到系统目录中。但是如果要通过客户端连接到远程的 DB2 服务器对实例或者数据库进行管理，就需要先对实例或数据库进行编目，使相关的信息写入到客户端本地的机器上，从而才能在客户端访问到远程服务器上的信息。下面将通过一个例子说明对实例和数据库进行编目的步骤。
例 3‑4 编目实例和数据库，然后进行连接。
环境说明：远程服务器的 IP 地址为 213.102.16.38，其上的 DB2 开放 50000 端口，有一个名为 MyWork 的实例。DBA 在客户端想通过网络连接到该服务器上，并且连接到服务器上已经存在的 MyDB 数据库上，在其中创建一张表 MyTable。
以下所有步骤都是在客户端的 CLP 中发出的命令。
第一步：
| CATALOG TCPIP NODE MyNode REMOTE 213.102.16.38 SERVER 50000 REMOTE_INSTANCE MyWork |
该命令表示在客户端本地创建一个节点 MyNode，对应于远程服务器的 MyWork 实例。
第二步：
| CATALOG DATABASE MyDB AS MyDB2 AT NODE MyNode |
该命令表示在指定的节点上编目数据库，指定该节点的原因是 MyDB 数据库在该节点所对应的实例中，AS 子句是给数据库起一个别名，以防和本地的 MyDB 数据库重名。运行该命令之后，在客户端就可以找到远程的 MyDB 数据库了，在客户端的名称是 MyDB2。
第三步：
| CONNECT TO MyDB2 CREATE TABLE MyTable( …… ) |
最后，可以连接到 MyDB2 数据库（对应于远程服务器上的 MyDB 数据库），然后创建表。
连接到某个数据库之后，使用以下 DB2 命令：
| GET DB CFG FOR < 数据库名称 > |
可以列出该数据库中的各种参数，如下所示，就列出了 SAMPLE 数据库的配置情况。
| 数据库 sample 的数据库配置数据库配置发行版级别 = 0x0c00 数据库发行版级别 = 0x0c00 数据库地域 = CN 数据库代码页 = 1208 数据库代码集 = UTF-8 数据库国家 / 地区代码 = 86 数据库整理顺序 = IDENTITY 备用整理顺序 (ALT_COLLATE) = 数字兼容性 = OFF Varchar2 兼容性 = OFF 数据库页大小 = 8192 动态 SQL 查询管理 (DYN_QUERY_MGMT) = DISABLE 对此数据库的发现支持 (DISCOVER_DB) = ENABLE 限制访问 = NO 缺省查询优化类 (DFT_QUERYOPT) = 5 并行度 (DFT_DEGREE) = 1 在算术异常时继续 (DFT_SQLMATHWARN) = NO 缺省刷新有效期 (DFT_REFRESH_AGE) = 0 缺省维护的选项（DFT_MTTB_TYPES）的表类型 = SYSTEM 保留的高频值的数目 (NUM_FREQVALUES) = 10 保留的分位点数目 (NUM_QUANTILES) = 20 十进制浮点舍入方式 (DECFLT_ROUNDING) = ROUND_HALF_EVEN 备份暂挂 = NO 数据库是一致的 = YES 前滚暂挂 = NO 复原暂挂 = NO 启用的多页文件分配 = YES 恢复状态的日志保留 = NO 日志记录状态的用户出口 = NO 自调整内存 (SELF_TUNING_MEM) = OFF 数据库共享内存大小（4KB） (DATABASE_MEMORY) = AUTOMATIC 数据库内存阈值 (DB_MEM_THRESH) = 10 锁定列表的最大存储量（4KB） (LOCKLIST) = 50 每个应用程序的锁定百分比列表 (MAXLOCKS) = 22 程序包高速缓存大小（4KB） (PCKCACHESZ) = (MAXAPPLS*8) 共享排序的排序堆域值（4KB） (SHEAPTHRES_SHR) = 5000 排序列表堆（4KB） (SORTHEAP) = 256 数据库堆（4KB） (DBHEAP) = AUTOMATIC 目录高速缓存大小（4KB） (CATALOGCACHE_SZ) = (MAXAPPLS*4) 日志缓冲区大小（4KB） (LOGBUFSZ) = 8 实用程序堆大小（4KB） (UTIL_HEAP_SZ) = 5000 缓冲池大小（页） (BUFFPAGE) = 250 SQL 语句堆（4KB） (STMTHEAP) = AUTOMATIC 缺省应用程序堆（4KB） (APPLHEAPSZ) = AUTOMATIC 应用程序内存大小（4KB） (APPL_MEMORY) = AUTOMATIC 统计信息堆大小（4KB） (STAT_HEAP_SZ) = AUTOMATIC 检查死锁的时间间隔（毫秒） (DLCHKTIME) = 10000 锁定超时（秒） (LOCKTIMEOUT) = -1 更改的页阈值 (CHNGPGS_THRESH) = 60 异步页清除程序的数目 (NUM_IOCLEANERS) = AUTOMATIC I/O 服务器的数目 (NUM_IOSERVERS) = AUTOMATIC 索引排序标志 (INDEXSORT) = YES 顺序检测标志 (SEQDETECT) = YES 缺省预取大小（页） (DFT_PREFETCH_SZ) = AUTOMATIC 跟踪修改的页数 (TRACKMOD) = OFF 容器的缺省数目 = 1 缺省表空间扩展数据块大小 ( 页 ) (DFT_EXTENT_SZ) = 32 最大活动应用程序数 (MAXAPPLS) = AUTOMATIC 活动应用程序的平均数目 (AVG_APPLS) = AUTOMATIC 每个应用程序的最大打开数据库文件数 (MAXFILOP) = 32768 日志文件大小（4KB） (LOGFILSIZ) = 1000 主日志文件的数目 (LOGPRIMARY) = 3 辅助日志文件的数目 (LOGSECOND) = 2 已更改的至日志文件的路径 (NEWLOGPATH) = 日志文件路径 = D:DB2NODE0000SQL00001SQLOGDIR 溢出日志路径 (OVERFLOWLOGPATH) = 镜像日志路径 (MIRRORLOGPATH) = 首个活动日志文件 = 磁盘上已满的块日志 (BLK_LOG_DSK_FUL) = NO 事务使用的最大主日志空间的百分比 （MAX_LOG）= 0 1 个活动 UOW 的活动日志文件的数目 (NUM_LOG_SPAN) = 0 组落实计数 (MINCOMMIT) = 1 软检查点前回收的日志文件的百分比 (SOFTMAX) = 100 启用的恢复的日志保留 (LOGRETAIN) = OFF 启用的日志记录的用户出口 (USEREXIT) = OFF HADR 数据库角色 = STANDARD HADR 本地主机名 (HADR_LOCAL_HOST) = HADR 本地服务名称 (HADR_LOCAL_SVC) = HADR 远程主机名 (HADR_REMOTE_HOST) = HADR 远程服务名称 (HADR_REMOTE_SVC) = 远程服务器的 HADR 实例名 (HADR_REMOTE_INST) = HADR 超时值 (HADR_TIMEOUT) = 120 HADR 日志写同步方式 (HADR_SYNCMODE) = NEARSYNC HADR 对等窗口持续时间（秒） (HADR_PEER_WINDOW) = 0 第一个日志归档方法 (LOGARCHMETH1) = OFF logarchmeth1 的选项 (LOGARCHOPT1) = 第二个日志归档方法 (LOGARCHMETH2) = OFF logarchmeth2 的选项 (LOGARCHOPT2) = 故障转移日志归档路径 (FAILARCHPATH) = 错误时重试日志归档次数 (NUMARCHRETRY) = 5 日志归档重试延迟（秒） (ARCHRETRYDELAY) = 20 供应商选项 (VENDOROPT) = 启用的自动重新启动 (AUTORESTART) = ON 索引重新创建时间和重做索引构建 (INDEXREC) = SYSTEM (RESTART) 在索引构建期间记录页 (LOGINDEXBUILD) = OFF loadrec 会话的缺省数目 (DFT_LOADREC_SES) = 1 要保留的数据库备份的数目 (NUM_DB_BACKUPS) = 12 恢复历史记录保留时间（天数） (REC_HIS_RETENTN) = 366 自动删除恢复对象 (AUTO_DEL_REC_OBJ) = OFF TSM 管理类 (TSM_MGMTCLASS) = TSM 节点名 (TSM_NODENAME) = TSM 所有者 (TSM_OWNER) = TSM 密码 (TSM_PASSWORD) = 自动维护 (AUTO_MAINT) = ON 自动数据库备份 (AUTO_DB_BACKUP) = OFF 自动表维护 (AUTO_TBL_MAINT) = ON 自动 runstats (AUTO_RUNSTATS) = ON 自动语句统计信息 (AUTO_STMT_STATS) = OFF 自动统计信息概要分析 (AUTO_STATS_PROF) = OFF 自动概要文件更新 (AUTO_PROF_UPD) = OFF 自动重组 (AUTO_REORG) = OFF 启用 XML 字符操作 (ENABLE_XMLCHAR) = YES WLM 收集时间间隔（分钟） (WLM_COLLECT_INT) = 0 |
关于其中每个参数的详细解释，请参见 IBM 官方文档和 DB2 信息中心。
修改某个参数使用如下 DB2 命令：
| UPDATE DB CFG FOR < 数据库名称 > USING < 参数名称 > < 更新后数值 > |
数据库级别的常用命令如表 3.2 所示。
表 3.2 数据库级别的常用命令
| 命令名称 | 描述 |
|---|---|
| GET DB CFG FOR < 数据库名称 > | 返回指定数据库的参数设置 |
| UPDATE DB CFG FOR < 数据库名 > USING < 参数 > < 值 > | 将指定数据库的某个参数设置为新的值 |
| CREATE DATABASE < 数据库名称 > | 创建数据库 |
| DROP DB < 数据库名 > | 删除数据库 |
| ACTIVATE DATABASE < 数据库名称 > | 激活指定数据库 |
| DEACTIVATE DATABASE < 数据库名称 > | 停止指定数据库 |
| CONNECT TO < 数据库名称 > [user < 用户名 >] [using < 密码 >] | 连接到指定的数据库 |
| CONNECT RESET | 断开与当前数据库的连接 |
| DISCONNECT < 数据库名称 > | 断开与指定数据库的连接 |
| TERMINATE | 断开与数据库的连接并初始化客户端 |
| CATALOG DB < 数据库名 > AT NODE < 节点名 > | 编目数据库 |
注：该表格中所有命令都是 DB2 级命令，可以直接在命令行处理器中执行。
|
|
缓冲池（Buffer Pool）是内存中的一块区域，用于临时读入和更新数据库页（包含表行或者索引项）。在计算机领域有一个著名的 80 | 20 法则，即有 80% 的时间会使用 20% 的数据，而只有另外 20% 的时间才会使用其余 80% 的数据。换句话说，现在正在使用的数据，在不久的将来还很有可能（大约 80%）会使用到。这一法则在数据库领域效果更加明显。根据这个规律，可以把最频繁使用的数据从硬盘事先放到内存中，供用户或者程序使用，因为内存的速度要远远高于硬盘的速度。所以，能够从内存中读写的数据越多，读写硬盘（也称为 I/O）的次数和时间越少，数据库运行的效率就会越高。缓冲池就是这块内存区域。每个数据库都至少要包含一个缓冲池。
缓冲池由很多页面构成，同一个缓冲池的所有页面必须大小相同。DB2 V9 支持四种大小的页面：4K，8K，16K 和 32K。如果一个数据库包含不同页面大小的表空间（详见 3.7 节），就应该为每一种页面大小的表空间创建一个相同页面大小的缓冲池。在 UNIX 或 Linux 环境下，SAMPLE 数据库默认的缓冲池名称为 IBMDEFAULTBP，页大小为 8K，共 1000 页。在 Windows 环境下，SAMPLE 数据库默认的缓冲池名称也是 IBMDEFAULTBP，页大小为 8K，共 250 页。
缓冲池创建使用 CREATE BUFFERPOOL 语句，其中有一个重要参数 AUTOMATIC，表示是否想让数据库管理器自动调节缓冲池大小。如果想启用自动调整功能，首先需要把数据库参数 SELF_TUNING_MEM 设置为 ON，然后在 CREATE BUFFERPOOL 语句中加上 AUTOMATIC 参数。
例 3‑5 创建缓冲池。
在 SAMPLE 数据库中创建一个名为 MyBP1 的缓冲池，每个数据页大小为 4K，总页数为 500 页，启用自动调节大小功能。
| UPDATE DB CFG FOR sample USING SELF_TUNING_MEM ON CREATE BUFFERPOOL MyBP1 SIZE 500 AUTOMATIC PAGESIZE 4K |
对已经创建的缓冲池，也可以使其更改为具有自动调整大小功能的缓冲池。例如，把默认的 IBMDEFAULTBP（不具有自动调整大小功能）缓冲池改为有自动调整大小功能的缓冲池，使用如下命令：
| ALTER BUFFERPOOL IBMDEFAULTBP IMMEDIATE SIZE 1000 AUTOMATIC |
删除缓冲池使用 DROP BUFFERPOOL 语句：
| DROP BUFFERPOOL < 缓冲池名称 > |
需要注意的是，一个数据库至少要有一个缓冲池，所以不能把所有的缓冲池都删除掉。
db2mtrk 这个系统级命令可以查看内存使用状况，指定 -d 参数可以查看数据库级别的内存，其中包括缓冲池的使用情况。以下是在 Linux 下查询 SAMPLE 数据库的内存使用状况的命令：
| db2 CONNECT TO sample db2mtrk -d -p -v |
结果会显示出 SAMPLE 数据库内存的详细使用状况，其中也包括缓冲池的状况。
在 SYSIBM.SYSBUFFERPOOLS 或者 SYSCAT.BUFFERPOOLS 中有一个字段 NPAGES，当其设置为 -2 时（有些材料中说其值为 -1，但我们经过在 DB2 V9.5 中进行实验，验证其值为 -2），表示该缓冲池为自动调整大小，此时实际的缓冲池大小可以通过数据库配置参数 BUFFSIZE 查看。如果没有使用自动调整大小功能，NPAGES 字段将显示缓冲池的实际大小。用户可以使用如下语句来查看这个参数：
| SELECT bpname, npages FROM SYSIBM.SYSBUFFERPOOLS |
或者
| SELECT bpname, npages FROM SYSCAT.BUFFERPOOLS |
为了衡量缓冲池设置的大小是否合理，有一个公式计算缓冲池的命中率：
物理读取表示先从磁盘读入到内存（缓冲池）中的次数，而逻辑读取表示读取缓冲池的总次数（包括数据原本就在缓冲池中直接读取的，以及先从磁盘读入到缓冲池再读取的）。这个公式的含义表示原本就在缓冲池中可以直接读取的次数占总的读取次数的比率，这个值越高，表示读写硬盘的次数就越少，数据库运行效率就越高。理想情况下，命中率应该超过 95%，最好接近 100%。
还可以在控制中心中通过图形化方式对缓冲池进行管理，如图 3.13 所示。
图 3.13 缓冲池的设置
|
|
表空间（Table Space）是 DB2 数据库中存储数据的逻辑块。之所以称作逻辑块，是因为它实现了把真正的物理存储设备进行划分的功能。真正在底层存储数据的称为容器，容器可以是裸设备（又叫原始分区，指在 UNIX 或 Linux 下没有格式化的、不通过文件系统来管理的磁盘分区）、目录或者文件。表空间把若干个容器组织起来，使之更有效地进行存储。一个表空间中可以包含一个或多个容器，但是一个容器只能属于一个表空间。如果一个表空间包含多个容器，存放在这个表空间中的数据会循环存储在每一个容器中。与缓冲池一样，目前版本的 DB2 表空间所包含的容器也由四种大小的页面构成：4K，8K，16K 和 32K。合理地分配表空间，可以有效地提高数据存取的效率。通常情况下，把表数据放在一个表空间中，把索引数据放在另一个表空间，把大对象再单独放到一个表空间，如果这些表空间所包含的容器在多块不同的磁盘上，就能够让这些数据并行地进行存取。
表空间有四种类型：常规表空间（Regular Tablespace）、大对象表空间（Large Tablespace）、系统临时表空间（System Temporary Tablespace）和用户临时表空间（User Temporary Tablespace）。
常规表空间用于存储普通数据和索引等内容；大对象表空间用于存储普通数据或者大对象数据；系统临时表空间用于数据库执行排序、重组、连接等操作时存放临时数据；用户临时表空间用于存放和管理用户临时表（参见 3.8.2 小节）。
表空间有两种管理方式：系统管理表空间（System Manage Space，简称 SMS）和数据库管理表空间（Database Manage Space，简称 DMS）。系统管理表空间是由操作系统的文件管理器负责表空间所包含容器的管理，它所包含的容器只能是目录。数据库管理表空间是由 DB2 数据库管理器负责表空间所包含容器的管理，它所包含的容器可以是裸设备或者文件。
通常来说，系统管理表空间基本不需要 DBA 参与管理，操作起来很方便，而数据库管理表空间操作相对麻烦一些。但是数据库管理表空间中的数据存储效率要比系统管理表空间高一些，二者各有所长。
从 DB2 V9 开始，提供了一种自动管理存储的功能，来简化 DBA 的操作。开启这个功能的前提是创建对应数据库的时候开启了自动管理存储功能（参见 3.4.1 小节）。可以在创建表空间时指定是否启用该功能。
上面所述的四种表空间类型和两种管理方式，并不是可以任意组合使用的。以下我们将详细介绍表空间的种类和管理方式。
当表空间开启了自动管理存储功能时，DB2 会自动增加表空间所对应容器的大小。此时，如果创建的是常规表空间或大对象表空间，将会使用数据库管理方式（DMS）；如果创建的是系统临时表空间或用户临时表空间，将会使用系统管理方式（SMS）。
如果没有开启自动管理存储功能，就需要在创建表空间时指定使用哪种管理方式（DSM 或 SMS）来管理哪种类型的表空间。
按照每个页面大小，表空间分成 4K，8K，16K 和 32K 四种，每页的大小越大，能够存储的数据量也就越大。另外，同样页面大小的大对象表空间比常规表空间能够存储的数据也要大很多。例如，一个 32K 页面大小的常规表空间最多可以存储 512GB 的数据，而一个 32K 页面大小的大对象表空间最多可以存储 16394GB 的数据。正因为如此，在 DB2 V9 中，数据库管理表空间的默认类型由原来的“常规”改为了“大对象”。
创建表空间使用 CREATE TABLESPACE 语句，其中包括几个重要参数，下面我们结合几个例子详细讲解。
例 3‑6 创建表空间示例一。
在 Windows 环境下 SAMPLE 数据库中创建一个名为 resource 的系统管理表空间，包含三个容器：在 D，E 和 F 分区上各有一个 TbSp 的目录。
| CONNECT TO sample CREATE TABLESPACE resource MANAGED BY SYSTEM USING('D:TbSp','E:TbSp','F:TbSp') |
系统管理表空间所包含的容器必须是目录，这些目录并不需要事先在硬盘上创建好，在创建表空间的时候会自动创建这些目录。
例 3‑7 创建表空间示例二。
在 Windows 环境下 SAMPLE 数据库中创建一个名为 resource2 的数据库管理表空间，页大小为 8K，包含两个容器：在 D 盘和 E 盘的 DMS 目录下各有一个名为 DMSTBSP 的文件，每个容器都包含 250 页。
| CONNECT TO sample CREATE TABLESPACE resource2 PAGESIZE 8K MANAGED BY DATABASE USING(FILE 'D:DMSDMSTBSP' 250, FILE 'E:DMSDMSTBSP' 250) |
数据库管理表空间所包含的容器可以是文件或者裸设备。
例 3‑8 创建表空间示例三。
在 Linux 环境下 SAMPLE 数据库中创建一个名为 resource3 的数据库管理表空间，页大小为 4K，使用已经创建好的缓冲池 MyBP1 进行缓存，扩展块大小为 64 页，预读取大小为 192 页，包含三个容器：/dev/rhd1，/dev/rhd2 和 /dev/rhd3，每个容器都包含 2000 页。
| CREATE TABLESPACE resource3 PAGESIZE 4K MANAGED BY DATABASE USING (DEVICE '/dev/rhd1' 2000, DEVICE '/dev/rhd2' 2000, DEVICE '/dev/rhd3' 2000) EXTENTSIZE 64 PREFETCHSIZE 192 BUFFERPOOL MyBP1 |
数据库管理表空间所包含的容器如果是设备，该设备必须事先已经存在，并且实例的拥有者和 SYSADM 组（参见 5.3 节）必须能够写入它们。扩展块（Extent）是由一些连续的页构成，扩展块大小指的是在写入下一个容器之前写入到当前容器中数据的页数，对于本例来说，一共有三个容器，在每个容器中写入 64 个页面的数据之后，会转移到下一个容器继续写入，循环往复。预读取大小指的是数据库管理器在进行存取之前会自动预读到缓冲区的页面的数量，如果不指定该值，将使用数据库级配置参数 DFT_PREFETCH_SZ 的值，如果该值为 AUTOMATIC，将会采用以下的公式进行计算：
| 预读取页面数量 = 容器数量×每个容器的物理主轴数量×扩展块大小 |
其中物理主轴数量可以通过实例级注册表变量 DB2_PARALLEL_IO 来设定。
在设置表空间的各项参数中，一个典型的错误是把预读取页面的数量设置为 0，这样会导致性能低下。
例 3‑9 创建表空间示例四。
在 Linux 环境下 SAMPLE 数据库中创建一个名为 resource4 的表空间，开启自动管理存储功能。
| CONNECT TO sample CREATE TABLESPACE resource4 MANAGED BY AUTOMATIC |
注意，由于 SAMPLE 数据库在创建的时候默认就开启了自动存储开关，所以才能够在其中创建表空间时开启自动存储功能。
例 3‑10 创建表空间示例五。
在 Linux 环境下 SAMPLE 数据库中创建一个名为 usertmpsp 的用户临时表空间，采用数据库管理表空间方式进行管理，包含两个容器：/dms/container1 和 /dms/container2，大小都为 500 页。
| [db2inst1@ibmclass ~]$ su - root Password: [root@ibmclass ~]# cd / [root@ibmclass /]# mkdir /dms [root@ibmclass /]# chmod a+rwx /dms [root@ibmclass /]# su - db2inst1 [root@ibmclass dms]# db2 db2 => CONNECT TO sample db2 => CREATE USER TEMPORARY TABLESPACE usertmpsp MANAGED BY DATABASE USING(FILE '/dms/container1' 500, FILE '/dms/container2' 500) |
需要注意的是，在 Linux 中，创建表空间之前需要先对容器所在目录的权限进行分配。
例 3‑11 创建表空间示例六。
在 Linux 环境下 SAMPLE 数据库中创建一个名为 tmpsyssp 的系统临时表空间，采用系统管理表空间方式进行管理，包含两个容器：/sms/syssp1/ 和 /sms/syssp2/。
| [db2inst1@ibmclass ~]$ su - root Password: [root@ibmclass ~]# cd / [root@ibmclass /]# mkdir /sms [root@ibmclass /]# mkdir /sms/syssp1 [root@ibmclass /]# mkdir /sms/syssp2 [root@ibmclass /]# chmod a+rwx /sms/syssp1 [root@ibmclass /]# chmod a+rwx /sms/syssp2 [root@ibmclass /]# su - db2inst1 [root@ibmclass dms]# db2 db2 => CONNECT TO sample db2 => CREATE SYSTEM TEMPORARY TABLESPACE tmpsyssp MANAGED BY DATABASE USING('/sms/syssp1', '/sms/syssp2') |
需要注意的是，给数据库中各个对象命名时，不能以 sys 开头，否则会报系统错误。以 sys 开头的对象都被认为是 DB2 内部已经提供的对象，而不是用户自己创建的。所以本例中表空间命名为 tmpsyssp，而不是 systmpsp。
表空间创建好之后，可以向其中添加新的容器，也可以修改现有容器的大小，具体命令请参见 DB2 信息中心，此处不再赘述。
新建一个数据库时，也可以指定表空间，如果没有指定表空间，默认情况下会创建三个表空间：
（1）目录表空间 SYSCATSPACE，用于存储各种系统信息的目录表；
（2）系统临时表空间 TEMPSPACE1，用于执行排序等操作时存放临时数据；
（3）用户表空间 USERSPACE1，用于存放用户的表、索引、大对象等数据。
当一个表空间没有被任何表、索引或大对象使用，可以将其删除，释放对应的容器空间。删除表空间的语句为：
| DROP TABLESPACE < 表空间名称 > |
要想列出当前数据库的所有表空间，可以在命令行处理器中首先连接到某个数据库上，然后发出如下 DB2 命令：
| LIST TABLESPACES [SHOW DETAIL] |
如果包括了 SHOW DETAIL 选项，可以列出详细信息。要记住在这一步中列出的表空间 ID 号，以便在下一条命令中使用。
如果想列出某个表空间的容器，使用如下 DB2 命令：
| LIST TABLESPACE CONTAINERS FOR < 表空间 ID 号 > |
还可以通过查询系统目录中有关表空间的视图来查看表空间状态：
| SELECT tbspace, tbspacetype, extentsize, prefetchsize FROM syscat.tablespaces |
通过快照监控器也可以查看表空间的状态，相应的 DB2 命令如下：
| GET SNAPSHOT FOR TABLESPACES ON < 数据库名称 > |
db2pd 能够从内存集中检索信息，指定相应参数，就可以查看表空间相关信息：
| db2pd – db < 数据库名称 > -tablespaces |
需要注意的是，由于 db2pd 是系统级命令，所以是在命令窗口中，而不是在命令行处理器中执行的。
表空间的各种维护操作还可以在控制中心里通过图形化界面实现，如图 3.14 所示。
图 3.14 使用图形化方式管理表空间
（1）对于在线事务处理系统（OLTP），可以使用小的页面，如 4K 的页。而对于在线分析处理系统（OLAP）和决策支持系统（DSS），应该使用大页面，如 32K 的页；
（2）通常 DMS 在性能上要高于 SMS，大约高 10% ～ 15%;
（3）增加缓冲池的大小；
（4）表空间预读取大小要足够大，一定不能设置为 0；
（4）尽量开启缓冲池自动调整大小的功能；
（5）尽量开启表空间的自动管理存储功能。
|
|
模式（Schema）是一个逻辑分组，每个数据库内部的对象在创建时，都可以显式或者隐式地指定模式，同一个模式下的对象构成一个集合。从某种角度来说，模式与用户比较相似，但是与用户也有不同之处，DB2 的用户必须存在于操作系统中，而模式并不需要存在于操作系统中，也不需要与用户一一对应上。
通常情况下，使用数据对象都需要指定模式名称和对象名称，即：
| 模式名称 . 对象名称 |
如 db2inst1.employees 代表模式 db2inst1 下的表 employees。不同的模式下，对象名称可以相同，不会相互干扰。
显式创建模式的命令为：
| CREATE SCHEMA < 模式名称 > [AUTHORIZATION 用户名 ] |
该命令的含义是创建一个指定名称的模式，如果包含 AUTHORIZATION 子句，则表示该用户是这个模式的拥有者。
可以给模式授予相应的特权，具体操作请参见 5.4 节。
如果在创建某个对象时指定了模式名称，而该模式还不存在，则会隐式地创建该模式，如：
| CREATE TABLE my.table1( …… ) |
如果模式 my 不存在，则会创建该模式，接着在模式 my 下创建表 table1。
如果在创建对象或者查询数据的时候没有指定模式名称，系统会使用专用寄存器 CURRENT SCHEMA 中指定的值作为模式名称。可以使用如下 DB2 命令查看该专用寄存器（参见 3.17 节）的值：
| VALUES CURRENT SCHEMA |
可以通过如下 DB2 命令修改该寄存器中的值：
| SET CURRENT SCHEMA < 模式名称 > |
这样该专用寄存器中记录的就是指定的模式名称。
如果没有专门指定名称，DB2 使用当前登录的用户名作为该专用寄存器的值。
可以在控制中心中通过图形化方式管理模式，如图 3.15 所示。
图 3.15 模式的管理
|
|
DB2 是关系型数据库的代表，二维表是关系型数据库的典型数据结构。在 DB2 中真正存储数据的对象就是表（Table）。表中包含若干列，每一列都要指定列名和数据类型，一个列又可以称为一个字段。各个列定义好之后，表的框架就搭建好了。然后可以在表中填充数据，数据是以行的形式存在于表中的，每一行数据又称为一条记录。
创建表的典型语句为：
| CREATE TABLE < 表名称 > ( 字段 1 数据类型 [ 列级别约束 ], 字段 2 数据类型 [ 列级别约束 ], …… 字段 n 数据类型 [ 列级别约束 ,] [ 表级别约束 ] ) [IN < 表空间名称 >] [INDEX IN < 表空间名称 >] [LONG IN < 表空间名称 >] |
其中数据类型参见 3.11 节，约束参见 3.12 节，表空间参见 3.6 节。与在数据库原理课程中学过的标准建表语句稍有不同的是，可以指定这张表的普通数据放在哪个表空间当中，索引放在哪个表空间当中，大对象数据放在哪个表空间当中。如果没有指定与表空间相关的子句，这些数据将自动创建在系统默认的表空间 USERSPACE1 中。
表创建好之后可以进行一定程度的修改，通常来说，为了不影响已经存在的业务代码的运行，应该只允许对现有表添加新的字段，或者把字符类型的字段的宽度增加，或者修改约束条件，其他的修改操作（例如删除某个字段）即使在语法上允许，也尽量避免使用。修改表使用 ALTER TABLE 语句，具体的语法请参见 DB2 信息中心，此处不再赘述。
删除表使用 DROP TABLE 语句，需要注意的是，删除表既删除其中的数据，也删除表的定义。
考虑到读者的基础，不再给出创建、修改、删除基本表的例子，需要更多信息的读者可以阅读任何一本数据库原理的教材中关于 SQL 语句的章节。
用户临时表又称为已声明的全局临时表（Declared Global Temporary Table，简称 DGTT），与普通的用户表不同，用户临时表并不是一直存储在硬盘上，而是在一个应用程序结束之后，与之相关的用户临时表会自动从系统中删除。另外，用户临时表还有一些安全性和性能上的优势，如：
（1）一个用户（或者应用程序）定义的用户临时表不会被其他用户（或者应用程序）看到；
（2）在对用户临时表操作的时候，可以记录事务日志，也可以不记录事务日志；
（3）不进行系统编目；
（4）没有行锁；
（5）不进行权限检查。
在使用用户临时表的时候容易引起误解的是，它仍然支持索引，可以在它上面创建任何标准的索引，同时它也支持统计，可以对它进行 RUNSTATS 操作（参见 7.8.3 小节）。
例 3‑12 用户临时表的创建。
| CREATE USER TEMPORARY TABLESPACE apptemps MANAGED BY SYSTEM USING ('apptemps'); DECLARE GLOBAL TEMPORARY TABLE t_employees LIKE employee NOT LOGGED; DECLARE GLOBAL TEMPORARY TABLE SESSION.t_dept ( deptid CHAR(6), deptname CHAR(20) ) ON COMMIT DELETE ROWS NOT LOGGED; DECLARE GLOBAL TEMPORARY TABLE SESSION.t_projects AS ( full select ) DEFINITION ONLY ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE IN TABLESPACE apptemps; |
用户临时表空间默认创建数据库的时候没有创建，需要手工创建。
用户临时表使用 DECLARE 创建，使用 DECLARE 语句意味着不在系统目录中写记录。
第一个临时表 t_employees，没有指定模式，其模式被默认成 SESSION，LIKE 只是把 emplyee 的结构复制过来，而不会复制数据，NOT LOGGED 指定此次事务不记日志。
第二个临时表，当 COMMIT 语句执行后就删除临时表中的行，但是表结构还存在，直到连接断开为止。
第三个临时表，当 COMMIT 语句执行后还保持临时表中的行，WITH REPLACE 语句表示如果此表之前已经存在，则替换原来的表。
|
|
视图（View）是一个虚表，它本身不存储数据，而是架设在其他表或者视图上来查看数据。视图就好比是一个放大镜，当定义好这个放大镜的范围之后，就可以通过这个放大镜查看其他表中的相关数据。能够查看哪些数据，以什么样的方式查看数据，都是在创建视图的时候决定的。
合理使用视图能够带来很多优势，主要包括：
（1）视图能够简化用户的操作；
（2）视图使用户能够以多种角度看待同一数据；
（3）视图对重构数据库提供了一定程度的逻辑独立性；
（4）视图能够对机密数据提供安全保护。
创建视图使用 CREATE VIEW 语句，其基本语法为：
| CREATE VIEW < 视图名称 > [( 字段列表 )] AS 子查询 [WITH CHECK OPTION] |
其中如果省略了字段列表，则会根据子查询的字段列表来生成视图的字段，需要注意的是，与创建表不同，如果明确指定了字段列表，只能指定字段的名称，具体的数据类型在基本表中已经定义好了，不能在视图中再指定数据类型。关于 WITH CHECK OPTION 选项，将在下一小节详细介绍。
例 3‑13 创建视图。
创建一个名为 empinfo 的视图，在其中包括职员编号（empno）、名字（firstnme）和部门名称（deptname），注意信息来自于两张表：职员表 employee 和部门表 department，两张表通过部门编号进行关联。
| CREATE VIEW empinfo AS SELECT e.empno, e.firstnme, d.deptname FROM employee e, department d WHERE e.workdept=d.deptno |
删除视图使用 DROP VIEW 语句。
可以通过控制中心图形化方式管理视图，如图 3.16 所示。
图 3.16 视图的管理
3.9.2 视图的 WITH CHECK OPTION 选项
使用 WITH CHECK OPTION 选项可以在视图上指定一个约束，该约束在通过视图进行数据插入和修改时会起作用。
上一节讲到，视图当中不保存数据，但是我们可以通过视图对数据进行操纵，实际上是对视图所对应的基本表中的数据进行操纵。创建视图的时候在子查询中可能包含一个 WHERE 子句，这个子句指定的条件可以看成一个大门，如果加上了 WITH CHECK OPTION 选项，就要求数据必须通过这个大门进入，否则的话，数据可以不通过这个大门（不满足 WHERE 子句的条件）进入到基本表中。
例 3‑14 带 WITH CHECK OPTION 选项的视图。
创建一个名为 empsalary 的视图，包含薪水（salary）高于 4000 元的职员的编号（empno）、名字（firstnme）、姓氏（lastname）、教育级别（edlevel）、性别（sex）、薪水（salary）和奖金（bonus）等信息，并且要求不能通过该视图向职员表 employee 中插入薪水不高于 4000 元的记录。
| CREATE VIEW empsalary AS SELECT empno, firstnme, lastname, edlevel, sex, salary, bonus FROM employee WHERE salary>4000 WITH CHECK OPTION |
按照上面的语句把视图创建成功，然后分别执行如下两条插入语句：
| INSERT INTO empsalary VALUES('800101', 'Winter', 'Green', 15, 'M', 5000, 700) INSERT INTO empsalary VALUES('800102', 'Jack', 'Liu', 13, 'F', 3500, 300) |
会发现第一条语句能够执行成功，而第二条语句执行时报错：插入或更新操作的结果行不符合视图定义。这就说明在通过视图向表中插入数据时，由于视图在创建的时候使用了 WITH CHECK OPTION 子句，就会强制执行条件检查，对于不满足条件的数据，不允许通过视图进入到基本表中。
视图可以嵌套，即在视图的基础上再创建视图，此时视图是否有强制检查选项，需要根据这两个视图创建的定义来确定。
例 3‑15 WITH CHECK OPTION 选项的级联。
已知表 t1 的两个字段 c1 和 c2 都是整型的，给出如下几个视图的定义：
| CREATE VIEW v1 AS SELECT c1,c2 FROM t1 WHERE c1<10 CREATE VIEW v2 AS SELECT c1,c2 FROM v1 WHERE c1>0 WITH LOCAL CHECK OPTION CREATE VIEW v3 AS SELECT c1,c2 FROM v2 WHERE c2>100 CREATE VIEW v4 AS SELECT c1,c2 FROM v3 WHERE c2<200 WITH CASCADED CHECK OPTION |
那么以下 INSERT 语句哪些能够执行成功？
| （1）INSERT INTO v1 VALUES(100, 20) （2）INSERT INTO v2 VALUES(100, 20) （3）INSERT INTO v3 VALUES(5, 20) （4）INSERT INTO v4 VALUES(5, 20) （5）INSERT INTO v4 VALUES(100, 150) （6）INSERT INTO v4 VALUES(5, 150) （7）INSERT INTO t1 VALUES(100, 20) |
分析：
视图 v1 创建时没有指定 WITH CHECK OPTION 选项，所以可以通过 v1 向表 t1 中插入任何数据，第 1 条语句能够执行成功。
视图 v2 创建时指定了 WITH LOCAL CHECK OPTION 选项，代表只强制检查该定义语句中指定的条件（c1>0），与视图 v1 的条件（c1<10）无关，所以第 2 条语句也能够执行成功。
视图 v3 没有指定 WITH CHECK OPTION 选项，而且是定义在视图 v2 的基础上，所以只需要满足 v2 的条件（c1>0）即可，所以第 3 条语句也能够执行成功。
视图 v4 定义在 v3 之上，指定了 WITH CASCADED CHECK OPTION 选项，代表强制进行级联检查，含义是不仅要检查该语句本身指定的条件（c2<200），同时还要检查其原始的每一级视图的条件，包括 v3 的条件（c2>100）、v2 的条件（c1>0）和 v1 的条件（c1<10），所以第 4、5 两条语句都会执行失败，而第 6 条语句会执行成功。
无论视图上定义了什么条件，加上了什么样的强制检查选项，对基本表 t1 都不会有任何影响，直接向基本表中插入数据不会有任何问题，所以第 7 条语句可以执行成功。
对视图的操作实际上是对后台的基本表中数据进行操作，这些数据有可能来自于一张表，也可能来自于多张表，一般来说，只有当视图来自于一张表中的某些字段时，才能够通过该视图对数据进行操作。判断一个视图是否可更新，主要看其在创建的时候指定的子查询。子查询必须满足以下条件：
（1） 其 FROM 子句基于一张表、一个可更新的嵌套表表达式（参见第 4 章）或者一个可更新的公共表表达式（参见第 4 章）；
（2） 不使用 GROUP BY 和 HAVING 子句；
（3） SELECT 选择的列中不包括列函数；
（4） 不使用集合操作 UNION，EXCEPT 和 INTERSECT，但可以使用 UNION ALL；
（5） 不使用 DISTINCT；
（6） 不满足以上条件的视图称为只读视图，在 SYSCAT.VIEW 中标记为 READONLY 的视图就是只读视图。
需要注意的是，一个可更新视图只是在理论上可以进行更新，但实际通过该视图对基本表的数据进行操作时，能否操作成功还要看是否违反了基本表上定义的约束条件。
|
|
索引（Index）主要用于提高数据查询的效率，唯一索引还可以保证列的唯一性。
在关系二维表上定义的索引分成普通索引、唯一索引和聚簇索引三种，对 XML 字段可以单独定义索引（其索引方式与关系模型的不同）。
普通索引主要用于提高查询效率，可以按照升序或者降序或者双向来创建索引。当查询数据时需要排序，就可以利用这种索引直接返回结果，不需要把数据都放到系统临时表空间中去排序。
唯一索引除了能够提高查询效率之外，更重要的作用是保证相关字段的唯一性。跟表中的唯一约束（参见 3.12.2 节）不同，唯一索引所包含的字段并不要求必须非空，它只要求所包含的字段值组合起来是唯一的。
聚簇索引又称为集群索引，这种索引能够直接把表中的数据按照指定的顺序放到合适的位置，所以在查询的时候可以获得很高的效率。当插入新数据或者数据修改时，会将数据插入到或者移动到合适的页面，以便能够保持正确的查询顺序。聚簇索引在每张表中只能创建一个。在增、删、改操作非常频繁的表中不适合建立聚簇索引。
XML 索引是在表中 XML 字段所包含的 XML 文档当中创建特定的索引，用于对 XML 路径和值进行查询时提高效率。
用户和应用程序只能创建和删除索引，但不能明确指定何时使用索引，索引何时使用、如何使用是由数据库管理器来决定的。
本小节介绍索引存储的基本原理。数据库管理器使用 B+ 树结构进行索引存储。B+ 树如图 3.17 所示，其中 RID 表示行标识，代表指向具体数据行的指针。
图 3.17 索引 B+ 树的结构示例
顶层称为根节点。底层由叶节点组成，底层存储了索引键值，并有一个指针指向包含键值的表中的行。根节点层和叶节点层之间的那些层称为中间节点。
当查找特定的索引键值时，索引管理器会从根节点开始搜索该索引树。对于下一层的每个节点根都包含一个键。每个键的值是下一层中对应节点的最大现有键值。
图 3.17 显示要查找的键是“K”。在根节点中大于或等于“K”的第一个键是“N”。它指向下一层的中间节点。在该中间节点中大于或等于“K”的第一个键是“L”。它指向发现有“K”的索引键及其对应的行标识的特定叶节点。行标识能够标识基本表中的对应行，实际上就是一个指向对应行的指针。
创建索引使用 CREATE INDEX 语句，下面通过几个例子来说明如何创建各种索引。
例 3‑16 创建普通索引。
有一张数据量非常大的学生表 student，其中有学号（stuno）、姓名（name）、性别（sex）等字段，在上面经常进行这样的查询：
| SELECT name, sex FROM student ORDER BY stuno DESC |
如何创建一个索引，能够有效地提高查询效率？
由于在查询的时候要根据学号进行降序排序，所以应该把索引创建在学号字段上，而查询的字段是姓名和性别，所以把这两个字段也放到索引当中是比较理想的。
| CREATE INDEX stuIdx ON student(stuno DESC) INCLUED(name, sex) |
创建索引的语句中用 ON 子句指定索引创建在哪张表的哪个字段上，同时可以指定升序或者降序，INCLUED 子句指定的字段可以包含在索引当中，这样在发出上述查询时，就不必到基本表 student 中去查询 name 和 sex 了，而是在索引当中直接返回这两个字段的值，大大提高了查询效率。
当然，创建这样的索引，由于包含了另外的两个字段，索引所占用的空间也就增大了。
例 3‑17 唯一索引。
学生表 student 的身份证号字段 id_card 中不允许出现重复值，但是可以有空值，来表示暂时不知道该学生的身份证号码，用唯一索引实现该功能。
| CREATE UNIQUE INDEX idCardIdx ON student(id_card) |
在创建索引的命令中加上 UNIQUE 关键字，表示创建的是唯一索引，其所包含的字段中不允许出现重复值，但可以出现空值。请读者注意它与唯一约束的区别（参见 3.12.2 节），唯一约束既要求保证唯一性，又要求非空。
例 3‑18 单向索引。
创建一个定义于学生表 student 的姓名（name）字段的索引，在该字段上只允许利用该索引进行单向的升序检索。
| CREATE INDEX nameIdx ON student(name ASC) DISALLOW REVERSE SCANS |
在 DB2 V8 和更早的版本中，索引默认的检索方式是单向的，只进行升序检索或者降序检索，如果想利用一个索引进行双向检索，必须在创建索引的时候明确指定 ALLOW REVERSE SCANS 选项。而在 DB2 V9 中，索引的默认检索方式已经改成了双向，如果明确要求进行单向检索，需要指定 DISALLOW REVERSE SCANS 选项，例如本题。
索引的管理也可以使用控制中心，如图 3.18 所示。
图 3.18 索引的管理
|
|
在表中存储数据时，不同类型的数据占用的空间和存储的方式都不相同。接下来，我们就简单认识一下 DB2 中的各种数据类型（Data Type）。
DB2 主要分成两类数据类型：内置的数据类型和用户自定义的数据类型。本节只介绍内置类型，用户自定义类型在 3.17.2 节介绍。
内置数据类型主要包括数值型、文本型、日期时间型和 XML 四大类。
数值型具体划分如图 3.19 所示。
图 3.19 数值型数据的种类
SMALLINT 称为小整型或短整型，是两个字节的整数，精度为 5 位，其数据表示范围从 -32,768 到 32,767。
INT 称为整型，也可以写成 INTEGER，是四个字节的整数，精度为 10 位，其数据表示范围从 -2,147,483,648 到 2,147,483,647。
BIGINT 称为大整型或长整型，是八个字节的整数，精度为 19 位，其数据表示范围从 9,223,372,036,854,775,808 到 9,223,372,036,854,775,807。
REAL 称为单精度浮点数，是实数的 32 位近似值。数字可以为零，或者在从 -3.402E+38 到 -1.175E-37 或从 1.175E-37 到 3.402E+38 的范围内。
DOUBLE 称为双精度浮点数，是实数的 64 位近似值。数字可以为零，或者在从 -1.79769E+308 到 -2.225E-307 或从 2.225E-307 到 1.79769E+308 的范围内。
DECFLOAT 称为十进制浮点数，是 V9.5 中引入的新数据类型，适合于处理准确的十进制业务。它具有 DECIMAL 类型的准确性，同时又具有浮点数的性能优势，特别适合于在处理货币值相关的应用程序中使用。
DECIMAL 称为十进制数，是一种可以自己定义小数位数的压缩型十进制数，也可以写成 DEC，NUMERIC 或者 NUM，定义这种类型的数据需要指定两个参数：DECIMAL(p,s)。p 表示数字的总长度（不包括正负号和小数点），s 表示小数长度。
DECIMAL 类型的数据可以精确地表示指定精度的小数。它所占用的存储空间与第一个参数，即数字的总长度有关，计算公式为：tranc(p/2)+1。其中 tranc 表示截取整数部分。
字符型具体划分如 3.20 所示。
图 3.20 字符型数据的种类
CHAR 称为定长字符串，需要指定长度，如 CHAR(10)。也可以写成 CHARACTER( 长度 )。定长字符串的长度介于 1 到 254 字节之间。如果没有指定长度，那么就认为是 1 个字节。
VARCHAR 称为变长字符串，需要指定最大长度，如 VARCHAR(20)。变长字符串长度的最大值是 32672。存储变长字符串时，需要额外花费 4 个字节来记录字符串的实际长度。所以，如果指定了 VARCHAR(20)，每个这种类型的数据在硬盘上实际要占用 20+4=24 个字节的空间。
需要注意的是，对于变长字符串的实际占用存储空间量还有一个限制：数据必须适合放在单个表空间页面上。也就是说，对于使用 4K 页表空间的表，长度不能超过 4 × 1024-4=4092 字节；对于 8K 页表空间的表，长度不能超过 8 × 1024-4=8188 字节；对于 16K 页表空间的表，长度不能超过 16 × 1024-4=16380 字节；对于 32K 页表空间的表，长度不能超过 32 × 1024-4=32764 字节，但是该数超过了 VARCHAR 的最大长度 32672，所以对于 32K 页表空间的表，最大能够存放 32672 字节。默认情况下表空间为 4K，所以要使用 VARCHAR 类型来存储超过 4092 字节的数据，就需要显式创建更大页大小的表空间。
LONG VARCHAR 称为大型变长字符串，不需要指定最大长度，其最大可以存储 32700 个字节。与 VARCHAR 类型不同的是，该数据类型不受表空间页大小的限制，对于 4K 页表空间的表，长度可以超过 4092 字节，只要不超过 32700 字节即可。实际在硬盘上占用的存储空间为 ( 字符串长度 +24)，24 个字节用于记录与该数值相关的信息。
CLOB 称为大对象字符串，需要指定长度，CLOB(n [K|M|G])，长度可以是字节，也可以是 K，M 或 G 等某一种单位，每个 CLOB 最大可以存储 2G 字节的数据。CLOB 数据并不是直接存放在表中，而是单独存放在一个表空间中，表中存放的是一个指针，指向对应的实际数据。
GRAPHIC，VARGRAPHIC，LONG GRAPHIC 和 DBCLOB 分别与前面的 CHAR，VARCHAR，LONG VARCHR 和 CLOB 对应，只是都存储双字节字符，存储的长度都减小一半。
BLOB 称为二进制大对象，也需要指定长度，BLOB(n [K|M|G])，长度可以是字节，也可以是 K，M 或 G 等某一种单位，每个 BLOB 最大可以存储 2G 字节的二进制数据。与 CLOB 一样，表中存放的也是指针，指向实际的数据。BLOB 适合于存放图片、音频、视频的容量非常大的内容，DB2 本身不考虑存放的内容是什么，只是按照二进制的方式将数据存放起来，使用的时候还需要配合相应的函数，对不同内容的数据进行不同的处理。
日期时间型包括 DATE，TIME 和 TIMESTAMP 三种。
DATE 称为日期型，能够表示的年份范围从公元 1 年到 9999 年中的任何一天，日期型数据显示出来是固定长度的 10 位字符串，在硬盘上存储需要占用 4 个字节，采用的是压缩字符串形式进行存储。
TIME 称为时间型，能够表示一天当中的时、分、秒，显示出来是固定长度的 8 位字符串，在硬盘上存储需要占用 3 个字节，采用的也是压缩字符串形式进行存储。
TIMESTAMP 称为时间戳型，能够表示年、月、日、时、分、秒，并且能够精确到微秒级别（具体能够精确到的位数还与计算机的硬件和操作系统有关）。
XML 是从 DB2 V9 开始引入的新的数据类型，能够直接把 XML 数据作为内容存储到表中，与以往把 XML 作为大对象存储不同的是，从 V9 开始，提供了 XQuery（类似于关系数据库中 SQL 的一种专门针对 XML 的查询语言）直接对 XML 这种层次型的数据进行各种操作，关于 XQuery 的简单介绍，请参见第 4 章。关于 XQuery 更详细的介绍，可以阅读本丛书的第二本《 DB2 深度解析——高级 DBA 和开发者篇》。
在 9.1 版本中，XML 数据是与表中数据分开存放的，XML 数据单独存放在一个表空间中，而从 9.5 版本开始，长度比较短的 XML 数据直接存放在表中，长度很长的 XML 数据单独存放在另外的表空间中，这样有利于提高查询效率。
在关系数据库中还存在一种特殊的类型：空值，用 Null 表示。如果一个值被设置为空值，表示该值不存在。空值可以放在任意的数据类型中来表示不存在。需要注意的是，空值不能参与任何的算术运算和逻辑运算。
|
|
关系数据库中二维表的每一列数据除了需要指定数据类型，有时还需要指定一些约束条件，来限制该列能够存储哪些数据。关系数据库中主要存在五种约束（Constraint）：非空、唯一、主键、外键、检查。
约束有两个级别：列级别和表级别。如果某个约束只对某个列有限制，就是列级别约束，如果某个约束与多个字段相关，则需要定义成表级别约束。
非空（Not Null）约束表示某个字段中不允许出现空值（Null），就规定这个字段使用非空约束，它是一个列级别约束。其定义方式为：
| 字段名称 数据类型 NOT NULL |
某个字段中不允许出现重复值，就规定这个字段使用唯一（Unique）约束，同样它也是一个列级别的约束。需要注意的是，从 DB2 V9 开始，唯一约束同时也要求非空，即定义了某个字段唯一，则该列当中既不能出现重复值，也不能出现空值。其定义方式为：
| 字段名称 数据类型 UNIQUE |
主码（Primary Key）约束又称为主键约束，是一个或者多个字段的组合，关系数据库要求在一张表中，不能出现完全相同的两行，通常主键就是能够用于区分不同记录的字段或者字段组合。在实际项目中，每张表往往会定义一个编号字段来作为主码。
主码要求同时满足非空和唯一的条件，如果主码是由多个字段组合构成的，每个字段中都不能出现空值，这些字段的组合不能重复。
如果单独一个字段作为主码，它既可以定义成列级别约束，也可以定义成表级别约束。如果是多个字段组合作为主码，必须定义成表级别约束。
列级别主码的定义方式为：
| 字段名称 数据类型 NOT NULL PRIMARY KEY |
需要注意的是，在定义主码之前必须指定该字段为非空的。
表级别主码的定义方式为：
| PRIMARY KEY( 字段列表 ) |
外码（Foreign Key）约束又称为外键约束，通常是定义两张表之间的关联的。相关联的两张表一张作为主表，一张作为从表，从表中有一个或者多个字段参照主表中相应的字段，也就是说，从表中这些字段当中只能出现主表中出现过的值（或者是空值），不能出现其他没有出现过的值。下面通过一个例子来讲解外码的创建。
例 3‑19 外码约束。
建立两张表，一张为系别表 Department，包括编号（DeptNo）和系名（DeptName）两个字段，一张为学生表 Student，包括学号（StuNo）、姓名（Name）、性别（Sex）和系别（DeptNo），其中学生表的系别字段要参照系别表，同时要求当系别表中某行记录删除时，自动删除学生表中该系的所有学生。
| CREATE TABLE Department ( DeptNo CHAR(10) NOT NULL PRIMARY KEY, DeptName VARCHAR(20) NOT NULL ) CREATE TABLE Student ( StuNo CHAR(11) NOT NULL PRIMARY KEY, Name VARCHAR(8) NOT NULL, Sex CHAR(2), DeptNo CHAR(10), FOREIGN KEY(DeptNo) REFERENCES Department(DeptNo) ON DELETE CASCADE ) |
在从表 Student 创建的最后一句中，ON DELETE CASCADE 子句指的是当主表中的数据删除时，从表中对应的数据一起被级联删除。此处还可以写成 ON DELETE SET NULL，表示主表数据删除时，从表对应数据设置为空值，其他的写法请参见信息中心。
关于外码的创建，有三点需要注意：一是必须先定义主表，再定义从表；二是从表所参照的字段在主表中必须是主码或者候选码（有唯一约束的字段）；三是从表中对应字段的数据类型必须与主表中的相同，但字段名称并不要求相同。
如果要求某个字段在某个范围内取值，就需要使用检查（Check）约束，它可以是列级别的约束，语法为：
| 字段名称 数据类型 CHECK( 约束条件 ) |
也可以是表级别的约束，语法为：
| CHECK( 约束条件 ) |
除了以上五种标准 SQL 定义的约束，DB2 中还扩展了一些约束，比较常用的有缺省值和标识列。
缺省值（Default），又叫做默认值，用于指定某个字段的默认值，当插入数据的时候没有给出值，将使用这个默认值进行填充，它是一个列级别约束，其语法为：
| 字段名称 数据类型 DEFAULT < 默认值 > |
标识列（Identity），定义在数值型字段上的一种约束，用它可以在插入一行新记录的时候生成一个按照一定规律自动增长的值。
标识列可以有两种生成方式：GENERATED ALWAYS 和 GENERATED BY DEFAULT，下面详细介绍这两种方式的区别。
1 ． GENERATED ALWAYS
这种生成方式总是由 DB2 生成值，不能在应用程序或者 SQL 语句中直接提供值。
例 3‑20 GENERATED ALWAYS 生成的标识列。
下例说明了通过 GENERATED ALWAYS 生成的标识列如何使用。运行下例之前，首先要把自动提交的选项关闭（db2 +c）。
| CREATE TABLE inventory1 ( partno INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 100, INCREMENT BY 1), description CHAR(20) ); INSERT INTO inventory1 VALUES (DEFAULT,'door'); ---> 成功，插入了 100,door INSERT INTO inventory1 (description) VALUES ('hinge'); ---> 成功，插入了 101,hinge INSERT INTO inventory1 VALUES (102,'window'); ---> 失败 ,partno 不能插入值 COMMIT; INSERT INTO inventory1 (description) VALUES ('lock'); ---> 成功，插入了 102,lock ROLLBACK; ---> 取消了前一步操作 INSERT INTO inventory1 (description) VALUES ('frame'); ---> 成功，插入了 103,frame COMMIT; SELECT * FROM inventory1; 100 door 101 hinge 103 frame |
2 ． GENERATED BY DEFAULT
采用这种方式，如果用户没有给标识列提供值，DB2 将自动生成一个值；如果用户给标识列提供了值，DB2 将不再生成值，而是直接使用用户提供的值。使用这种方式，不能保证生成的值是唯一的。
例 3‑21 GENERATED BY DEFAULT 生成的标识列。
下例说明了通过 GENERATED BY DEFAULT 生成的标识列如何使用。运行下例之前，同样需要首先把自动提交的选项关闭（db2 +c）。
| CREATE TABLE inventory2 ( partno INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 100, INCREMENT BY 1), description CHAR(20) ); INSERT INTO inventory2 VALUES (DEFAULT,'door'); ---> 成功，插入了 100,door INSERT INTO inventory2 (description) VALUES ('hinge'); ---> 成功，插入了 101,hinge INSERT INTO inventory2 VALUES (102,'window'); ---> 成功，插入了 102,window COMMIT; INSERT INTO inventory2 (description) VALUES (102,'lock'); ---> 成功，插入了 102,lock INSERT INTO inventory2 (description) VALUES ('lock'); ---> 成功，插入了 102,lock ROLLBACK; ---> 取消了前一步操作 INSERT INTO inventory2 (description) VALUES ('frame'); ---> 成功，插入了 103,frame COMMIT; SELECT * FROM inventory2; 100 door 101 hinge 102 window 103 frame |
可以使用 IDENTITY_VAL_LOCAL() 函数来获取当前生成列的生成值，它主要是用于在从表中给对应的列插入数据，例如：
| INSERT INTO PARENT_TABLE (PK_ID, ... ) VALUES (DEFAULT, ...); INSERT INTO CHILD1_TABLE (...,FK_ID,...) VALUES (....,IDENTITY_VAL_LOCAL(),...); INSERT INTO CHILD2_TABLE (...,FK_ID,...) VALUES (....,IDENTITY_VAL_LOCAL(),...); |
其中 PARENT_TABLE 为主表，PK_ID 为主码，是标识列。而 CHILD1_TABLE 和 CHILD2_TABLE 是参照于主表的从表，其中 FK_ID 是外码，参照主表中的主码。由于外码的取值要在主码所参照的主表中出现，而主表中的主码是由标识列自动生成的，要想知道当前生成的值就需要使用 IDENTITY_VAL_LACAL() 函数。
|
|
序列（Sequence）在某些特征上与标识列很像，也可以自动生成值。但是通过序列生成值不需要依赖于表。序列比较适合于以下场合：
（1）要将从一个序列中生成的值存储到不同的表中；
（2）一个表中有多个列需要自动生成的值；
（3）生成新值的进程与任何表的引用无关。
创建序列的时候需要指出开始值、增长步长、缓存量大小以及是否循环等参数。下面结合一个例子说明。
例 3‑22 创建序列。
请解释下面创建序列语句的含义。
| CREATE SEQUENCE MySeq START WITH 1 INCREMENT BY 1 NO CYCLE CACHE 30 |
该语句创建了一个名为 MySeq 的序列，START WITH 1 表示它从 1 开始生成值，INCREMENT BY 1 表示每次增长的长度为 1，NO CYCLE 表示当该序列增长到最大值之后，就不再从头开始生成，CACHE 30 表示每次在内存中预先生成 30 个值，这样在使用的时候可以从内存中直接调出，不必在调用的时候再生成，可以提高效率。但是使用缓存技术预先生成 30 个值之后也可能存在问题，当系统因为某种原因崩溃了，数据库重新启动之后，上次在缓存中的 30 个值将不能再使用，该序列将接着使用后续的值生成序列值，这就会造成生成的序列值不连续。
序列创建好之后，有两个表达式可以得到其生成的值：
（1）“PREVVAL FOR < 序列名称 >”可以得到序列的当前值。例如：
| SELECT PREVVAL FOR MySeq FROM SYSIBM.SYSDUMMY1 |
（2）“NEXTVAL FOR < 序列名称 >”可以得到下一个生成值，而且每使用一次该表达式，序列的值就自动加 1。例如：
| INSERT INTO T1 VALUES(NEXTVAL FOR MySeq, …… ) |
标识列和序列之间存在相似之处，但是也存在差别。当设计数据库和应用程序时应该根据其各自的特征来选择使用哪种对象。
标识列具有下列特征：
（1）仅当创建了表时，才可以将标识列定义为表的一部分。一旦创建了表，就不能改变它来添加一个标识列（但是可以改变现有的标识列特征）。
（2）标识列自动为单个表生成值。
（3）当将标识列定义为 GENERATED ALWAYS 时，始终由数据库管理器生成所用的值。在修改表的内容期间，不允许应用程序来提供它们自己的值。
序列对象具有下列特征：
（1）序列对象是未与任何一个表关联的数据库对象。
（2）序列对象生成可在任何 SQL 或 XQuery 语句中使用的顺序值。
（3）由于任何应用程序可以使用序列对象，所以有两种表达式可用来控制如何检索指定序列中的下一个值和在正在执行的语句之前生成的值。对于当前会话中的先前语句，PREVVAL 表达式对指定序列返回最新生成的值。NEXTVAL 表达式对指定序列返回下一个值。使用这些表达式允许在几个表内的几个 SQL 和 XQuery 语句中使用相同值。
|
|
存储过程（Stored Procedure）是数据库中的一种程序，它可以拥有输入输出参数，可以访问和修改一个或多个表中数据。在一个存储过程内，可以以一定的逻辑（如分支和循环）包装多条 SQL 语句。存储过程可以作为对客户机应用程序或其他例程的子例程扩展。
存储过程通常用于封装复杂的应用程序逻辑，以及执行开销较大的数据库操作，例如多表连接和游标操作。
存储过程的一个显著优势是减少了对网络资源的内在需要，减少网络使用。对于一个复杂的应用，如果采用普通的客户端应用程序使用多条 SQL 反复与数据库服务器进行交流，势必要占用大量的网络带宽，而如果把业务逻辑封装在服务器这一端，把最初的参数提供给服务器，通过存储过程处理之后，得到的结果再返回给客户端应用程序，就会大大减少网络上的流量，所以存储过程对于数据库活动相对繁重而用户交互相对较少的情形是很理想的。存储过程的另外一个优势是有更高的安全性，由于公司的数据库中可能保存着机密的、敏感的数据，如果通过客户端应用程序大量访问数据库，黑客可能会反编译应用程序，通过读取数据库表中的字段得到一些有用的信息，而存储过程可以做到对过程本身代码加密，只留出输入输出参数接口，对黑客来说面对的就是一个黑盒子，通过这种方式可以提高数据库的安全性。
存储过程可以在 DB2 内部使用自带的 SQL PL 语言创建，也可以使用一些高级语言（如 C 或 Java 等）创建，本节中只介绍在 DB2 中直接创建存储过程的方法，涉及到更多存储过程的内容，请参见本系列教材的第二本《 DB2 深度解析——高级 DBA 与开发者篇》。
例 3‑23 创建存储过程。
创建一个名为 GetName 的存储过程，包括一个输入参数：职员编号（p_empno）；一个输出参数：职员姓名（p_empname），根据输入的编号返回该职员的姓名。
| CREATE PROCEDURE GetName (IN p_empno CHAR(6), OUT p_empname VARCHAR(30)) LANGUAGE SQL BEGIN DECLARE f_name VARCHAR(12); DECLARE l_name VARCHAR(15); SELECT firstnme, lastname INTO f_name, l_name FROM employee WHERE empno=p_empno; SET p_empname=f_name || ' ' || l_name; END |
首先解释一下程序段的语法。使用 CREATE PROCEDURE 命令来创建存储过程，在存储过程名称后面的括号中指定参数列表，IN 表示输入参数，OUT 表示输出参数，接下来的 LANGUAGE SQL 表示使用 DB2 的 SQL PL 语言进行存储过程的设计，在 BEGIN 和 END 之间的部分是存储过程的主程序体，程序体内前两行 DECLARE 语句声明了两个变长字符串型变量，SELECT 语句中使用 INTO 子句把查询到的结果送到这两个变量当中，最后使用 SET 语句把三个字符串合并为一个字符串，|| 是字符串连接的运算符。这样就实现了根据职员编号查询职员姓名（由 firstname 和 lastname 组合而成）的功能。
存储过程可以在命令行处理器中创建，也可以在专用的开发工具 Data Studio 中创建。对于复杂的存储过程，建议在 Data Studio 中进行创建。
在存储过程中可以使用游标（Cursor）来存储和处理 SELECT 语句返回的多行结果，游标是定义在内存中的一种特殊的临时表，能够临时存储 SELECT 语句得到的结果集，通常情况下，只能按照从上到下的顺序逐行读取游标中的数据来进行进一步的处理。对于复杂的业务逻辑，还应该有完善的错误处理机制，根据不同的错误情况返回不同的错误代码，使调用者明确错误产生的原因。
删除存储过程使用 DROP PROCEDURE 命令。
存储过程创建完成之后，在数据库中会有相应的记录，在控制中心中展开到存储过程子项，可以看到刚才创建的 GetName 过程已经在 sample 数据库中存在了，如图 3.21 所示。
图 3.21 在控制中心中查看存储过程
此时存储过程就作为一个数据库对象存在于数据库中了，使用它时需要对它进行调用，调用的命令为 CALL，下例展示了如何在命令行处理器中调用上一节创建的存储过程。
例 3 ‑24 调用存储过程。
在命令行处理器中使用 GetName 存储过程来查询职员姓名，职员编号为“000010”。
运行结果如下：
| db2 => CALL GetName('000010', ?) 输出参数的值 -------------------------- 参数名： P_EMPNAME 参数值： CHRISTINE HAAS 返回状态 = 0 |
|
|
触发器（Trigger）是一种根据数据库内容变化而自动执行的程序，它定义于一张表上，当该表中的数据发生变化的时候，如果满足了触发器中预定义的条件，就会引发触发器的执行，进而去做其他操作。
DB2 中的触发器分为前触发器、后触发器和替代触发器三种。一个触发器涉及到两个动作，一个是能够激活触发条件的数据变化，即触发器执行的原因，另一个是触发器中指定的操作，即触发器执行的结果。前触发器是先执行触发器中指定的操作，再执行引发触发器动作的操作，即先果后因。后触发器正好相反，先执行激活触发器的那个操作，再执行触发器中指定的操作，即先因后果。替代触发器是专门定义于视图之上的触发器，把原本在视图当中无法执行的操作（如对只读视图执行插入操作）替换为一系列具体可以对基本表执行的操作。
本节通过一个例子讲解前触发器和后触发器的创建。替代触发器的内容请参见信息中心以及本丛书第二本《 DB2 深度解析——高级 DBA 和开发者》。
例 3 ‑25 创建触发器。
由于经济形势不景气，央行采取一系列措施进行宏观调控，其中一个主要的手段是调整利率。假设央行的数据库中存在以下两张表：
| CREATE TABLE CurrentRate ( id CHAR(4) NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, rate DECIMAL(5,2) NOT NULL, status VARCHAR(9) ) |
该表表示当前利率，id 为编号，name 为利率种类的名称，例如“one year”表示定期一年的利率，rate 表示利率的值，精确到小数点后两位，status 表示利率的状态，可以取以下几种值：Highest 表示达到了历史上最高利率，Lowest 表示达到了历史上最低利率，Rising 表示比上次利率高，Dropping 表示比上次利率低，Steady 表示和上次相比利率稳定。
| CREATE TABLE RateHistory ( id CHAR(4) NOT NULL, name VARCHAR(20) NOT NULL, rate DECIMAL(5,2) NOT NULL, changeTime TIMESTAMP ) |
该表记录利率变化的历史信息，前三个字段的含义与 CurrentRate 表相同，最后一个字段 changeTime 表示利率调整的时间。
当进行利率调整时，会使用 UPDATE 语句对 CurrentRate 的 rate 字段进行调整，设计两个触发器，一个用于更新 CurrentRate 表的 status 字段，一个用于把新的利率信息计入到历史表中。
| CREATE TRIGGER RateStatus NO CASCADE BEFORE UPDATE OF rate ON CurrentRate REFERENCING OLD AS oldrate NEW AS newrate FOR EACH ROW MODE DB2SQL SET newrate.status= CASE WHEN newrate.rate>=(select max(rate) from ratehistory where id=newrate.id) THEN 'Highest' WHEN newrate.rate<=(select min(rate) from ratehistory where id=newrate.id) THEN 'Lowest' WHEN newrate.rate>oldrate.rate THEN 'Rising' WHEN newrate.rate<oldrate.rate THEN 'Dropping' WHEN newrate.rate=oldrate.rate THEN 'Steady' END |
在第一行指定了触发器的名称，第二行 BEFORE 指定该触发器是前触发器，也就是说，该触发器中指定的动作（SET 赋值命令以及 CASE 语句部分）会在对利率表执行 UPDATE 操作之前进行，第二行的 NO CASCADE 表示触发器不会级联（一个触发器所做的动作引发另一个触发器的执行），前触发器都不会产生级联，所以必须加上 NO CASCADE 关键字，第三行的 UPDATE OF rate ON CurrentRate 表示触发条件，即当在 CurrentRate 表的 rate 字段上执行了 UPDATE 操作，就会激活该触发器，第四行指定了旧行和新行的相关名，在触发器被激活执行的过程中，表中每一行更改之前和更改之后的数据可能都会被引用，在此指定的旧行相关名表示当前行在操作之前的值，新行的相关名表示当前行在操作之后的值，第五行指定该触发器对表中的每一行都执行一次（还有一种触发器能够对表只执行一次操作），第六行表示创建触发器使用的是 DB2 自带的 SQL PL 语言（目前版本的 DB2 只支持这一种语言编写的触发器）。
从 SET 语句开始是触发器的程序体，表示当触发器被激活时所做的操作。该语句指定了对当前行的新值中 status 字段进行重新赋值，根据不同的条件分别赋给 Highest，Lowest，Rising、Dropping 和 Steady 五种不同的值。
最后，END 表示触发器程序体结束。
| CREATE TRIGGER RecordHistory AFTER UPDATE OF rate ON CurrentRate REFERENCING NEW AS newrate FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO RateHistory VALUES (newrate.id, newrate.name, newrate.rate, current timestamp) ; END |
当执行完对利率的更新操作之后，应该将此次的更新记录到利率历史表中，采用后触发器来实现该功能。第一行指定触发器名称，第二行指定该触发器是后触发器，即当对 CurrentRate 表操作完成之后才执行此次触发器指定的操作，第三行指定触发条件是对 CurrentRate 表的 rate 字段执行 UPDATE 操作，第四行指定新行的相关名，第五行指定对表的每一行执行一次触发器规定的操作，第六行指定编写触发器程序的语言，BEGIN 和 END 之间的是程序体，执行了一个对历史表的插入操作，其中插入的最后一个字段就是当前的时间戳。
删除触发器使用 DROP TRIGGER 语句。
|
|
DB2 内部提供了大量的函数，例如求某个字段最大值的 MAX() 函数，求某个日期所在年份的 YEAR() 函数，等等。如果在使用 DB2 的过程中，需要反复使用同一个功能，就可以把这个功能定义成用户自定义函数（User Defined Function，简称 UDF）。可以使用 DB2 自带的 SQL PL 语言设计函数，也可以使用一些高级语言（如 C，C++ 和 Java 等）设计函数，并存储在 DB2 数据库中。
用户自定义函数与存储过程比较相像，都可以接收输入参数，在程序体中操作后返回输出结果。二者的不同点在于，存储过程通常与具体的业务关联比较密切，程序体内部涉及到业务逻辑，与某个业务系统内部的表密切相关，而用户自定义函数通常没有复杂的业务逻辑，也不与具体的表发生关联，通常在函数体内部所做的都是通用的操作，这种操作可以套用在任何表上。另外在调用方式上二者也有不同之处，存储过程通过 CALL 命令直接调用，而函数不能直接调用，而是在 SQL 表达式进行调用。
把使用编程语言编写的函数称为外部函数，把从另一个现有的函数改造继承过来的函数称为有源函数。
用户自定义函数分为三种类型：
（1）标量函数。返回一个单值的函数称为标量函数。例如，DB2 内置的 YEAR() 函数就是一个标量函数。标量 UDF 可以是外部函数或有源函数。
（2）列函数。从表的一列中进行统计返回单值答案的函数称为列函数，有时也称为聚集函数或聚合函数。例如，求某个字段平均值的函数 AVG()。不能对 DB2 数据库管理器定义外部列 UDF，但是可以定义源于一个内置列函数的列 UDF。
（3）表函数。返回结果是一个表的函数称为表函数，它将一个表返回至引用它的 SQL 语句，只能在 SELECT 语句的 FROM 子句中引用表函数。此类函数可用于将 SQL 语言处理能力应用于非 DB2 数据的数据，或将此类数据转换为 DB2 表。
例如，表函数可以提取一个文件并将它转换成表，将来自互联网的样本数据制成表。这些信息可以与该数据库中的其他表连接。
表函数只能是外部函数，它不能是有源函数。
很多用户自定义函数在使用的时候需要使用用户自定义数据类型（User Defined Type，简称 UDT），本节首先介绍用户自定义数据类型的创建方法，然后针对这些类型创建用户自定义函数。
用户自定义数据类型可以用于建表时的数据定义或者用户自定义函数中，它主要分为单值类型和结构化类型两种。单值类型继承自 DB2 现有的数据类型，可以对特定领域进行更具有实际意义的描述，例如可以分别定义身高、体重、年龄等类型，它们都继承自整型，但是分别表示不同的含义。结构化类型类似于 C 语言中的结构体，是由多个单值类型组合而构成的复合类型，使用的时候需要对结构化类型中的每一个分项进行设置。
例 3‑26 用户自定义数据类型、用户自定义函数的创建和使用。
创建一个人民币数据类型，并用它进行建表、插入、更新、查询等操作。
| CREATE DISTINCT TYPE RMB AS DEC(12,2) WITH COMPARISONS |
创建自定义数据类型使用 CREATE DISTINCT TYPE 语句，接着是新数据类型的名称，然后用 AS 子句指出源类型是什么。本例中指定源数据类型为 12 位宽度、2 位小数的十进制类型，WITH COMPARISONS 表示新创建的数据类型能够和具有相同源类型的值进行比较。
| CREATE TABLE teacher ( id CHAR(10) NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, salary RMB ) |
创建了一张教师表，包括编号、姓名和薪水字段，其中薪水字段就使用了新定义的人民币数据类型。
| INSERT INTO teacher VALUES('COMP000001', 'John', RMB(4026.83)) INSERT INTO teacher VALUES('MNGR000002', 'Rose', 3817.35) |
这两条插入语句都能够执行成功，严格的写法是第一种。当创建用户自定义数据类型之后，DB2 会自动提供在新类型和源类型之间的转换函数。对本例来说，RMB() 能够把一个普通的十进制类型转换为人民币类型，DECIMAL 能够把人民币类型转换为十进制类型。第二个插入语句在执行的时候，DB2 隐含地进行了类型转换。
| UPDATE teacher SET salary=salary*1.1 WHERE name='John' |
这条语句在执行的时候会失败，提示“找不到具有兼容自变量的类型为‘ FUNCTION ’的名为‘ * ’的已授权例程”，意思就是对于 RMB 数据类型，原有的乘法操作运算已经无效了。对此有两种解决方案，一种是把 salary 转换成十进制再进行乘法运算，另一种是创建一个用户自定义函数，在函数中定义对 RMB 类型的乘法运算法则。在此我们采用第一种方法：
| UPDATE teacher SET salary=DECIMAL(salary)*1.1 WHERE name='John' |
接下来进行查询：
| SELECT * FROM teacher WHERE salary>4000 |
这条查询语句也会失败，因为 RMB 类型不能和整型直接进行比较，同样地，在进行比较之前先进行类型转换：
| SELECT * FROM teacher WHERE salary>RMB(4000) |
或者
| SELECT * FROM teacher WHERE DECIMAL(salary)>4000 |
接下来我们介绍用户自定义函数的创建和使用方法。在创建了上述的 RMB 数据类型之后，需要对这种类型进行一系列新的函数定义。例如，要获取所有教师的平均薪水，采用以下语句：
| SELECT AVG(salary) FROM teacher |
同样会报错，原因是针对新定义的 RMB 数据类型，没有相对应的求平均值的函数，应该先使用如下的语句定义函数：
| CREATE FUNCTION AVG(RMB) RETURNS RMB SOURCE SYSIBM.AVG(DECIMAL) |
其中参数和返回值只需要指定数据类型即可，最后一行为函数体，指明其运算法则是采用 DB2 内部对十进制类型进行平均值计算的方法。创建成功后，再次发出查询平均值的语句：
| SELECT AVG(salary) FROM teacher |
就可以得到正确结果了。
关于用户自定义数据类型和函数更深入的知识请参照信息中心或者本丛书的第二本《 DB2 深度解析——高级 DBA 和开发者篇》。
自定义类型的删除使用 DROP DISTINCT TYPE 语句，函数的删除使用 DROP FUNCTION 语句，需要注意的是，删除自定义数据类型之前，要先删除与之相关的表。
|
|
DB2 提供了很多专用寄存器，这些寄存器记录了各种系统信息，可以像变量一样进行查询和修改（有些专用寄存器不允许修改）。可以使用 VALUES 语句查询各个专用寄存器的值：
| VALUES < 专用寄存器名称 > |
专用寄存器可以用在 SQL 语句中，有些专用寄存器的值可以修改，有些不能，常用的专用寄存器请参见表 3.3。
表 3.3 DB2 V9 中常用的专用寄存器
| 专用寄存器名称 | 作用 | 是否可更新 |
|---|---|---|
| CURRENT CLIENT_APPLNAME | 当前客户端连接的应用程序名称 | No |
| CURRENT CLIENT_USERID | 当前客户端连接的用户 ID | No |
| CURRENT CLIENT_WRKSTNNAME | 当前连接的工作站名称 | No |
| CURRENT DATE | 数据库服务器的当前日期 | No |
| CURRENT ISOLATION | 当前隔离级 | Yes |
| CURRENT LOCK TIMEOUT | 当前锁等待时间（单位为秒） | Yes |
| CURRENT QUERY OPTIMIZATION | 当前优化级别 | Yes |
| CURRENT SCHEMA | 当前模式 | Yes |
| CURRENT SERVER | 当前应用服务器 | No |
| CURRENT TIME | 数据库服务器当前时间 | No |
| CURRENT TIMESTAMP | 当前时间戳 | No |
| CURRENT TIMEZONE | 当前时区 | No |
| USER | 当前用户 | Yes |
能够修改值的专用寄存器的修改方法也不尽相同，具体修改方式请参见信息中心。
|
|
在本章当中，介绍了 DB2 中各个级别的对象。最上层的是系统，每个系统中可以包含多个实例，实例实际上是一个进程，通常在系统中把开发和测试放在不同的实例中进行。每个实例可以包含多个数据库，实例的删除并不影响数据库。数据库是实际存储数据的对象，从 V9 版本开始，创建数据库时可以指定由 DB2 进行自动存储管理，这样可以简化 DBA 对数据库、缓冲池和表空间的管理工作。系统、实例、数据库都有各自级别的配置参数。
数据库中可以包含多个缓冲池和表空间，缓冲池是内存中的一块区域，目的是减少 I/O 操作，缓冲池的命中率越高，存取数据的效率就越高。表空间是数据库中存储数据的逻辑块，真正在磁盘上存储数据的是容器，每个表空间可以包含多个容器，而一个容器只能属于一个表空间。容器可以是裸设备、目录或者文件。表空间可以分为常规、大对象、系统临时、用户临时四种，又有系统管理和数据库管理两种管理方式。为了方便 DBA 操作，从 DB2 V9 开始对表空间管理也可以设置为 AUTOMATIC，交给数据库管理器去自动管理，这种方式存取效率较高，而且简化了很多 DBA 操作，是一种比较理想的方式。通常情况下，把表中普通数据放在一个表空间，索引数据放在另一个表空间，大对象数据放到第三个表空间，可以充分利用系统资源，让多块硬盘并行工作，提高存取效率。
模式是一个逻辑上的组，能够把其他数据库对象分组进行管理，使用数据库对象的时候，也需要先指定模式名，再指定对象名。默认情况下的模式名就是连接数据库的用户名，但是也可以指定一个其他名称（不必与任何用户名称对应）作为模式名。
表空间中包含表、索引、视图等很多数据库内部对象。表用于存储实际的数据，数据库中除了最常用的基本表之外，还可以定义用户临时表，每个用户临时表只对创建它的用户是可见的，在应用程序结束的时候系统自动删除用户临时表。索引是为了提高查询效率而设置的一种对象。而视图是根据一定条件架设在基本表之上的一种虚表，本身不存储数据，可以透过视图查看基本表中的数据。
创建表的时候要指定每个字段的数据类型和约束条件，从 DB2 V9 开始除了传统的数值、字符、日期时间、空值之外，还引入了一种新的 XML 数据类型，而且提供了专门对它进行查询的 XQuery 语句。约束条件除了表中 SQL 中规定的非空、唯一、主码、外码和检查之外，还支持缺省值和标识列约束。与标识列功能相似的一种对象是序列，但是序列不依赖于表，而且不会发生并发性问题。
在数据库中有三种与程序相关的对象：存储过程、触发器和函数。存储过程类似于高级语言中的子函数或者方法，可以提供输入输出参数，在存储过程内部实现具体的业务逻辑。函数可以有多个输入参数，但最终只返回一个值，它通常只处理非常简单的事情。触发器是一种根据数据库内容变化而自动执行的程序，定义在一张表上，当表中的数据发生一定的变化时，就触发了预先定义的条件，从而去执行一些预定义的动作。通常触发器用于保证数据库中各个表之间的数据一致性。
本章最后介绍了一些常用的专用寄存器，专用寄存器用于存储一些重要的系统信息。大多数专用寄存器能够像普通变量一样进行取值和赋值。
|
|
判断题
1. DB2 的体系结构中，每台主机中可以运行多个实例。 （ ）
2. DB2 中每个实例对应于一个进程，在系统启动的时候这些进程会自动启动。 （ ）
3. 在 Windows 环境下默认的实例名为 DB2，在 UNIX 和 Linux 环境下默认的实例名为 db2inst1。 （ ）
4. 必须在实例已经启动的状态下，才能够在该实例中创建数据库。 （ ）
5. 由于数据库是在实例中创建的，所以删除一个实例之后，其包含的数据库也会一起被删除。 （ ）
选择题
1. 创建了一个十进制的数据 DEC(5,2)，下列哪个数值可以存放到这种数据类型中？ （ ）
A. 135.246 B. 2456.1 C. 2741.25 D. -1310
2. 给出如下的语句：
| CREATE TABLE t1 (col1 INT NOT NULL, PRIMARY KEY(col1)); CREATE TABLE t2 (col1 INT NOT NULL, col2 CHAR(1) NOT NULL,PRIMARY KEY (col1, col2), FOREIGN KEY (col1) REFERENCES t1 (col1) ON DELETE CASCADE ON UPDATE RESTRICT); CREATE TABLE t3 (col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (col1, col2), FOREIGN KEY (col1) REFERENCES t1 (col1) ON DELETE NO ACTION ON UPDATE RESTRICT); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1, 'a'), (1, 'b'), (2,'c'); INSERT INTO t3 VALUES (1, 100), (2, 200), (2,300); |
执行以下的 DELETE 语句之后，会删除多少行数据？ （ ）
DELETE FROM t1 WHERE col1 = 1;
A. 4 B. 3 C. 1 D. 0
3. 给出如下的存储过程：
| CREATE PROCEDURE increase_salary ( IN p_workdept CHAR(6), OUT p_sum DECIMAL(9,2) ) SET p_sum = (SELECT SUM(salary) FROM employee WHERE workdept=p_workdept); |
在命令行处理器中如何调用这个存储过程？ （ ）
A. CALL increase_salary('A00')
B. VALUES increase_salary('A00')
C. CALL increase_salary('A00', ?)
D. VALUES increase_salary('A00', ?)
4. 给出如下一系列的创建视图语句和数据插入语句：
| CREATE VIEW view1 AS SELECT col1 FROM table1 WHERE col1 > 10; CREATE VIEW view2 AS SELECT col1 FROM view1 WITH CASCADED CHECK OPTION; CREATE VIEW view3 AS SELECT col1 FROM view2 WHERE col1 < 100; INSERT INTO view1 VALUES(5); INSERT INTO view2 VALUES(5); INSERT INTO view3 VALUES(20); INSERT INTO view3 VALUES(100); |
上述 INSERT 语句能够执行成功的有几条？ （ ）
A. 0 B. 1 C. 2 D. 3
5. 数据库管理员需要在 /home/database 路径下创建一个名为 mydb 的数据库，并且想设置数据库自动运行 RUNSTATS 功能。以下哪一种方案能够花费最小的代价实现需求？ （ ）
A. CREATE DATABASE mydb on /home/database UPDATE DB CFG for mydb using AUTO_RUNSTATS ON UPDATE DB CFG for mydb using AUTO_MAINT OFF
B. CREATE DATABASE mydb on /home/database UPDATE DB CFG for mydb using AUTO_MAINT ON
C. CREATE DATABASE mydb on /home/database UPDATE DB CFG for mydb using AUTO_RUNSTATS ON UPDATE DB CFG for mydb using AUTO_MAINT ON
D. CREATE DATABASE mydb on /home/database
填空题
1. 定义了一个十进制数据 DECIMAL(11,3)，则它所占用的存储空间为 个字节。
2. 想在命令行处理器中查看当前的日期，应该使用 语句或命令。
简答题
1. 简述标识列和序列的区别。
2. 外码约束中 ON DELETE CASCADE 和 ON DELETE SET NULL 有什么区别？
3. 在图 3.17 中，如果要查找的键值为 H，会按照怎样的步骤检索？得到的结果是什么？
实验题
1. 做实验验证，删除一个 SMS 表空间，观察对应的目录是否会被一起删除？
2. 设计一个实验，使在一个控制中心中能够同时管理多个实例以及实例下的多个数据库。
学习
- 在 developerWorks Information Management 专区，了解关于信息管理的更多信息。查找技术文档、操作文章、培训、下载、产品信息等信息。
- 阅读本书的 第 1 章：DB2 概述。
- 阅读本书的 第 2 章：基本工具。
- 阅读本书的 第 3 章：DB2 中的数据对象。
- 更多推荐书籍，请访问 developerWorks 图书频道。
- 通过 Information Management 专区 DB2 9 技术资源中心和 DB2 校园讲座，了解 DB2 产品家族的更多产品信息和相关技术。
- 通过 Information Management 专区 DB2 9 认证专栏，了解 DB2 认证考试的相关资源。
- 通过 DB2 V9.7 信息中心，了解 DB2 的详细产品信息和相关技术等全面的内容。
获得产品和技术
- 现在可以免费使用 DB2 。下载 DB2 Express-C，这是为社区提供的 DB2 Express Edition 的免费版本，它提供了与 DB2 Express Edition 相同的核心数据特性，为构建和部署应用程序奠定了坚实的基础。
- 下载 信息管理软件试用版，体验它们强大的功能。
讨论
- 参与 developerWorks blog并加入 developerWorks 社区。
| ||
|
戴慰，IBM 工程师。E-mail:db2ok@163.com | |
| ||
|
张冬青，大连东软信息学院计算机科学与技术系教师。E-mail:zhangdongqing@neusoft.edu.cn | |
| ||
|
郭权，大连东软信息学院计算机科学与技术系教师。E-mail:guoquan@neusoft.edu.cn | |
如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！
赞0作者其他文章
评论 0 · 赞 0
评论 0 · 赞 0
评论 0 · 赞 1
评论 0 · 赞 0
评论 0 · 赞 0
添加新评论0 条评论