DB2 9.7: 已创建的全局临时表 CGTT DGTT

最新推荐文章于 2018-08-10 14:22:19 发布

conghong0918

最新推荐文章于 2018-08-10 14:22:19 发布

阅读量179

点赞数 1

简介

如 DB2 9.7 for Linux, UNIX, and Windows Information Center（参见参考资料获取链接）所述，已创建的全局临时表是 DB2 9.7 中引入的一种全新的用户定义的临时表。在此发行版之前，已声明的临时表（DGTT）是唯一的一种用户定义的临时表。DGTT 与 CGTT 之间的主要差异在于 CGTT 的定义是持久存储在 DB2 目录下的。DGTT 在创建后仅在用户会话期间保存。与 DGTT 不同，CGTT 在会话中创建并且在会话终止后仍然将持久保存。已创建的临时表的定义由所有并发会话共享，即使其内容是各会话所私有的。

通过 CGTT 和 DGTT，应用程序会话可以使用已创建的临时表来保存操作或重复引用的中间结果集，而不会干扰并发运行的应用程序。CGTT 的持久化定义导致以下操作差异：

在应用程序会话定义已创建的临时表之后，并发运行的会话不需要重新定义它。
您可以在 SQL 函数、触发器、索引和视图中引用已创建的临时表。

此外，任意连接都可以随时引用已创建的临时表，而不需要设置脚本来初始化已创建的临时表。连接只可以访问它自己插入的行。

创建 CGTT 主要有两个动机。首先，CGTT 的行为对 SQL 程序员而言更像是一个普通表，但它具有提升性能的潜力。可以提前定义 CGTT 表，并且用户可以像普通表一样使用它。由于数据仅供各自会话使用（因此不需要行锁定），并且没有日志选项，因此提供了潜在的性能优势。

提供 CGTT 的第二个动机是帮助将非 DB2 临时表转换为 DB2 临时表。它可以降低这类操作的成本，从而能够更加轻松地迁移到 DB2。

其他 DB2 系列产品，比如 DB2 for z/OS® 以及 SQL 标准都支持已创建的临时表。

以下小节将帮助您理解 CGTT，并针对何时以及为何使用它们进行一番技术讨论；此外还将讨论它们的语法并通过一个实际示例展示如何使用它们；以及从 DGTT 迁移到 CGTT 的最佳实践。

前提条件

本文面向 DB2 数据库开发人员和 DB2 数据库管理员。读者需要具备 DB2 和脚本的基本知识。

系统要求

DB2 9.7 Workgroup Server Edition for Linux®, UNIX®, and Windows®，或者 DB2 9.7 Enterprise Server Edition for Linux, UNIX, and Windows。

回页首

技术讨论

本节讨论应该考虑的已创建的临时表的行为。

CGTT 允许不执行日志。可以通过设置以下选项来提高性能：not logged on commit delete rows。不使用日志是默认行为。这还意味着提交或回滚会造成表被截短。

如果打算记录所有变量，则使用：logged on commit preserve rows。提交语句会将会话数据恢复为之前的提交或回滚语句。如果您希望获取与普通表相同的行为，则应该使用此选项。

CGTT 在效率上优于普通表。由于不需要锁定（所有数据都是本地的），因此 CGTT 的性能要比普通表更好。如果未指定日志，则性能会更高。如果您计划截短表，则应该使用 “not logged” 选项。

如果为 CGTT 表创建了索引，则查询编译器会将这些索引考虑在内。特定会话中的 Runstat 只会考虑本地数据。这将允许各会话使用不同的访问计划。

除了下表列举的情况之外，CGTT 将提供与普通表相同的支持。但仍然需要注意以下几点：

CGTT 支持导入/导出，但它不支持载入。
允许批准和撤回。
添加到某个表中的索引对于其他会话可见。但是，如果会话数据与某个新索引冲突（比如重复行在唯一的索引上冲突），则在数据纠正之前不会为该会话物化该索引。更加可取的方式是在准备阶段定义所有索引，以避免这些混淆。
CGTT 不支持唯一的约束或主键，但您可以创建唯一的索引。
CGTT 不支持约束。如果您使用 LIKE 语句创建 CGTT 表（像普通表一样创建 CGTT），则该语句会执行成功，但 CGTT 不会继承原始表的约束。
表压缩是默认启用的（如果您购买了该功能）。但是，压缩经常访问的数据可能不是最好的性能选项。您可以禁用压缩 CGTT 表。

