DB2中多种常用功能的解决方法

导读】描述了多种常用功能的解决方法，而这些功能并不完全符合关系誓约。

 

 

 

简介

 

关系模型是一件美好事物。对它妥协是牵强附会，就象给米开朗琪罗的大卫扣上一顶棒球帽一样。然而，不属于纯关系模型的事物可能最终出现在您的数据库或应用程序中。如果您以修道士般的严肃态度看待关系誓约，那么不必继续阅读本文。对那些可能希望享受在狂野地带漫步的人而言，本文将带您到 RDBMS 的红灯区。请继续阅读本文以发现如何：

 

存储派生值

给每行编号以创建一个人工键

检索由 DB2® 生成的标识值

给结果集中的行编号

请求数据然后截断结果集

删除表中所有行而不记入日志

用视图和重命名表命令“删除”列

加宽 varchar 列

学习何时使用真的视图以及汇总表（名声不太好的实现视图）

学习声明的临时表与公共表表达式之间的差异

这些技术中的一些技术可以改进性能，就象您可能在 RDBMS 参考手册中看到的实用建议，它建议您使数据符合第四范式，然后在实现设计前做一些妥协以取得需要的性能。其它一些技术会使应用程序程序员的工作更简单，并且带来性能上意想不到的效果。

 

派生值

 

您可能在关于关系数据库的大学课程中的某处学到不要存储派生值。毕竟，可以在组装结果集时计算这些值，从而避免数据库中的冗余数据，并获得正确答案。DB2 在版本 7 中引入生成的列有以下几个原因。

 

请求数据的用户可能正在使用您没有控制权的应用程序。如果他们正在使用只需移动和点击鼠标就可以完成工作的应用程序进行 SELECT * 操作，他们可能没有意识到他们真正希望看到的是 COMPENSATION，并且必须将 COMMISSION 和 SALARY 列中的值相加。一个生成的列允许您存储这个值，并保持它的准确性：

create table employee

(name char(10), salary dec(10,2), commission dec(10,2),

compensation dec(11,2)

generated always as (commission + salary))

 

 

 

通过下面的 SQL 语句来保持 COMPENSATION 列的准确性：

 

 

insert into employee (name, salary, commission) values ('Blair',5,10)

 

 

update employee set salary=0

 

 

您需要唯一地标识每一行。我们将在稍后讨论这一点。

 

 

要求不区分大小写来创建索引，接下来就介绍这一点。

 

区分大小写

 

区分大小写是功能强大的，而且如果 RDBMS 知道 Greenland 与 greenland 不匹配，它会搜索得更快。然而，用户如果提交对“Macinnis”的搜索，他们可能实际上希望您的应用程序返回“MacInnis”。对于名称搜索，您可能要考虑在 NAME 列上创建一个索引。然而，DB2 索引中的值也是区分大小写的。让 MacInnis=Macinnis 很简单，只要使用 UPPER 或 UCASE 函数即可：

 

 

SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'

 

 

但是，这会强制进行表扫描，而且您得不到索引的好处。这就是引入生成的列的用途所在：如果标准访问方法是关于名称的搜索，那么使用生成的列来以大写格式存储名称：

 

 

CREATE TABLE EMPLOYEE (NAME VARCHAR(10),

 

NAME_UP

 

GENERATED ALWAYS AS (UPPER(name)))

 

 

 

现在在这个列的大写版本上创建索引：

 

 

CREATE INDEX NAME_IND ON EMPLOYEE ( NAME_UP )

 

 

该查询可以获得索引的好处，并避免了表扫描：

 

 

SELECT NAME FROM EMPLOYEE WHERE UPPER(NAME) = 'MACINNIS'

 

 

让我们看看如何用生成的列来枚举行。我们为什么要给行编号？关系理论告诉我们行与列没有内在的顺序：您可以在请求数据时指定顺序。但人们喜欢给事物编号，从书中的页号到运动衫上的号码。您可能知道在计算机科学中数据被看成表格式关系模型。您的用户有多少学会查看 Lotus 和 Excel 电子表格中的表格式数据（屏幕左边有向下递增的行号）呢？大多数关系数据库管理系统都有内部 RID（行标识）或 TID（元组标识）。OS/390® 上的 DB2 和 Oracle 将这一点具体化，使程序无需知道内容就可以方便地标识行。我们没有具体化 Windows／UNIX／OS/2 上 DB2 的行标识，因为我们允许它改变：潜在主键中的一个危险特性。DB2 的确有其它方法将一列作为人工主键使用。

 

