在 DB2 中声明全局临时表与创建全局临时表 [英] DECLARE GLOBAL TEMPORARY TABLE Vs CREATE GLOBAL TEMPORARY TABLE in DB2

问题描述

我正在 DB2 中创建一个 GLOBAL TEMPORARY TABLE.当我上网时，我有两种创建方式1. 声明2. 创建.

am creating a GLOBAL TEMPORARY TABLE in DB2. and when i surfed i got a two way to create 1. Declare 2. Create.

1. DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_EMP
      (EMPNO  CHAR(6) NOT NULL,
       SALARY DECIMAL(9, 2),
       BONUS  DECIMAL(9, 2),
       COMM   DECIMAL(9, 2)) WITH REPLACE ON COMMIT PRESERVE ROWS ;

2. CREATE GLOBAL TEMPORARY TABLE TMPDEPT
      (TMPDEPTNO   CHAR(3)      NOT NULL,
       TMPDEPTNAME VARCHAR(36)  NOT NULL,
       TMPMGRNO    CHAR(6),
       TMPLOCATION CHAR(16) ) ON COMMIT PRESERVE ROWS ;

从 IBM 站点我得到一个信息，创建是最好的，因为它是持久的，允许所有用户会话访问相同的表定义，而无需在启动时声明它以及更多优势.

and from IBM site i got a info that create is the best since its being persistent , allowing all user sessions to access the same table definition without having to declare it at startup and many more advantages.

链接:http://www.ibm.com/developerworks/data/library/techarticle/dm-0912globaltemptable/

我在使用 create over declare 时遇到了一些疑问:

and i had few queries in using create over declare:

1. 我在使用 CREATE GLOBAL TEMPORARY TABLE 时找不到 Replace 关键字.

1. I couldn't find the Replace keyword while using CREATE GLOBAL TEMPORARY TABLE .

考虑一种情况，正在打开连接并执行存储过程，
在该存储过程中创建全局临时表并在该存储过程中调用另一个存储过程再次具有 same Create Temp table 语句..在这种情况下会发生什么..由于两个表 naes 相同并且在单个连接中，它是否会引发任何错误?

consider one scenario, am opening a connection and executing a Stored Procedure,
within that Stored Procedure am creating Global temp table and with in that Stored Procedure am calling Another Stored Procedure which again have same Create Temp table statement .. what will happen in this case.. does it throw any error since both table naes are same and within the single connection?

声明有会话而创建没有??这与持久性有关吗?

Declare have session and create doesn't have?? does this related to persistant??

在性能方面哪个更好?声明温度还是创建温度?

in performace wise which is better? Declare temp or create temp?

建议一些场景，以便最好地使用 declare/create !!

Suggest some scenarioes for the best usage of declare / create !!

推荐答案

有一篇 Craig 的好文章S. Mullins 涵盖了两者之间的主要区别.在大多数情况下，它们的工作方式相同.

There is a good article from Craig S. Mullins that covers the major differences between the two. For most purposes, they work the same.

Created Temp Tables 是在 DSNDB07 中创建的，它是工作文件数据库(与需要工作存储的 SQL 语句期间使用的存储区域相同).声明的临时表存储在您必须创建的临时表空间中.

Created Temp Tables are created in DSNDB07, which is the working file database (the same storage area used during SQL statements that need working storage). Declared Temp Tables are stored in temporary tablespace that you have to create.

CTT 有一些缺点:

• 由于它们不是持久化的，一些典型的数据库操作包括锁定、日志记录和恢复不适用于已创建临时表.

• Because they are not persistent, some typical database operations including locking, logging, and recovery do not apply to created temporary tables.

无法在已创建的临时表上创建索引，因此所有访问是通过完整的表扫描.

Indexes can not be created on created temporary tables so all access is by a complete table scan.

无法在已创建的临时表上创建约束.

Constraints can not be created on created temporary tables.

null 是创建的列唯一允许的默认值临时表.

A null is the only default value permitted for columns of a created temporary table.

创建的临时表不能被 DB2 实用程序引用.

Created temporary tables can not be referenced by DB2 utilities.

创建的临时表不能指定为对象UPDATE 语句.

Created temporary tables can not be specified as the object of an UPDATE statement.

从创建的临时表中删除时，所有行必须是已删除.

When deleting from a created temporary table, all rows must be deleted.

虽然可以在创建的临时表上创建视图，但 WITH无法指定 CHECK OPTION.

Although views can be created on created temporary tables, the WITH CHECK OPTION can not be specified.

DTT 通常更灵活:

• 声明的临时表可以有索引和 CHECK 约束定义在他们身上.

• Declared temporary tables can have indexes and CHECK constraints defined on them.

您可以发出 UPDATE 语句和定位的 DELETE 语句针对已声明的临时表.

You can issue UPDATE statements and positioned DELETE statements against a declared temporary table.

您可以隐式定义已声明临时表的列并使用 SELECT 中的结果表.

You can implicitly define the columns of a declared temporary table and use the result table from a SELECT.

现在回答你编号的问题:

Now for your numbered questions:

1.&2. 一个都没有.我相信(而且我不能 100% 确定这是否准确，我们的商店几乎在所有情况下都使用 DTT)一个 CTT 被声明一次(由DBA)，然后应用程序程序员可以在任何会话中使用它.每个连接都有自己的副本，一旦应用程序断开连接，该会话中存储在该 CTT 中的数据将消失.

1. & 2. There isn't one. I believe (and I'm not 100% sure if this is accurate, our shop pretty much uses DTTs in all cases) that a CTT is declared once (by a DBA), and then the application programmers can use it in any session. Each connection will have their own copy, and once the application disconnects, the data stored in that CTT in that session will go away.

3.SESSION 只是 DTT 的架构标识符.说明这是暂时的不持久的表.

3. SESSION is just the schema identifier for DTTs. It shows that it is a temporary table that does not persist.

4.我认为两者的性能大致相同.他们会更快与普通表相比，因为锁定、日志记录、恢复等将不适用.

4. I think both will have about the same performance. They will be faster than normal tables, because locking, logging, recovery, etc will not apply.

5.总的来说，我会说 DTT 是可行的方法，但 CTT 是有用(正如 Craig 在他的文章中所说):

5. In general, I would say that DTTs are the way to go, but CTTs are useful (as Craig says in his article):

(CTTs)应主要在没有更新临时数据时考虑是必需的，并且对临时数据的访问完全是顺序的.

(CTTs) should be considered primarily when no updating of temporary data is needed and access to the temporary data is purely sequential.

这篇关于在 DB2 中声明全局临时表与创建全局临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！