DB2 10.1 基础认证考试 610 备考教程:第 5 部分: 使用表、视图和索引

本教程将讨论 IBM® DB2® 10.1 对数据类型、表、视图、触发器、约束和索引的支持。阐明了这些对象的特性,如何使用结构化查询语言 (SQL) 创建和操作它们,以及如何在一个应用程序中使用它们。本教程是一个系列的第 5 部分,帮助您为 DB2 10.1 基础认证考试 610 做好准备。

Rob Strong, IT 架构师,认证:信息架构, IBM

Rob Strong 的照片Rob Strong 向 IBM 业务合作伙伴提供有关信息管理产品的技术咨询。他拥有 20 多年的不同平台上使用 DB2 数据库的经验,从概念到实现和支持。他的这些经验是在为 IBM 生产线创建高性能、高可用性解决方案的过程中获得的。他是一名认证的 IT 架构师。他是数据系统架构、数据建模、数据访问和应用程序中开发和维护流程方面的一位专家。



Hana Curtis, IBM 认证的解决方案专家, IBM

Hana Curtis 的照片Hana Curtis 是 IBM 多伦多软件实验室的 DB2 Continuing Engineering 团队的一员,专注于产品服务能力。之前她曾任职于 DB2 开发和质量保证部门,担任过数据库顾问,与 IBM 业务合作伙伴合作,共同实现其 DB2 应用。Hana 是 DB2 SQL Procedural Language for Linux, UNIX, and Windows (Prentice Hall, 2003) 一书的作者之一。



2013 年 8 月 12 日

开始之前

了解您期望从这个教程中学到什么,以及如何更好地掌握这些内容。

关于本系列

是否希望成为一名 IBM 认证的数据库管理员(DB2 10.1 基础认证)?如果是,那么您来对了地方。这个 DB2 10.1 基础认证备考系列 旨在探讨您参加 DB2 10.1 基础认证考试(考试 610)之前需要了解的所有主题。即使不打算马上参加认证考试,这个教程系列也可以帮助您了解 DB2 10 for z/OS® 和 DB2 10.1 for Linux®, UNIX®, and Windows® 中的许多新特性和功能。

还未看到您要找的教程?您可以回顾 DB2 9 基础认证 730 备考系列 中的 DB2 9 教程。

关于本教程

22% 的 DB2 10.1 基础认证考试(考试 610)旨在测试您对 DB2 中的表、视图和索引的结构、创建和修改的了解。这包括受支持的数据类型、各种形式的表和视图、对要输入表和视图中的值创建的限制、为提高访问速度而可以构建的索引,以及为在出现某些事件时促发其他操作而构建的触发器。

目标

本教程中的材料涵盖 DB2 10.1 基础认证考试(考试 610)第 5 部分的目标。您可以在 http://www-03.ibm.com/certify/tests/obj610.shtml 查看这些目标。

完成本教程之后,您应当能够:

  • 说明 DB2 数据类型(XML 数据类型,Oracle 兼容性数据类型)的用法。
  • 创建一个临时表。
  • 识别何时应当使用参照完整性。
  • 识别数据约束的方法。
  • 识别表、视图或索引的特征。
  • 识别应当何时使用触发器。
  • 对架构有一定的了解。

先决条件

为了理解这里给出的一些材料,您应当熟悉以下概念:

  • 数据库管理系统 (DBMS):该系统在授权用户和应用程序间提供支持数据的收集、维护、保护、共享和检索所需的核心服务。一般来说,它使用操作系统提供的设备和通信服务,由应用程序用来实现业务逻辑和控制与系统用户的交互。
  • 关系数据库管理系统:被用户视为命名表的一种数据库管理系统,其中数据以行和命名列的形式排列。
  • 结构化查询语言 (SQL):用于在关系数据库管理系统中定义对象和操作数据的一种标准化语言。
  • 专用寄存器:由 DB2 为应用程序进程定义的一个存储区域,用于存储可在 SQL 语句中引用的信息。“专用寄存器” 的示例有 CURRENT TIMESTAMP 或 CURRENT USER。

系统要求

您无需 DB2 副本即可完成本教程,但是,如果您能够访问 DB2 数据库服务器,那么就可以测试本文介绍的一些命令和概念。

您可以从 IBM 下载 DB2 Express-C 的一个赠送版本。


架构

一个架构是表、视图、触发器、函数、过程或索引等命名对象的集合。它是通过命名约定对这些对象进行逻辑分组的一种方式。它还是为若干对象使用同一自然名称的一种方式,且可预防对这些对象的二义性引用。

例如,架构名称 ‘APP1’ 和 ‘APP2’ 使我们便于区分两个不同的 SALES 表,APP1.SALES 和 APP2.SALES,两者无需拥有相同的结构。

DB2 架构不同于 XML 架构,不应将两者混淆。XML 架构是描述 XML 文档结构和验证其内容的一个标准。

如果某个对象在创建时用一个架构名称明确加以限定,就会将该对象分配给该架构。如果在创建对象时未指定任何架构名称,则根据 CURRENT SCHEMA 专用寄存器中的规定使用默认的架构名称。 SET CURRENT SCHEMA='MYSCHEMA';


DB2 中的数据包含在表中。一个表由一个或多个各种数据类型的列组成。数据组织为行,每一行使用为表指定的结构。

表是使用 CREATE TABLE SQL 语句定义的。

DB2 支持以下类型的表。

  • 系统目录表。
  • 基表,其中包含持久化数据,并且可直接查询。
  • 时态表:
    • 系统周期时态表:保存所有行的所有值的历史记录。
    • 业务周期时态表:为每一行支持有效的应用程序定义的周期。
    • 双时态表:同时吸纳系统周期和业务周期的特性。
  • 类型化表:(注意:仅适用于 Linux、UNIX 和 Windows)包含根据用户定义的结构化数据类型定义的数据。
  • 物化查询表:包含衍生自海量数据的数据,可直接查询。
  • 临时表:
    • 已创建的临时表:定义被编录并与所有用户共享,不过每个会话可轻松获得其自己的实例,不与其他会话共享数据。
    • 已声明的临时表:定义和数据都不共享
  • 克隆表:(注意:仅适用于 z/OS)支持维护高可用性、高性能的表,不可直接查询。
  • 列数据表:(注意:仅适用于 z/OS),不可直接查询。
    • 辅助表:面向各种 LOB 列数据。
    • XML 表:面向 XML 列数据。

每个 DB2 服务器(在 Linux、UNIX 和 Windows 上称为 “数据库”,在 zOS 上称为 “子系统”)拥有一组表,称为系统目录表,用于存储服务器内的对象相关信息。

注意:这些目录表的架构是 SYSCAT(在 Linux、UNIX 和 Windows 上)或 SYSIBM(在 z/OS 上),其中表也附加前缀 ‘SYS’,比如 SYSIBM.SYSTABLES。

您可以使用 SELECT 语句查看目录,就像任何其他表一样。目录表通过 CREATEALTERDROP 等数据定义语句 (DDL) 或其他操作(比如 RUNSTATS)自动加以维护。

基表

使用 CREATE TABLE SQL 语句定义一个表。清单 1 中所示的语句创建一个名为 BOOKS 的简单表,其中包含三个列:

清单 1. 清单 1. 一个包含三列的简单表
 CREATE TABLE BOOKS ( BOOKID INTEGER, BOOKNAME
                    VARCHAR(100), ISBN CHAR(10) );

您还可以使用 CREATE TABLE SQL 语句创建一个类似于另一个表或视图的表:

清单 2. 清单 2. 使用 CREATE TABLE
CREATE TABLE MYBOOKS LIKE BOOKS;

该语句创建的表包含与原始表或视图相同的列。新表的列与旧表中的列具有相同的名称、数据类型和属性为空的特性。您还可以指定复制其他属性(比如列默认值和身份属性)的子句。

CREATE TABLE 语句有诸多选项可供使用(在下面几节中介绍新概念时会进行描述)。CREATE TABLE SQL 语句的详细信息可在 SQL 参考大全中找到(参见 参考资料 部分)。

创建好表之后,有多种用数据填充它的方式。INSERT 语句允许您将一行或多行数据插入到表中。LOAD 实用程序用于加载大量数据、将行直接放到数据页面上、批量(而非逐行)维护索引,并且能够避免修改注册信息。注意:Linux、UNIX 和 Windows 提供了一个 IMPORT 实用程序来插入来自数据文件的行。它使用 INSERT 语句,且旨在加载少量数据。

存储表

表存储在表空间中。表空间拥有分配给它们的物理空间。

在创建一个表时,您可以让 DB2 将表放在默认的表空间中,或者指定您希望存储表的表空间,如清单 3 中所示。

清单 3. 清单 3. 使用默认表空间或指定表空间
CREATE TABLE BOOKS ( BOOKID INTEGER, BOOKNAME
                    VARCHAR(100), ISBN CHAR(10) ) IN BOOKINFO;

尽管这里并未详细介绍表空间,但您一定要明白,适当地定义表空间可对数据库的性能和可维护性产生重大影响。有关表空间的更多信息,请参阅本系列的第二个教程。

修改表

使用 ALTER TABLE SQL 语句更改一个表的特征。例如,您可以添加或删除:

  • 一个列。
  • 一个主键。
  • 一个或多个惟一约束或参考约束。
  • 一个或多个检查约束。

清单 4 中所示的语句添加了一个名为 BOOKTYPE 的列到 BOOKS 表。

清单 4. 清单 4. 添加 BOOKTYPE 列
ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1)

您还可以更改一个表中特定列的以下特征。

  • 一个列的身份属性。
  • 一个字符串列的长度。
  • 一个列的数据类型。
  • 一个列的可为空特性。
  • 一个列的约束。

在修改表时有以下限制。

  • 在修改一个列的数据类型时,新数据类型必须与已有数据类型兼容。例如,您可以将 CHAR 列转换为 VARCHAR 列,但不能将它们转换为 GRAPHIC 或数值列。可以将数值列转换为任何其他数值数据类型,只要新数据类型大到足以存放这些值。例如,可以将一个 INTEGER 列转换为 BIGINT,但无法将一个 DECIMAL(10,2) 列转换为 SMALLINT。
  • 在修改一个字符串列时,无法缩短长度。

清单 5 中所示的语句将 BOOKNAME 列的数据类型从 VARCHAR(100) 改为 VARCHAR(200),并将 ISBN 列的 null 属性改为 NOT NULL WITH DEFAULT 'UNKNOWN':

清单 5. 清单 5. 修改列的数据类型
ALTER TABLE BOOKS ALTER BOOKNAME SET DATA TYPE
                    VARCHAR(200) ALTER ISBN SET DEFAULT 'UNKNOWN';

表的某些特征无法修改。例如,您无法更改表所在的表空间、列的顺序,或将一个列的数据类型更改为不兼容的数据类型(参见 数据类型 一节)。要更改这样的特征,则需要先保存表数据,删除表,然后重新创建它。

删除表

DROP TABLE 语句可从数据库中删除一个表,删除数据和表定义。如果表上定义了索引或约束,这些内容也会删除。

如清单 6 中所示的 DROP TABLE 语句删除了 BOOKS 表及其数据:

清单 6. 清单 6. 删除表和数据
DROP TABLE BOOKS;

NOT NULL、DEFAULT 和 GENERATED 列选项

表的列可以使用 CREATE TABLE 语句根据一个列名和数据类型进行指定。列可以使用其他指定的子句来限制列中的数据。

默认情况下,一个列允许包含 null 值。如果您不希望支持 null 值,那么为列指定 NOT NULL 子句。可以使用 WITH DEFAULT 子句和一个默认的值指定默认值。注意,如果某个列允许使用 null,那么该列需要使用一个额外字节来存放 null 指示符。任何 null 值不等于、大于或小于任何实际值,也不等于除可在 SQL 中使用的特殊值 “NULL” 之外的其他 null 值。

如清单 7 所示,CREATE TABLE 语句创建一个名为 BOOKS 的表,其中 BOOKID 列不允许使用 null 值,且 BOOKNAME 的默认值是值为 TBD 的字符串。

清单 7. 清单 7. 使用 CREATE TABLE 语句
CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL,
                    BOOKNAME VARCHAR(100) WITH DEFAULT 'TBD', ISBN CHAR(10) );

在 BOOKS 表中,BOOKID 是分配给每本书的一个惟一号码。您可以不让应用程序生成标识符,而是指定 DB2 使用 GENERATED ALWAYS AS IDENTITY 子句生成一个 BOOKID,如清单 8 中所示。

清单 8. 清单 8. 使用 GENERATED ALWAYS AS IDENTITY 子句
 CREATE TABLE BOOKS ( BOOKID INTEGER NOT NULL
                    GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), BOOKNAME
                    VARCHAR(100) WITH DEFAULT 'TBD', ISBN CHAR(10) );

GENERATED ALWAYS AS IDENTITY 为每条记录生成一个 BOOKID。生成的第一个值是 1,后续的值在前面的值的基础上递增 1。

注意:在 DB2 for Linux, UNIX, and Windows 中,也可以使用 GENERATED ALWAYS 选项让 DB2 自动计算衍生同一行其他列的一个列值。

时态表

DB2 通过直接支持时态表来提供以简单有效的方式跟踪和查询历史、当前和计划条件的技术。

有两种时态表:系统周期时态表和业务周期时态表。

在一个系统周期时态表中,行的映像保留在一个相关的历史记录表中。时间戳由系统维护,反映每个行包含所记录的值的时间。这就给予表内容一个完整的历史视角。我们可以通过查询获取过去任何特定时间产生的结果。

在一个业务周期时态表中,时间戳的含义由业务决定,且其时间戳处于应用程序的控制之下。通常情况下,这些会反映当业务计划(或已经打算)让某些条件生效时(比如当给定利率会生效时)或当某个保单有效时的时间戳。这种表允许查询过去、现在或未来的业务条件。

这两种时态表并不惟一。DB2 允许您定义同时具有两种时态属性的表。这些表被称为双时态表。