在您借助任何这些唯一标识每一行的基本方法之前，请尽力找到真实的主键：问自己这个问题：“如果我们把每一行都写在纸上，应如何唯一标识它；假定客户或供应商打电话询问状态 — 我们怎样才能找到他们所询问的数据？”如果您在每张纸上都打上日期和时间戳记，那么这就是主键。

 

生成行标识

 

让我们从由其它数据库迁移到 DB2 的应用程序开始。如果移自 SQL Server，您可能厌倦了关于关系纯洁性的说教并希望了解 IDENTITY。下面是如何用 DB2 v7 创建 IDENTITY 列：

 

 

CREATE TABLE T1

 

(C1 DECIMAL(15,0) GENERATED BY DEFAULT AS IDENTITY

 

(START WITH 10),

 

C2 INTEGER)

 

 

 

还有在内存中高速缓存标识值的选项，这使插入更快，但如果您的系统在生成 IDENTITY 值时遭受硬件或软件崩溃，那么将在标识序列中留下间隔。缺省情况是一次增加整数 1，但您也可以按其它值（2 和 10 等）增加。插入后，您会对生成的值自然地产生好奇。为了应用程序的下一段逻辑，您可能需要知道这个值。在发行说明（Windows 上 x:\sqllib\release.txt）中记录的名为 IDENTITY_VAL_LOCAL() 的函数可为您检索这个值。

 

IDENTITY 在每个表中是唯一的。那些 Oracle 迷将很高兴得知 DB2 的版本 7，修订包 3 将把 SEQUENCE 列带入 DB2。序列在整个数据库中是独一无二的 — 这对于在多个表中使用的值很有用。您也可以在序列中循环以重用这些值。SEQUENCE 和 IDENTITY 不是数据类型：它们使用象 SMALLINT、INTEGER 或小数位是零的 DEC 那样的现有数据类型。INT 和 BIGINT 是最好的选择，它们能给您良好的性能和适当的数值范围。还允许负值。

 

生成人工主键还有其它方法。如果一次只有一位用户访问表（并且一次只插入一行），则触发器很不错。将您的主键列定义为缺省非空值，这样当在 INSERT 中没有指定它时，它就得到一个虚设的值（触发器将重写这个虚设的值）：

 

 

CREATE TRIGGER AutoIncrement NO CASCADE BEFORE

 

INSERT ON Foobar

 

REFERENCING NEW AS n

 

FOR EACH ROW MODE DB2SQL SET (n.col1) =

 

(SELECT COALESCE(MAX(col1),0) + 1 FROM Foobar )

 

 

 

DB2 还有一个名为 GENERATE_UNIQUE 的函数。这个函数将节点号（用于多分区数据库）与时间戳记结合，因此它可以与企业扩展版本（EEE）一起使用。IDENTITY 和 SEQUENCE 在 DB2 的下一个主要版本出现前还不能与 EEE 一起使用。GENERATE_UNIQUE 有两个缺点：数据类型（CHAR(13) FOR BIT DATA）不是按顺序递增，并且不象数值数据类型那样易于使用。

 

更简单的解决方案是标量子查询表达式：

 

 

INSERT INTO Foobar (key_col, ...)

 

VALUES (COALESCE((SELECT MAX(key_col) FROM Foobar) +1, 0) ...)

 

 

 

获得一屏数据

 