您可以为 CGTT 创建触发器。CGTT 也可以在触发器的主体中使用。CGTT 的内容仅供会话使用。对于以下情况，您应该使用 CGTT 来代替 DGTT：

需要经常创建它们
需要索引
希望在视图中使用它

CGTT 不支持：

rename、alter、reorg、refresh、nickname、comments 和 lock 语句
RI
类型表、范围分区或 MDC 表
作为物化视图的基表
作为安全表的基表
XML 列

DB2 与 Oracle 实现相比具有若干优势。 Oracle 的语法适用于 DB2，并且在大多数情况下都不需要迁移。借助 DB2，您可以：

散列分区数据
回滚条件（CGTT 具有这方面的灵活性）
指定一个表空间，但必须是用户临时目录
选择记录与否

CGTT 在许多应用下都可以带来收益。我们通过一些示例来了解如何有效利用 CGTT 功能：

在线购物车应用，会话开始时为每个用户的购物车创建一个 CGTT，并在会话结束时销毁它，只要在用户真正购买商品后才会复制到实际的表中。
班级入学，每个学生都有所有的课程安排选择，每个学生都将自己的选择放在 CGTT 中。只有在学生接受选择之后，这些选择才会移动到所有课程选择的实际表中。
公司的福利登记，员工将做出下一财政年度的福利选择（卫生保健、牙齿护理、401k 等）。提交选择之后，它们才从 CGTT 移动到实际表中。

CGTT 比普通表更加易于维护，因为普通表需要重新组织，并且可以减少事务繁多的应用程序的记录竞争问题。由于支持使用索引和日志选项，它的调优任务也比 DGTT 更加简单。

回页首

如何使用 DB2 CGTT

本节中的代码清单展示了 CGTT 在 DB2 中的运行方式。所有这些任务都可以在 DB2 API 和工作中完成，包括 CLP、CLPPlus、Optim Database Administrator 和 Optim Development Studio。清单可以在您自己的 DB2 数据库中运行。确保您将脚本中的 dbname 更改为了自己数据库的名称，并在运行测试之前创建用户 user1 和 user2。本节展示即使在提交后也不能从其他会话中访问数据。

清单 1 展示了创建 CGTT 的语法：

清单 1. DB2 语法

				
Create GLOBAL TEMPORARY TABLE  (
,
,
…  )
ON COMMIT [PRESERVE|DELETE] ROWS
[NOT LOGGED|LOGGED] ON ROLLBACK [PRESERVE|DELETE] ROWS 
DISTRIBUTE BY HASH ( col1,..)
IN ;

清单 2 展示了如何连接到数据库、删除/创建全局临时表、创建别名、批准所有权限为公有、将数据插入表以及选择表中指定行。

请再次确保将所有清单中的 dbname 替换为您数据库的名称，以及将 user1 替换为您的用户名。

清单 2. 连接到数据库和其他操作

				
CONNECT TO dbname USER user1;

DROP TABLE user1.gtt_test;

CREATE GLOBAL TEMPORARY TABLE user1.gtt_test
   ( col1   number(5),
     Col2   date default sysdate) 
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK DELETE ROWS;

DROP PUBLIC ALIAS gtt_test;

CREATE PUBLIC ALIAS gtt_test FOR user1.gtt_test;

GRANT ALL ON gtt_test TO PUBLIC;

INSERT INTO gtt_test ( col1 ) select rownum from syscat.tables where rownum < 100;

COMMIT;

SELECT COUNT(*) FROM gtt_test;

清单 2 将为新创建的全局临时表创建一个公有别名，以便于可以使用 gtt_test 来引用它，而不需要限定表名。它还将访问权限批准为公有，插入了数据并从表中选择了指定行。

运行清单 2 时，您应该能看到与清单 3 相同的结果。清单 3 显示了选择语句的结果。您应该可以看到 select count(*) 语句返回了一个 99 项：

清单 3. select count(*) 结果

				
>db2 -tvf gtt_1.sql 


SELECT COUNT(*) FROM gtt_test

1          

-----------

         99


  1 record(s) selected.

如果您在其他会话中运行清单 4 中的代码，则会发现数据在不同会话之间是没有共享的。在清单 4 中，它作为不同用户连接到数据库，并执行 selects count(*) 从已创建的全局临时表 gtt_test 中选择指定项。表上有一个别名，因此您不需要指定表的所有者。