使用 PERIOD SYSTEM_TIME 创建表需要三个步骤。

  1. 仅为当前数据创建基表,不过要包含三个 TIMESTAMP 列:两个表示系统时间的起点和终点,另一个表示事务起始时间(在内部使用),并指定 PERIOD SYSTEM_TIME。可以使用 ALTER 将这些添加到现有表中。清单 9 显示了包含这些字段的一个典型 CREATE TABLE 语句。
    清单 9. 清单 9. 为当前数据创建基表
    CREATE TABLE POLICY (                            
      POLICYID INTEGER NOT NULL PRIMARY KEY,       
      VIN VARCHAR(10) NOT NULL,                    
      COVERAGE INTEGER NOT NULL,                   
      SYS_START TIMESTAMP(12) NOT NULL             
        GENERATED ALWAYS AS ROW BEGIN,           
      SYS_END TIMESTAMP(12) NOT NULL               
        GENERATED ALWAYS AS ROW END,             
      TRAN_START TIMESTAMP(12)                     
        GENERATED ALWAYS AS TRANSACTION START ID,
      PERIOD SYSTEM_TIME (SYS_START, SYS_END) );
  2. 创建一个同结构化的历史表,这可以使用 CREATE TABLE … LIKE 语法轻松完成此操作,如清单 10 所示。
    清单 10. 清单 10. 使用 CREATE TABLE ... LIKE 语法
    CREATE TABLE POLICY_HISTORY LIKE POLICY;
  3. 修改基表以激活历史表,如清单 11 所示。
    清单 11. 清单 11. 修改基表
    ALTER TABLE POLICY ADD VERSIONING USE HISTORY TABLE POLICY_HISTORY;

正常向基表插入、更新和删除数据,但不要为三个时间戳提供值(因为总是会生成这些值),如清单 12 所示。

清单 12. 清单 12. 插入、更新和删除数据
INSERT INTO POLICY(POLICYID, VIN, COVERAGE)
  VALUES(1001,'11235811',100000);
COMMIT;
UPDATE POLICY SET COVERAGE=200000 WHERE POLICYID=1001;
COMMIT;
DELETE FROM POLICY WHERE POLICYID=1001;
COMMIT;

在更改 POLICY 表中的值的过程中,旧版的行被自动复制到 POLICY_HISTORY 中,带有适当的系统时间戳。在前三个步骤结束时(如果这些是仅有的操作且每一个都被提交),POLICY 表中不会再有任何行,不过在 POLICY_HISTORY 表中有两个独立的行,显示两组值和表明它们何时拥有这些值的时间戳。

清单 13 中所示的查询可用于查明在 2011 年 12 月 1 日下午 6 点什么保单是有效的。注意,这一 “时间旅行” 查询仅指 POLICY 表,而不显式针对 POLICY_HISTORY。有关查询和 “时间旅行” 查询的更多信息,参见第 4 部分:使用 SQL 处理 DB2 数据。

清单 13. 清单 13. 查找保单
SELECT COVERAGE FROM POLICY WHERE POLICYID = 1001 
                  FOR SYSTEM_TIME AS OF '2011-12-01-18.00.00';

创建一个具有 PERIOD BUSINESS_TIME 的表仅需一个步骤,因为不涉及单独的历史表。只需包含要由应用程序维护的两个开始/结束时间戳(或日期)以及 PERIOD BUSINESS_TIME 子句,如清单 14 所示。

清单 14. 清单 14. 创建具有 PERIOD BUSINESS_TIME 的表
CREATE TABLE POLICY (
    POLICYID INTEGER NOT NULL,
    VIN VARCHAR(10) NOT NULL,
    COVERAGE INTEGER NOT NULL,
    POLICY_START DATE NOT NULL,
    POLICY_END DATE NOT NULL,
    PERIOD BUSINESS_TIME (POLICY_START, POLICY_END),
    PRIMARY KEY(POLICYID, BUSINESS_TIME WITHOUT OVERLAPS) );

通过 WITHOUT OVERLAPS 子句,DB2 将确保给定 POLICYID 的行不会重叠时间。

物化查询表

根据其定义的 SQL 查询,物化查询表包含来自一个或多个源表的数据,就像视图一样。不过不同于一个视图,数据是以物理方式存储的。可以根据系统调度(由系统维护)或用户指示(由用户维护)刷新数据。

物化查询表对于基于大量数据的复杂查询非常有用。DB2 可以预先计算所有或部分这样的查询,并使用预计算或物化的结果更高效地回应查询。物化查询表常用于数据仓库和商业智能应用。

还可以使用 LOAD 实用程序以及 UPDATEINSERTDELETE SQL 语句来更新由用户维护的物化查询表。

物化查询表可用于提高动态 SQL 查询的性能。如果 DB2 确定可以使用物化查询表解析一个查询的一部分,那么可由 DB2 重写查询,以便使用物化查询表。

物化查询表通过 CREATE TABLE 语句创建,使用 SQL full select 和一些其他参数来控制其刷新和可用新,从而实现查询优化。

清单 15 中的 DDL 创建了一个物化查询表来包含 BOOKS 当前库存的汇总数据,要根据用户指示进行刷新。

清单 15. 清单 15. 创建由用户刷新的物化查询表
CREATE TABLE INVENTORY(BOOKTYPE,AUTHORS,TITLES,BOOKS)              
AS (SELECT BOOKTYPE,COUNT(DISTINCT AUTHORID), COUNT(DISTINCT ISBN), 
      COUNT(DISTINCT BOOKID)                                
    FROM BOOKS                                                     
    GROUP BY BOOKTYPE)                                             
DATA INITIALLY DEFERRED REFRESH DEFERRED                           
MAINTAINED BY USER;

可通过(以交互方式或从应用程序)执行清单 16 中的 SQL 语句随时替换表内的数据。

清单 16. 清单 16. 刷新物化查询表中的数据
REFRESH TABLE INVENTORY;

全局临时表

CREATE GLOBAL TEMPORARY TABLE 语句创建了对临时表的描述。从已创建临时表进行选择操作的每个会话仅检索同一会话插入的行。会话终止时,就会删除与会话相关的表的行。这使会话能够使用 SQL 对其数据执行操作,而不会引起巨大的开销。

已创建全局临时表的其余规范很像基表的规范,如清单 17 所示。

清单 17. 清单 17. 创建一个已创建的全局临时表
SET CURRENT SQLID='USER1';
CREATE GLOBAL TEMPORARY TABLE LIBRARY.TOBECATALOGED (
     BOOKNAME VARCHAR(100),
     AUTHORNAME VARCHAR(100)
     );
GRANT ALL ON LIBRARY.TOBECATALOGED TO USER2, USER3, USER4;

只有 USER2、USER3 和 USER4 能够使用 LIBRARY.TOBECATALOGED 的临时实例。当 USER2 提到 LIBRARY.TOBECATALOGED 时,该实例就会出现,并且仅能由 USER2 从 USER2 的当前会话中访问。该实例及其数据只保留到 USER2 执行 COMMIT 时(这是默认设置)。另请注意,USER1 的 GRANT 必须是 GRANT ALL。USER2 不必显式创建表实例,只是使用它。示例如清单 18 所示。

清单 18. 清单 18. 隐式创建已创建全局临时表的实例
SET CURRENT SQLID='USER2';
INSERT INTO LIBRARY.TOBECATALOGED VALUES('This Book Title', 'Ann Author');
INSERT INTO LIBRARY.TOBECATALOGED VALUES('Remember This Also', 'Another Author');
SELECT COUNT(*) FROM LIBRARY.TOBECATALOGED;
COMMIT;
SELECT COUNT(*) FROM LIBRARY.TOBECATALOGED;