这些方法对于那些在数据库和应用程序投入生产以前有机会进行一些设计工作的模式和应用程序来说是不错的。但您还记得那两个以 A（ARIES（航班订票环境仿真）和 ACID（原子性、一致性、隔离和持久性））开头的 4.5 字母单词吗？如果您预定了航班，那么您希望他们在您到达机场时记得这回事。这就是持久性：有用的数据是持久的。这意味着即使您定义了一个好的主键，有些人可能会查询结果集的“前二十行”，而不管结果集中有多少行。更糟的情况是有人要求您显示第 21 行到 40 行。但等一下，您会提出异议，关系表中的行没有顺序！对于希望在他们的 Netscape 浏览器中一次看到二十行的用户而言，您就好象在说冰岛语。DB2 允许您实时地给结果集排序，并可以从该结果集的开始或结尾部分提取任意数量的行：

 

 

SELECT NAME FROM ADDRESS

 

ORDER BY NAME

 

FETCH FIRST 10 ROWS ONLY

 

 

 

SELECT NAME FROM ADDRESS

 

ORDER BY NAME DESC

 

FETCH FIRST 10 ROWS ONLY

 

 

 

ORDER BY 将强制在内存中对整个结果集进行排序，所以，为了提高 DB2 服务器性能，我们不这么做（尽管只向客户机发送 10 行可能会提高网络性能）。如果您不关心顺序并且只想知道至少有 10 行符合结果集，则清除 ORDER BY 以省去 DB2 服务器上的排序：

 

 

SELECT NAME FROM ADDRESS

 

FETCH FIRST 10 ROWS ONLY

 

 

 

 

于是现在我们已看到您给行编号并且任意选择了一个子集。假设我们因某些性能上的好处而给行编号，这必将破坏关系模型。我们几乎完全妥协了，并且已经犯了关系七宗罪中的六宗。还有一条关系誓约您没有触犯：让我们实时地给行编号，牺牲掉性能和关系纯洁性吧。我们如何证明这样做的正确性呢？在因特网上谴责它吧。

向使用浏览器的客户显示公司数据显然证明了违背对关系纯洁性和性能推崇所作的承诺。您可以用 rownum 或 rank 函数实时地给结果集赋予行号。下面我们为用来记录地址的表中的行排序，并选择第 11 行到第 20 行。结果集由名称和实时创建的名为 rn 的列（它给行编号）组成：

 

 

SELECT * FROM (SELECT NAME, rownumber() OVER

 

(ORDER BY NAME)

 

AS rn FROM ADDRESS)

 

AS tr WHERE rn BETWEEN 11 and 20

 

 

 

rank 更为复杂，并且它允许您以排序的顺序标识联系，对于足球联赛非常理想：

 

 

create table football (team char(10), points int)

 

 

insert into football values ('United', 20)

 

 

insert into football values ('Arsenal', 20)

 

 

insert into football values ('Liverpool', 10)

 

 

 

select rank() over

 

(order by points desc) as place,

 

team, points

 

from football

 

 

PLACE TEAM POINTS

1 United 20

1 Arsenal 20

3 Liverpool 10

 

清空表 — 无需通知日志记录程序（截断表）

 

现在你已经得到了很多精巧的方法来处理你的数据了，我们再来学习一个小把戏。其他的数据库产品有被称为“截断表”的功能，即在不进行日志记录的情况下删除表中的所有数据，而保留表的结构（如果不想保留表结构，我们就使用 DROP TABLE 命令了）。如果想在 DB2 中得到这种功能，可以执行带有 REPLACE 选项的 LOAD 命令，并使用一个 0 字节的文件作为导入数据源，由于 DB2 的 LOAD 操作是不做日志的，所以可以通过这个小骗局来达到我们的目的。

 

猜测游戏和镜屋

 

您的表很不错 — 为什么要从视图访问它？这样做有许多理由：

 

列级别安全性：排除那些您不希望用户在定义视图的 SELECT 中看到的列。

 

 

行级别安全性：除非您定义一个视图，否则 Windows／UNIX／OS/2 上的 DB2 v7 不允许您限制对表中某些行的访问（如果您希望限制对允许用户看到的内容的更新，请记得加上 check 选项）：

create view london_football as

select * from football

where team in ('Arsenal','Aston Villa')

with check option

 

 

 

设想这一点对于“人力资源”应用程序的作用：用户可以查看薪水在 $nn,nnn 以下的雇员，给他们加薪而加薪后的薪水不会在 $nn,nnn 以上。

 

DROP COLUMN：DB2 不允许您删除一个列。我可以想到您希望删除列的三个理由：