清单 4. 作为不同用户连接到数据库

				
CONNECT TO dbname USER user2;

SELECT count(*) FROM gtt_test;

运行清单 4 时，您应该能看到与清单 5 相同的结果 — select count(*) 语句返回的结果是 0：

清单 5. select count(*) 结果

				
db2 "select count(*) from db2inst1.gtt_test";

1          

-----------

          0


  1 record(s) selected.

清单 6 应该在运行清单 2 时建立的第一个会话中运行。这将选择表中前几行的所有数据。

清单 6. 从表的前几行选择数据

				

SELECT * FROM gtt_test where rownum < 5;

在 user1 会话中运行清单 6。这将展示默认值对于 Created Global Temporary Table 有效，并且每一行中都设置了日期。您的结果应如清单 7 所示，其中显示了 4 行：

清单 7. 结果

				
$ db2 "SELECT * FROM gtt_test where rownum < 5";

COL1    COL2               

------- -------------------

     1. 2009-07-10-12.34.41

     2. 2009-07-10-12.34.41

     3. 2009-07-10-12.34.41

     4. 2009-07-10-12.34.41


  4 record(s) selected

下一步是运行清单 8，这次仍然将在用户 1 会话中运行。这将展示 Created Global Temporary Table 截短操作运行成功。

清单 8. 截短 CGTT

				

SELECT COUNT(*) from gtt_test;

TRUNCATE TABLE gtt_test;

SELECT * FROM gtt_test where rownum < 5;

此代码选择表中的所有项，截短它并再次选择所有项以展示所有数据已经不在。验证您的结果与清单 9 相同：

清单 9. 结果

				
db2inst1@mclaren-laptop:/home/mclaren/sql$ db2 -tvf gtt_4.sql 

SELECT COUNT(*) from gtt_test

1          
-----------
         99



  1 record(s) selected.



TRUNCATE TABLE gtt_test

DB20000I  The SQL command completed successfully.



SELECT * FROM gtt_test where rownum < 5


COL1    COL2               

------- -------------------

  0 record(s) selected.

从结果中可以看出，数据已正确截短。

回页首

将已声明的全局临时表 (DGTT) 转换为已创建的全局临时表 (CGTT)

DB2 支持 DGTT（已声明的全局临时表）和 CGTT（已创建的全局临时表）。两者都用于支持基于会话的数据。对于一些编程模型来说，将 DGTT 转换为 CGTT 可以避免在各用户会话开始时声明临时表的要求。由于 CGTT 是持久化的，因此可以在系统设置时创建并共享它们。

与 CGTT 相比，DGTT 具有以下限制：

DGTT 是在会话中声明的。表定义的持久化特性仅针对该会话。
DGTT 不在系统目录中。这意味着对于所有引用 DGTT 的语句来说，它们在使用之前都需要进行编译。从本质上说，CGTT 将表设置流程移到了会话外部。
DGTT 有固定的模式 SESSION。CGTT 采用与普通表相同的模式规则。

清单 9 展示了一个已声明临时表的示例。您将在稍后的清单中将这个 DGTT 转换为 CGTT。

清单 10. 已声明的全局临时表示例

				
declare global temporary table dgttbase1 
           ( B1_c1	integer,
             B1_C2	varchar(20),
	     B1_c3      varchar(20)
           )  on commit preserve rows;
insert into SESSION.dgttbase1 values (1,'R2_c1 is 1', 'inserted row');
insert into SESSION.dgttbase1 values (2,'R2_c1 is 2', 'inserted row');
connect reset;

connect to dbname;
insert into SESSION.dgttbase1 values (3,'R2_c1 is 3', 'inserted row');
select * from SESSION.dgttbase1;
connect reset;

SESSION.dgttbase1 表对于第二个会话不可见。要在第二个会话中使用 dgttbase1，您需要在会话中重新创建表。清单 11 显示了命令的输出。在会话结束时，DGTT 已经不复存在。

清单 11. 命令输出

				
declare global temporary table dgttbase1 ( B1_c1      integer, 
                                           B1_C2      varchar(20),
                                           B1_c3      varchar(20) 
                                         )  on commit preserve rows
DB20000I  The SQL command completed successfully.