LIBRARY.TOBECATALOGED 的所有实例都将具有相同的结构,即系统目录中定义的结构。清单 18 中的第一个 SELECT 会返回值 2。第二个 SELECT 会返回值 0,因为 LIBRARY.TOBECATALOGED 的定义接受了这样的默认行为,即数据仅会保留到执行 COMMIT 时。

全局临时表的第二种形式是已声明的全局临时表。该表仅可用于会话(与已创建的全局临时表一样),且放入该表的数据仅可在单一会话内使用,直至执行 COMMIT。

已声明的全局临时表和已创建的全局临时表之间的主要区别在于,已声明的全局临时表的结构没有经过预定义,而是在同一会话内定义。两个不同的会话中的每一个都可以有自己的表版本(具有不同结构),使用与另一个会话完全相同的名称。清单 19 展示了如何创建和使用已声明的全局临时表。

清单 19. 清单 19. 创建和使用已声明的全局临时表
SET CURRENT SQLID='USER2';
DECLARE GLOBAL TEMPORARY TABLE TOBECATALOGED (
     BOOKNAME VARCHAR(100),
     AUTHORNAME VARCHAR(100)
     );
INSERT INTO SESSION.TOBECATALOGED VALUES('A New Book', 'A Different Author');
SELECT COUNT(*) FROM SESSION.TOBECATALOGED;
COMMIT;
SELECT COUNT(*) FROM SESSION.TOBECATALOGED;

清单 19 中的第一个 SELECT 会获取值 1。第二个 SELECT 会获取值 0,因为两种全局临时表的默认行为都是在执行 Commit 时删除数据。

请注意,所有已声明的全局临时表的架构都将是 ‘SESSION’。已声明的全局临时表的结构没有寄存到系统目录中,因此可以在多个不同的会话中使用相同的表名称,每一个都具有不同的结构。

另请注意,对已声明的全局临时表的所有后续引用都应包含 ‘SESSION’ 架构,因为如果忽略这一点,DB2 会使用专用寄存器 CURRENT SQLID 的值解析表的全名。


数据类型

DB2 提供了一套丰富而灵活的数据类型。DB2 自带基本数据类型,比如 INTEGER、CHAR 和 DATE。它还包括用于创建用户定义的数据类型 (UDT) 的工具,因此您可以创建适应如今复杂的编程环境的复杂的非传统数据类型,或者更准确地说,是基于内置数据类型定义的类型。既定情况下的数据类型选择取决于存储在列中的信息的类型和范围。

有四种内置数据类型:数值、字符串、日期时间和 XML。请注意,在 z/OS 上有一个 ROWID 数据类型,而在 Linux、UNIX 和 Windows 上有一个 BOOLEAN。

用户定义的数据类型分类为:单值类型、结构化类型和引用类型。

数值数据类型

有四种数值数据类型:整数、小数、浮点数和十进制浮点数。这些类型在其可以存储的数值数据的范围和精度上各异。

  • 整数:SMALLINT、INTEGER 和 BIGINT 用于存储整数。例如,库存数量可以定义为 INTEGER。SMALLINT 可以在 4 个字节中存储从 -2,147,483,648 到 2,147,483,647 的整数。BIGINT 可以在 8 个字节中存储从 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 的整数。
  • 小数:DECIMAL 用来存储有最高达 31 位的小数部分的数字。要定义这个数据类型,则需要指定精度(p,表示总的位数)和小数位(s,表示小数点右边的位数)。定义为 DECIMAL(10,2) 的列可以保存的最高金额可为 99999999.99。数据库中所需的存储量取决于精度,按照公式 p/2 +1 计算。所以 DECIMAL(10,2) 需要 10/2 + 1 (即 6)字节。
  • 浮点数:REAL 和 DOUBLE 用于存储数字的近似值。例如,非常小或非常大的科学计量值可以定义为 REAL。REAL 可以定义为具有 1 到 24 位之间的长度,需要 4 字节的存储空间。DOUBLE 可以定义为具有 25 到 53 位之间的长度,需要 8 字节的存储空间。根据指定的精度,FLOAT 可以视为 REAL 或 DOUBLE 的同义词。
  • 十进制浮点数:DECFLOAT 为浮点数实现 IEEE 标准 754r,精度为 16 或 34 位。根据精度,DECFLOAT 可占据 8 或 16 个字节。

字符串数据类型

DB2 提供若干数据类型来存储字符数据或字符串。基于您要存储的字符串的大小和字符串中的数据选择一个数据类型。

以下数据类型用于存储单字节字符串。

  • CHAR 或 CHARACTER 用来存储最多 254 个字节(注意:在 z/OS 上是 255 字节)的固定长度的字符串。例如,制造商可以给零件分配一个 8 字符长度的标识符,因此这些标识符在数据库中存储为 CHAR(8) 类型的列。
  • VARCHAR 用来存储可变长度的字符串。例如,制造商用不同长度的标识符表示大量零件,因此这些标识符存储为 VARCHAR(100) 类型的列。VARCHAR 列的最大长度为 32,672 字节。在数据库中,VARCHAR 数据只占用恰好所需的空间,此外,长度占用了 2 个字节。

以下数据类型用于存储多字节字符串。

  • GRAPHIC 用来存储固定长度的多字节字符串。GRAPHIC 列的最大长度是 127 个字符。
  • VARGRAPHIC 用来存储可变长度的多字节字符串。VARGRAPHIC 列的最大长度是大约 16,000 个字符。

DB2 还提供了存储非常长的字符串数据的数据类型。所有长字符串数据类型都具有相似的特征。首先,在数据库中此数据在物理上并没有与行数据存储在一起,这意味着需要进行额外处理才能访问该数据。长数据类型的长度最大可以定义为 2GB-1。但是,所需的空间只是实际使用的空间。长数据类型如下所示。

  • CLOB(字符大对象)。
  • DBCLOB(双字节字符大对象)。
  • BLOB(二进制大对象),不应应用字符代码转换(例如图片)的 LOB。

日期时间数据类型

DB2 提供了以下三种存储日期和时间的数据类型。

  • DATE
  • TIME
  • TIMESTAMP

这些数据类型的值在数据库中存储为一种内部格式;但是应用程序可以将其作为字符串操作。在检索这些数据类型之一时,以字符串的形式表示它们。在更新这些数据类型时,将值放在引号中。

DB2 提供了操作日期时间值的内置函数。例如,可以使用 DAYOFWEEK 或 DAYNAME 函数判断某个日期值是星期几。使用 DAYS 函数计算两个日期之间相差多少天。DB2 还提供了专用寄存器以根据时间-日期时钟生成当前日期、时间或时间戳。例如,CURRENT DATE 返回一个表示系统上的当前日期的字符串。

日期和时间值的格式取决于在创建数据库时指定的数据库国家编码。有几种可用的格式:ISO、USA、EUR 和 JIS。例如,如果数据库使用 USA 格式,那么日期值的格式是 mm/dd/yyyy。在创建应用程序时,可以使用 BIND 命令的 DATETIME 选项改变格式。