回收空间：如果您希望这样做，可以导出您希望保存的数据，删除那个表，用您需要的那些列重新创建表，然后装入这个表。这是否代价高昂？当然是，但是回收空间需要这样或者 REORG TABLE。这些本来就是代价高昂的操作。

 

 

这个列不再是行的逻辑部分：例如，您意识到您的雇员可能有两个地址，并且停止跟踪雇员（employee）表中的地址（雇员表和雇员地址（employee_address）表之间现在有 n:m 关系）。在雇员表上创建一个不包含地址列的视图。

如果您真的要用新奇的方法，可以使用 RENAME TABLE 命令给基表一个新的名称，然后将原始表名作为该视图的名称。您的视图也可以连接雇员表中的有用列和从雇员地址获得的地址。现在我们回到了关系的正道。

 

列变宽了。如果它是 VARCHAR，那您运气不错。DB2 允许您将 VARCHAR 列最多加宽至表空间（tablespace）中定义的页大小宽度（缺省的 4K 页大小为 4,005，而在 32K 页上最多为 32,672）：

create table t2 (col1 varchar(10))

alter table t2 alter column col1 set data type varchar(12)

 

 

 

我很喜欢这个视图，所以我实现它

 

如果派生列对您来说还不够坏，整个派生表怎么样？使它与基表中的数据匹配或不匹配（以及使每个 SELECT 成为潜在的错读）的能力又如何？Oracle 称这些为实现的视图。DB2 称它们为自动汇总表，在特殊情况下称为复制汇总表。如果经常被问到一个问题（SELECT MAX(ORDERS) FROM LEADS），或者经常组装一个聚集（SELECT COUNT(FRANCHISES) FROM STORES WHERE STATE=’TEXAS’），那么或许值得将结果集存储在磁盘上，这样 DB2 就不必每天重新计算它二十次：特别当几天前的数据足以准确地支持基于查询的决策时。

 

让我们从想知道哪个客户订购最多的贪婪的销售经理开始。他们在名为 LEADS 的表中跟踪这一项，推断出客户过去所下订单的数目可能有助于确定哪些销售线索最有可能变为真实的销售。这个问题每天会被问几次（如果您预感这正在发生并且需要验证它，您可以使用名为 Query Patroller 的 DB2 工具查看来自用户的查询）。SELECT MAX() 通常需要一个表扫描，这会强制 DB2 查看表中的每一行。如果您有许多线索，则需要扫描许多行才能找到一个值。定义一个汇总表允许 DB2 将这个值存储在磁盘上，这样 DB2 只用读一行就可以得到答案：

 

 

create summary table leads_max

 

(MAX_ORDERS) as (SELECT MAX(ORDERS) FROM LEADS )

 

DATA INITIALLY DEFERRED

 

REFRESH DEFERRED

 

 

 

创建汇总表后，用这条命令填充它：

 

 

REFRESH TABLE LEADS_MAX

 

 

用户不必了解汇总表。DB2 优化器会决定何时使用基本表，何时使用汇总表。请注意 REFRESH DEFERRED 子句：您正在告诉 DB2 旧数据在汇总表中是可接受的。这在您不需要准确答案或当前答案时是合适的。它适合构建一个业务计划，但对于要怎样存储银行余额，它就不适合了。请参阅 SQL Reference 中的特殊寄存器 CURRENT REFRESH AGE 以及 Administration Guide 中的“Creating a Summary Table”一节，以了解在答案可以“足够接近”、无需精确时，如何为汇总表中的旧数据设置容忍度。

 