insert into SESSION.dgttbase1 values (1,'R2_c1 is 1', 'inserted row')
DB20000I  The SQL command completed successfully.

insert into SESSION.dgttbase1 values (2,'R2_c1 is 2', 'inserted row')
DB20000I  The SQL command completed successfully.

onnect reset
DB20000I  The SQL command completed successfully.

connect to dbname

insert into SESSION.dgttbase1 values (3,'R2_c1 is 3', 'inserted row')
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "SESSION.DGTTBASE1" is an undefined name.  SQLSTATE=42704

select * from SESSION.dgttbase1
SQL0204N  "SESSION.DGTTBASE1" is an undefined name.  SQLSTATE=42704

connect reset
DB20000I  The SQL command completed successfully.

清单 12 展示了如何将 DGTT 表转换为 CGTT。CGTT 的语法与 DGTT 极为相似。与 DGTT 相比，在第二个会话的过程中，cgttbase1 的定义仍然有效。

清单 12. 将 DGTT 转换为 CGTT

				
Connect to 
create global temporary table cgttbase1 
           ( B1_c1	integer,
             B1_C2	varchar(20),
             B1_c3      varchar(20)
           )  on commit preserve rows;
connect reset;

insert into cgttbase1 values (1,'R2_c1 is 1', 'inserted row');
insert into cgttbase1 values (2,'R2_c1 is 2', 'inserted row');

Connect reset;
Connect to 
insert into cgttbase1 values (3,'R2_c1 is 3', 'inserted row');
insert into cgttbase1 values (4,'R2_c1 is 4', 'inserted row');
select * from cgttbase1;
connect reset;

创建 CGTT 表 cgttbase1 之后，它将持久化到后续所有的会话中。

清单 13. 以上语句的输出

				
Connect to dbname
create global temporary table cgttbase1 ( B1_c1     integer, 
                                          B1_C2     varchar(20), 
                                          B1_c3     varchar(20) 
                                        )  on commit preserve rows
DB20000I  The SQL command completed successfully.
connect reset


connect to dbname
insert into cgttbase1 values (1,'R2_c1 is 1', 'inserted row')
DB20000I  The SQL command completed successfully.

insert into cgttbase1 values (2,'R2_c1 is 2', 'inserted row')
DB20000I  The SQL command completed successfully.
Connect reset

Connect to dbname
insert into cgttbase1 values (3,'R2_c1 is 3', 'inserted row')
DB20000I  The SQL command completed successfully.
insert into cgttbase1 values (4,'R2_c1 is 4', 'inserted row')
DB20000I  The SQL command completed successfully.

select * from cgttbase1

B1_C1       B1_C2                B1_C3               
----------- -------------------- --------------------
          3 R2_c1 is 3           inserted row        
          4 R2_c1 is 4           inserted row        

  2 record(s) selected.
connect reset

回页首

结束语

已创建的全局临时表 (CGTT) 是 DB2 9.7 中的新特性，它提供了以下功能：

索引支持
触发器支持
支持在视图中使用
持久化定义
日志选项
散列分区选项
回滚灵活性

CGTT 提供了全局持久定义，从而允许所有用户会话访问相同的表定义，而不需要在启动时声明它。对于一些已有的 DB2 应用程序，CGTT 可以通过替换 DGTT 来简化它们的编程模型。举例来说，允许使用视图和触发器支持可以简化应用程序的编写，因为它提供了这些选项。它在维护性方面也优于普通表，因为 CGTT 将减少维护需求。CGTT 在支持索引后也简化了调优工作。此外，与普通表和 DGTT 表相比，CGTT 在采用全新的日志提交选择之后提供了更大的灵活性和性能。

当您从其他数据库迁移到 DB2 时，CGTT 的作用也不可忽视。举例来说，Oracle 临时表可以方便地作为 DB2 CGTT 使用。

CGTT 提供了许多优势。您现在应该理解在应用程序中使用它们的必要性了。

来自：http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0912globaltemptable/index.html

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17032106/viewspace-705021/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17032106/viewspace-705021/

conghong0918

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
DB2 9.7: 已创建的全局临时表 CGTT DGTT

简介如 DB2 9.7 for Linux, UNIX, and Windows Information Center（参见参考资料获取链接）所述，已创建的全局临时表是 DB2 9.7 中引入的一种全新的用户定义的临时表。在...
复制链接

扫一扫