时间戳的字符串表示是 yyyy-mm-dd-hh.mm.ss.nnnnnn。

XML 数据类型

DB2 提供了 XML 数据类型来存储格式良好的 XML 文档。XML 列中的值存储为与字符串数据类型不同的内部表示。要在 XML 数据类型的列中存储 XML 数据,需要使用 XMLPARSE 函数对数据进行转换。可以使用 XMLSERIALIZE 函数将 XML 数据类型的值转换为 XML 文档的串行化字符串值。DB2 还提供了许多其他的内置函数来操作 XML 数据类型。

用户定义的数据类型

DB2 允许用户定义适合自己应用程序的数据类型。

用户定义的单值类型:基于内置类型定义新的数据类型。这个新类型具有该内置类型相同的功能,但可以使用它确保只比较相同类型的值。例如,您可以定义一个加元类型 (CANDOL) 和一个美元类型 (USADOL),两者均基于 DECIMAL(10,2)。两个类型都基于相同的内置类型,不过您不能比较它们,除非应用一个转换函数。以下 CREATE TYPE 语句创建 CANDOL 和 USADOL UDT,如清单 20 中所示。

清单 20. 清单 20. CREATE TYPE 语句
CREATE DISTINCT TYPE CANDOL AS DECIMAL(10,2) WITH COMPARISONS;
CREATE DISTINCT TYPE USADOL AS DECIMAL(10,2) WITH COMPARISONS;

DB2 自动生成执行基本类型与单值类型之间的转换的函数,并使用这些函数来比较单值类型实例的比较运算符。以下语句展示了如何创建一个具有 CANDOL 类型列的表,然后使用 CANDOL 转换函数将数据插入表中,如清单 21 所示。

清单 21. 清单 21. CANDOL 转换函数
CREATE TABLE ITEMS (ITEMID CHAR(5), PRICE CANDOL);
INSERT INTO ITEMS VALUES('ABC11',CANDOL(30.50) );

此外,在 Linux、UNIX 和 Windows 平台上您可以创建以下类型。

  • 用户定义的数组:数组类型被定义为包含另一个数据类型的元素的数组。
  • 用户定义的指针:指针类型表示对底层指针的一个引用。
  • 用户定义的行:行类型是可由 SQL PL 用作变量和参数的数据类型的一个结构,用于表示简单操作的一个行。它还可用作一个数组的数据类型。
  • 用户定义的结构化类型:该结构化类型有一个包含一系列属性的结构,其中每个属性都有一个数据类型。结构化类型可以用作表、视图或列的类型。例如,可以创建一种名为 ADDRESS 的结构化类型,它包含表示街道号码、街道名、城市之类的数据。然后在定义其他表(如职工表或者供应商表)时使用这种类型,因为这些表也需要同样的数据。另外,结构化类型在一个层次结构中可以有子类型。这样便可以存储属于一个层次结构的对象。
  • 用户定义的引用类型:在使用结构化类型时,可以使用引用类型定义对另一个表中行的引用。这些引用看起来与参照约束相似,然而,它们不强制表之间存在关系。表中的引用允许用不同的方法指定查询。

DB2 扩展器

DB2 扩展器支持复杂的、非传统的数据类型。它们与 DB2 服务器代码分开打包,且安装在使用扩展器提供的数据类型和方法的每个 DB2 实例中。

IBM 和独立软件供应商提供许多 DB2 扩展器。DB2 Spatial Extender 可用于存储和分析空间数据。DB2 Net Search Extender 支持用户和应用程序员使用 SQL 查询搜索存储在 DB2 数据库、其他数据库和文件系统中的全文文档。

DB2 扩展器是用用户定义类型和用户定义函数 (UDF) 的特性实现的。每个扩展器提供一个或多个 UDT、用于操作 UDT 的 UDF 和特定的应用程序编程接口 (API),或许还提供其他工具。


约束

DB2 提供若干种方式来控制什么数据可以存储在一个列中。这些特性称为数据库管理员对一个数据列或一组数据列施加的约束或规则。

DB2 提供三种约束:惟一性约束,参照完整性约束和检查约束。

以下几节对每种类型的约束提供详细的描述。

惟一性约束

惟一性约束用于确保一个列中的值是惟一的。可以通过一个或多个列定义惟一性约束。惟一性约束中包含的每个列都必须定义为 NOT NULL。

可以将惟一性约束定义为 PRIMARY KEYUNIQUE 约束。在使用 CREATE TABLE SQL 语句创建表或使用 ALTER TABLE 语句在创建表之后添加表时,会定义这些约束。

何时定义 PRIMARY KEY,何时定义 UNIQUE 键?这取决于数据的性质。一般来说,一个好的实践是对每个表定义一个主键(如果可行的话)。在前面的示例中,BOOKS 表有一个 BOOKID 列,用于惟一标识一本书,因此将其定义为主键。该值在包含与本书相关信息的其他表中也会用到。DB2 仅允许在一个表上定义一个主键。

ISBN 号码列需要是惟一的,但不是一个可在其他表中使用的值。在这种情况下,可以将 ISBN 列定义为 UNIQUE,如清单 22 中所示。

清单 22. 清单 22. 定义一个 UNIQUE 键
CREATE TABLE BOOKS (
    BOOKID INTEGER NOT NULL PRIMARY KEY,
    BOOKNAME VARCHAR(100),
    ISBN CHAR(10) NOT NULL 
        CONSTRAINT BOOKSISBN UNIQUE);

CONSTRAINT 关键字允许您为约束指定一个名称。在这个示例中,惟一性约束的名称是 BOOKSISBN。如果您希望删除某个约束,可以在 ALTER TABLE 语句中使用此名称。

DB2 只允许您在一个表上定义一个主键;不过您可以定义多个惟一性约束。

每当您在一个列上定义 PRIMARY KEYUNIQUE 约束时,DB2 就会创建一个惟一的索引,强制限定该列的惟一性。DB2 不允许您创建在相同的列上定义的多个惟一索引。因此,您无法对相同的列定义一个 PRIMARY KEYUNIQUE 约束。例如,针对 BOOKS 表的以下语句的执行都会失败,因为已经存在一个 PRIMARY KEY 了,如清单 23 中所示。

清单 23. 清单 23. 因存在 PRIMARY KEY 而执行失败的语句
ALTER TABLE BOOKS ADD CONSTRAINT UNIQUE (BOOKID);
CREATE UNIQUE INDEX IBOOKS ON BOOKS (BOOKID);

参考完整性约束

参照完整性约束用于定义表之间的关系,并确保这些关系持续有效。

假设您有一个存储作者信息的表,还有一个列出该作者所著图书的表。BOOKS 表与 AUTHORS 表之间有一个关系,其中每本书都有一个作者,该作者必须存在于 AUTHOR 表中。每个作者都有一个惟一标识符存储在 AUTHORID 列中。AUTHORID 在 BOOKS 表中用于识别每本书的作者。要定义这一关系,请将 AUTHORS 表的 AUTHORID 列定义为主键,然后在 BOOKS 表上定义一个外键,以建立与 AUTHORS 表中 AUTHORID 列的关系,如清单 24 所示。