REFRESH DEFERRED 是总结只读表上数据的理想选择。多分区数据库的特殊汇总表称为复制汇总表。您将在 DB2 EEE 中使用它以在每个分区都有小型表（或只读表）的副本。在 EEE 中，您通常将最大的表（称为事实表）分布到所有的分区。大量使用的连接键（如客户号码）应该作为分区键使用。DB2 将数据进行散列处理以对它分区。这意味着较少使用的连接键（如国家／地区或部门）可能会以次优化方式分布。当您在多分区数据库中连接数据时，与组合的连接更快（例如，CUSTOMER 和 COUNTRY 表中所有 COUNTRY 为 Argentina 的行都在同一分区）。如果 COUNTRY 不是分区键，这是不可能的。要获得组合，您可以将较小的表限制在一个分区，然后创建一个将它复制到其它分区的复制汇总表。这一策略在所复制的表较小或很少有更改时奏效（如果您在经常更改国名的国家做生意的话，要避免这么做）。如果表确实很小（如各大洲的列表），不要费心去复制它：DB2 将把它传送到所有分区并在连接期间将它保留在内存中。不要担心通过名称连接到副本：判断副本表何时可以提高性能是 DB2 的工作。

 

通过使汇总表 REFRESH IMMEDIATE，可以将它们用于动态数据。这有比 REFRESH DEFERRED 更严格的规则，所以请仔细阅读 SQL Reference。在首次创建汇总表之后，您仍必须使用 REFRESH TABLE 语句：

 

 

CREATE SUMMARY TABLE LEADS_BY_STATE

 

(NUM_LEADS, GRP_STATE)

 

AS (SELECT COUNT(ORDERS), STATE FROM LEADS GROUP BY STATE)

 

DATA INITIALLY DEFERRED

 

REFRESH IMMEDIATE

 

 

REFRESH TABLE LEADS_BY_STATE

 

 

 

也可以这样

 

我们现在已研究了两种视图。作为标准视图，视图定义存储在数据库中（在 SYSCAT.VIEWS.TEXT 中）而数据只存储在基表中。我们可以通过创建汇总表使得在这个数据上执行 SELECT 操作更快，这是以冗余数据为代价，它消耗更多磁盘空间并使得 INSERT、UPDATE 和 DELETE 更慢（或让基表和汇总表不同步，至少在下一次刷新以前是这样）。还有另一个极端：创建一个仅在数据库连接期间存在的聚集，或者甚至和 SQL 语句的生命期一样短。第一个称为 DECLARED TEMPORARY TABLE，第二个称为 COMMON TABLE EXPRESSION，也称为 TEMPORARY RESULT TABLE。一个声明的临时表需要一个 USER TEMPORARY TABLESPACE，您可以用 CREATE TABLESPACE 命令创建它（请参阅 SQL Reference）。您可以将这个临时表声明为应用程序运行时数据的保留位置。

 

 

DECLARE GLOBAL TEMPORARY TABLE table1

 

(column1 INT, column2 INT)

 

NOT LOGGED

 

 

 

您用模式 SESSION 限定表，因为它属于您连接到数据库时创建的会话：

 

 

INSERT INTO SESSION.TABLE1 VALUES (4,5)

 

 

SELECT * FROM SESSION.TABLE1

 

 

 

您可能希望这个临时表与现有表匹配，因此您可以用现有表的 SELECT 语句填充它。如果这样的话，使用 LIKE 创建它：

 

 

DECLARE GLOBAL TEMPORARY TABLE TEMP_EMP

 

LIKE EMPLOYEE

 

NOT LOGGED

 

 

 

 

INSERT INTO SESSION.TEMP_EMP

 

SELECT * FROM EMPLOYEE

 

 

 

当您断开连接时，DB2 将删除这个临时表。对于某些更临时的东西，DB2 支持公共表表达式，它允许您定义只存在于一条语句的表。公共表表达式还是另一个细微问题的答案：给一个不是以动词开始的 SQL 语句命名：

 

 

WITH COMPENSATION AS

 

(SELECT SUM(SALARY+COMMISSION)

 

AS TOTAL FROM EMPLOYEE)

 

SELECT TOTAL FROM COMPENSATION

 

 

 

您现在已被护送出红灯区。公共表表达式并不违背关系原则：它不要求 DB2 存储派生数据，也不添加人工列。如果一定要从这个故事引出一个寓意的话，假定用计算机解决一个问题有 n 种方法。一种方法可能成本最低，一种方法对您而言最快，一种方法对用户而言最快，而另一种方法对于继承您的设计以进行维护和添加新功能的开发人员而言最快。至于哪种选择最好，就作为习题留给读者吧。

原文转自：http://www.ltesting.net