清单 24. 清单 24. 建立与 AUTHORS 表中 AUTHORID 列的关系
CREATE TABLE AUTHORS (
    AUTHORID INTEGER NOT NULL PRIMARY KEY,
    LNAME VARCHAR(100),
    FNAME VARCHAR(100));
CREATE TABLE BOOKS (
    BOOKID INTEGER NOT NULL PRIMARY KEY,
    BOOKNAME VARCHAR(100),
    ISBN CHAR(10),
    AUTHORID INTEGER REFERENCES AUTHORS);

AUTHOR 表有一个主键在名为 BOOKS 的另一个表中被提及。AUTHOR 在表之间的关系中称为父表,BOOKS 是表之间的关系中的从属表。

在创建从属表时可以添加约束,或者可以稍后修改从属表来添加约束。

您还可以定义同一表中行之间的关系。在这种情况下,表关系的父表和从属表是一样的,如清单 25 所示。

清单 25. 清单 25. 定义行之间的关系
CREATE TABLE EMPLOYEE (
    PERSONID CHAR(10) NOT NULL PRIMARY KEY,
    MANAGER CHAR(10) REFERENCES EMPLOYEE);

类似地,如果您添加了一个 FIRSTBOOK 列到 AUTHORS 表,两个表之间可能就有第二种关系,其中角色是互换的,因为会在 AUTHORS 内为每个作者提供一个特定的 BOOKID 作为其 FIRSTBOOK。在该关系中,BOOKS 表将是父表,如清单 26 所示。

清单 26. 清单 26. 使用 BOOKS 表作为父表
ALTER TABLE AUTHORS ADD FIRSTBOOK INTEGER REFERENCES BOOKS;

在一组表上定义参考约束时,对表执行更新操作时,DB2 对这些表强制实施参考完整性约束。

  • DB2 确保只将有效数据插入到定义了参考完整性约束的列中。这意味着,在父表中必须总是有一个行的键值等于您插入到从属表的行中的外键值。例如,如果将一本新书插入 BOOKS 表,其 AUTHORID 为 437,那么在 AUTHORS 表中必须总有一行的 AUTHORID 是 437。
  • 当从父表删除一个在属表中拥有从属行的行时,DB2 也实施了相应规则。DB2 采取的操作取决于在表上定义的删除规则。可以指定 4 个规则:RESTRICT,NO ACTION,CASCADE 和 SET NULL。
  • 如果指定了 RESTRICT 或 NO ACTION,DB2 不允许删除父行。属表中的行必须先于父表中的行被删除。
  • 如果指定了 CASCADE,那么从父表中删除某个行时也会自动删除所有从属表中的从属行。
  • 如果指定了 SET NULL,那么会从父表中删除父行,并将从属行中的外键值设为 null(如果可以为空)。
  • 在更新父表中的键值时,有两个规则可以指定:RESTRICT 和 NO ACTION。如果一个从属表中博阿含从属行,那么 RESTRICT 不允许更新键值。如果在更新完成后,某个从属表包含从属行,而该从属行在父表中没有父键,那么 NO ACTION 会导致对父键值执行的更新操作遭到拒绝。

检查约束

检查约束用于验证列数据没有违反为列定义的规则。DB2 确保在插入和更新过程中不违反该约束。

假设您向 BOOKS 表添加一个图书类型列,那么您要考虑的值是 F (fiction) 和 N (nonfiction)。您可以添加一个具有检查约束的 BOOKTYPE 列,如清单 27 所示。

清单 27. 清单 27. 添加一个具有检查约束的 BOOKTYPE 列
ALTER TABLE BOOKS ADD BOOKTYPE CHAR(1) CHECK (BOOKTYPE IN ('F','N') );

在创建表或使用 ALTER TABLE SQL 语句后,您可以在添加它们时定义检查约束。您可以修改检查约束,方法就是删除它们,然后使用 ALTER TABLE SQL 语句重新创建它们。


视图

视图支持不同的用户或应用程序以不同的方式查看相同的数据。这不仅让数据更易于访问,而且可用于限制允许用户查看或更新的行和列。运行 SELECT 语句会产生一个表。视图实际上就是一个存储查询的名称,您可以像使用表一样使用它。

例如,假设一家公司有一个表,其中包含有关其职工的信息。一名经理需要查看其职工的地址、电话号码和薪资信息,而一个目录应用程序需要查看公司中的所有职工以及其地址和电话号码,但不需要查看其薪资信息。您可以创建一个视图,显示某个部门职工的所有信息,再创建一个视图,仅显示所有职工的姓名、地址和电话号码。

对于用户来说,视图就像是一个表。除视图定义外,视图不会占用空间,也不会存储自己的数据;视图中显示的数据源自其他表。您可以基于现有表、其他视图或两者的结合创建一个视图。在另一个视图上定义的视图称为嵌套视图。

您可以定义一个列名不同于基表对应列名的视图。您还可以定义视图来确认插入或更新的视图是否保持在视图条件之内。

定义的视图列表存储在 VIEWS 系统目录表中。(注意:在 z/OS 上,系统目录表始于 SYSIBM.SYS。)系统目录表 VIEWDEP 针对每个视图有一些行,指明构建该视图所基于的每个视图或表。另外,每个视图在 TABLES 系统目录表中有一个条目,在 COLUMNS 中有一些条目(因为视图使用起来通常与表一样)。

创建视图

CREATE VIEW SQL 语句用于定义一个视图。SELECT 语句用于指定在视图中显示哪些行和列。

例如,假设您想创建一个仅显示 BOOKS 表中的非小说类图书的视图,如清单 28 所示。

清单 28. 清单 28. NONFICTIONBOOKS 视图
CREATE VIEW NONFICTIONBOOKS AS 
    SELECT * FROM BOOKS WHERE BOOKTYPE = 'N';

定义该视图之后,在系统目录表中有该视图相关条目,但不另行存储数据,因为一个视图内的数据源自其他表或视图。

要在视图中定义不同于基表的列名,您可以在 CREATE VIEW 语句中指定它们。清单 29 中所示的语句创建一个包含两个列的 MYBOOKVIEW 视图:TITLE 表示 BOOKNAME 列,TYPE 表示 BOOKTYPE 列。

清单 29. 清单 29. 包含两个列的 MYBOOKVIEW
CREATE VIEW MYBOOKVIEW (TITLE,TYPE) AS 
    SELECT BOOKNAME,BOOKTYPE FROM BOOKS;

DROP VIEW SQL 语句用于删除视图。注意,对于 Linux、UNIX 和 Windows,如果删除一个表或一个视图所基于的另一个视图,从属视图在系统目录中仍有定义,但不发生效力。VIEWS 目录表的 VALID 列指出了某个视图是有效 (Y) 还是无效 (X)。在 z/OS 上,删除基础表或视图时,也会删除从属视图。

您可以通过以下代码删除 NONFICTIONBOOKS 视图:DROP VIEW NONFICTIONBOOKS;

您无法修改一个视图。要更改视图定义,则需要删除该视图,然后重新创建它。可以使用仅用于修改引用类型的 ALTER VIEW 语句。

只读和可更新的视图

在创建一个视图时,由于其结构特征,它可能是一个只读视图,也可能是一个可更新视图。一个视图的 SELECT 语句决定视图是只读还是可更新。一般来说,如果可以将一个视图的行映射到基表的行,那么视图是可更新的。例如,在前一个示例中定义的 NONFICTIONBOOKS 视图是可更新的,因为视图中的每一行是基表中的一个行。

确定一个视图是否可更新的规则很复杂,取决于定义中的 SELECT 语句。例如,使用 VALUES、DISTINCT 或任何形式联接的视图是可以直接更新的。您只需查看 VIEWS 系统目录表的 READONLY 列就可以轻松确定一个视图是否可更新:Y 表示它是只读的,而 N 表示它是可更新的。

创建可更新视图的详细规则记录在 DB2 SQL 参考大全中(参见 参考资料 部分)。

有一个允许通过只读视图更新数据的机制:INSTEAD OF 触发器。可以在一个视图上定义这些触发器来阻截对视图执行的 UPDATE、INSERT 和 DELETE 操作,而是对其他表执行操作,通常是构建视图所基于的基表。

从一个表中选择数据子集的可更新视图可以插入没有包含在该子集中的数据。例如,前面定义的 NONFICTIONBOOKS 视图仅包含 BOOKTYPE 为 ‘N’ 的行。如果您将 BOOKTYPE 为 ‘F’ 的行插到视图中,DB2 将该行插入基表 BOOKS 中。不过,如果您从视图中进行选择,则无法通过视图看到新插入的行。如果您不希望让用户插入视图范围以外的行,那么可以定义包含检查选项的视图。使用 WITH CHECK OPTION 定义一个视图,这会告诉 DB2 检查使用该视图的语句,从而导致产生满足视图条件的数据,如清单 30 所示。

清单 30. 清单 30. 使用 WITH CHECK OPTION 的视图
CREATE VIEW NONFICTIONBOOKS AS 
    SELECT * FROM BOOKS WHERE BOOKTYPE = 'N' 
    WITH CHECK OPTION;

该视图仍然限制用户查看非小说类图书。此外,它阻止用户插入值不为 N 的行到 BOOKTYPE 列,并将现有行中 BOOKTYPE 列的值更新为 N 以外的值。例如,清单 31 中所示的语句不再受支持。

清单 31. 清单 31. 不允许的语句
INSERT INTO NONFICTIONBOOKS VALUES (...,'F');
UPDATE NONFICTIONBOOKS SET BOOKTYPE = 'F' WHERE BOOKID = 111;

带有检查选项的嵌套视图

基于另一个视图构建的视图就是嵌套视图。定义嵌套视图时,可以使用检查选项限制操作。但是,您可以指定其他子句来定义继承限制的方式。可以将检查选项定义为 CASCADED 或 LOCAL。

如果不指定关键词,那么 CASCADED 将会是默认的检查选项。许多场景说明了 CASCADED 与 LOCAL 的行为之间的差别。

使用 WITH CASCADED CHECK OPTION 创建一个视图时,对视图执行的所有语句必须满足视图和所有基础视图的条件,即使没有使用检查选项定义那些视图。假设 NONFICTIONBOOKS 视图是在未使用检查选项的情况下创建的,您还可以使用 CASCADED 关键词基于 NONFICTIONBOOKS 视图创建 NONFICTIONBOOKS1 视图,如清单 32 所示。

清单 32. 清单 32. 在不使用检查选项的情况下创建的视图
CREATE VIEW NONFICTIONBOOKS AS 
    SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'; 
CREATE VIEW NONFICTIONBOOKS1 AS 
    SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100 WITH
    CASCADED CHECK OPTION;

清单 33 中所示的 INSERT 语句将不受支持,因为它们不满足至少其中一个视图的条件。

清单 33. 清单 33. 不允许使用的 INSERT 语句
INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'N');
INSERT INTO NONFICTIONBOOKS1 VALUES(120,..,'F'); 
INSERT INTO NONFICTIONBOOKS1 VALUES( 10,..,'F');

但以下 INSERT 语句会受到支持,因为它同时满足了两个视图的条件。
INSERT INTO NONFICTIONBOOKS1 VALUES(120,...,'N');

接下来,假设您使用 WITH LOCAL CHECK OPTION 基于 NONFICTIONBOOKS 视图创建 NONFICTIONBOOKS2 视图。现在,对视图执行的语句仅需满足指定了检查选项的视图的条件,如清单 34 所示。

清单 34. 清单 34. 满足指定了检查选项的视图的条件
CREATE VIEW NONFICTIONBOOKS AS
    SELECT * FROM BOOKS WHERE BOOKTYPE = 'N'; 
CREATE VIEW NONFICTIONBOOKS2 AS 
    SELECT * FROM NONFICTIONBOOKS WHERE BOOKID > 100
    WITH LOCAL CHECK OPTION;

在这种情况下,清单 35 中所示的 INSERT 语句将不受支持,因为它们不满足 NONFICTIONBOOKS2 视图的 BOOKID > 100 条件。

清单 35. 清单 35. 不允许使用的 INSERT 语句
INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'N');
INSERT INTO NONFICTIONBOOKS2 VALUES(10,..,'F');

然而,清单 36 中的 INSERT 语句将受到支持,即使值 N 不满足 NONFICTIONBOOKS 视图的 BOOKTYPE = 'N' 条件。

清单 36. 清单 36. 允许使用的 INSERT 语句
INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'N');
INSERT INTO NONFICTIONBOOKS2 VALUES(120,..,'F');

索引

索引是表的一个列或多个列的键值的一个有序列表。有两个原因说明了为什么要创建索引。

  • 确保一个列或多个列中值的惟一性。
  • 提高表查询性能。在执行查询时 DB2 优化器通过使用索引最大限度地减少要搜索的行数来提高性能。优化器还可以消除排序,以受索引支持的顺序显示查询结果。

可以将索引定义为惟一或非惟一索引。非惟一索引允许使用重复的键值,而惟一索引只允许一个键值在列表中出现一次。惟一索引允许显示一个 null 值。但是,第二个 null 值会导致重复问题,因此不受支持。注意:z/OS 支持 UNIQUE WHERE NOT NULL 索引,允许多个行具有 null 值。

索引是使用 CREATE INDEX SQL 语句创建的。为了支持 PRIMARY KEYUNIQUE 约束,还可以隐式地创建索引。在隐式创建一个索引时,会检查键数据的惟一性,而且如果找到重复数据或任何 null 值,操作会失败。

创建索引时可以根据升序或降序排列各个字段。您选择的选项取决于应用程序访问数据的方式。

创建索引

在示例中,在 BOOKID 列有一个主键。通常用户会对书名执行搜索,因此为 BOOKNAME 设置索引非常适合。以下语句在 BOOKNAME 列上创建了一个按升序排列的非惟一索引: CREATE INDEX IBOOKNAME ON BOOKS (BOOKNAME);

索引名 IBOOKNAME 用于创建和删除索引。除此之外,在查询或表更新中不使用该名称。

默认情况下,创建的索引是按升序排列的,不过您可以使用 ASCENDINGDECENDING 指定一个索引的每个列。以下语句在 AUTHORID 和 BOOKNAME 列中定义了索引。AUTHORID 列的值以降序排列,而同一 AUTHORID 内的 BOOKNAME 列是按升序进行排列的,如清单 37 所示。

清单 37. 清单 37. 排序
CREATE INDEX I2BOOKNAME ON BOOKS (AUTHORID DESC, BOOKNAME ASC);

创建一个索引时,键是以指定的顺序存储的。索引有助于提升所需数据按照指定顺序排列的查询的性能。例如,可以使用一个升序键来确定 MIN 列函数的结果。

DB2 不允许创建具有同一定义的多个索引。这甚至适用于为支持主键或惟一性约束而隐式创建的索引。因为 BOOKS 表已经具有在 BOOKID 列定义的一个主键,所以在 BOOKID 列中尝试创建索引会失败。

创建一个索引需要很长的时间。DB2 读取每一行来提取键,然后为这些键排序将列表写到数据库。如果表比较大,那么会使用临时表空间对键进行排序。

索引存储在表空间中。注意:Linux、UNIX 和 Windows 将索引分为一个独立的表空间,然后在您创建表时使用 INDEXES IN 子句识别该表空间。在创建表时会设置一个表的索引的位置,在删除并重新创建表之前,无法修改该位置。

DB2 还提供 DROP INDEX SQL 语句来从数据库中删除一个索引。无法修改一个索引。例如,如果需要修改一个索引(向键添加另一列),则必须删除该索引,然后重新创建它。

聚类索引

您可以在每个表上创建一个索引作为聚类索引。当以特定顺序引用表数据时,聚类索引通常很有用。聚类索引定义数据在数据库中的存储顺序。在插入操作期间,DB2 试图将新行放到包含类似键的行的附近。然后在需要按照聚类索引顺序排列数据的查询中,可以更快地检索数据。

要将一个索引创建为聚类索引,请在 CREATE INDEX 语句上指定 CLUSTER 子句,如清单 38 所示。

清单 38. 清单 38. 聚类索引
CREATE INDEX IAUTHBKNAME ON BOOKS (AUTHORID,BOOKNAME) CLUSTER;

该语句在 AUTHORID 和 BOOKNAME 列上创建一个索引作为聚类索引。该索引会提高为列出作者及其所著所有图书而编写的查询的性能。

在索引中使用包含列

在创建一个索引时,您可以选择包括额外的列数据,该数据存储在键中,但实际上它并非键本身的一部分,且没有经过排序。在一个索引中包含附加列的主要原因是要提高某些查询的性能。因为该数据已经存在于索引页面,DB2 无需访问数据页面来获取它。只能为惟一索引定义包含列。但是,在保证索引的惟一性时,不考虑包含列。(注意:z/OS 允许在一个索引中包含一个基于表列的表达式。)

假定您常常需要按 BOOKID 排序获取书名列表。查询应当如清单 39 所示。

清单 39. 清单 39. 按 BOOKID 排序查询图书
SELECT BOOKID,BOOKNAME FROM BOOK ORDER BY BOOKID;

您可以创建一个可能会提高性能的索引,如清单 40 所示。

清单 40. 清单 40. 用于提高性能的索引
CREATE UNIQUE INDEX IBOOKID ON BOOKS (BOOKID) INCLUDE(BOOKNAME);

因此,查询结果所需的所有数据都显示在索引中,不需要检索任何数据页面。

那么为何不将所有数据包含在索引中?首先,这将需要数据库中有更多物理空间,因为表数据会在索引中被复制。其次,每当更新数据值时,所有数据副本都需要更新,这会给时常更新的数据库带来巨大的开销。

我应当创建哪些索引?

在创建索引时要考虑以下几个方面。

  • 由于索引是键值的一个永久列表,所以它们需要在数据库中有足够的空间。创建许多索引需要您的数据库中有额外的存储空间。所需的空间量由键列的长度决定。DB2 提供一个工具来帮助您估算一个索引的大小。
  • 索引是值的额外副本,因此,在通过 INSERT、UPDATE 和 DELETE 更改表数据时,必须维护这些副本。如果表数据频繁发生变更,请考虑维护额外的索引会对性能产生什么样影响。
  • 在适当的列上得到定义时,索引能大幅提高特定查询的性能。

注意:Optim Query Tuner 可以就为 DB2 for Linux, Unix, and Windows 或 DB2 for z/OS 定义哪些索引提供详细、具体的建议。


触发器

触发器是与一个表相关的代码段,在对该表执行 INSERTUPDATEDELETE 操作时触发。

也有可对一个视图定义的触发器,在为 INSERTUPDATEDELETE 活动使用视图时,允许采用适当的替代操作,这些触发器称为 INSTEAD OF 触发器。

可以定义触发器,以便执行 BEFOREAFTER 操作的每个 ROW 或每个 STATEMENT

可以在一个表上为同一操作定义若干触发器。在一个视图上只能为每个操作定义一个 INSTEAD OF。当在一个表上为同一操作定义若干触发器时,它们的执行顺序与定义的顺序一样。

一个触发器可以执行的操作包括,基于涉及的值采取不同操作,影响其他表的内容,调用存储过程,或返回用户定义的错误条件。

例如,给定前面的 AUTHORS 表,在 BOOKS 中,整数列 NONFICTIONBOOKS 和 FICTIONBOOKS 是使用 BOOKTYPE 作为作者执行的计数进行汇总的,我们可以定义 BOOKS 表上所需的触发器之一,维护 AUTHORS 表中的汇总,如清单 41 所示。

清单 41. 清单 41. 在 BOOKS 表上创建触发器
CREATE TRIGGER BOOKSIA                                               
AFTER INSERT ON BOOKS REFERENCING NEW AS NEW FOR EACH ROW             
MODE DB2SQL                                                           
WHEN (NEW.BOOKTYPE ='N') UPDATE AUTHORS                               
  SET NONFICTIONBOOKS=NONFICTIONBOOKS+1 WHERE AUTHORID=NEW.AUTHORID;

要删除触发器,请执行以下命令: DROP TRIGGER BOOKSIA;


结束语

本教程旨在让您熟悉 DB2 中定义的数据类型、表、约束、视图、索引和触发器的特性。此外,本教程还向您展示了如何使用 CREATEALTERDROP 语句来管理这些对象。同时还提供了一些示例,以便您在受控的环境下使用这些对象亲自进行尝试。

第 6 部分:数据并行性 向您介绍数据一致性的概念,以及 DB2 用来在单用户和多用户环境下维护数据一致性的各种机制。

参考资料

学习

获得产品和技术

  • 现在您可以免费使用 DB2。下载 DB2 Express-C,这是为社区提供的 DB2 Express Edition 的免费版,它提供了与 DB2 Express Edtion 相同的核心数据功能,为构建和部署应用程序提供了坚实的基础。
  • 使用可以直接从 developerWorks 下载的 IBM 试用软件 构建您的下一个开发项目。
  • 以最适合您的方式 评估 IBM 产品:下载产品试用版,在线试用产品,在云环境下使用产品,或者在 SOA Sandbox 中花费几个小时来学习如何高效实现面向服务的架构。

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=940440
ArticleTitle=DB2 10.1 基础认证考试 610 备考教程:第 5 部分: 使用表、视图和索引
publish-date=08122013