声明全局临时表与在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时遇到了一些查询过度声明：

and i had few queries in using create over declare:

1. 我找不到 Replace 关键字，而使用 CREATE GLOBAL TEMPORARY TABLE 。

考虑一种情况，
正在打开连接，执行存储过程，在该存储过程中的

创建全局临时表
，并在该存储过程中调用另一个存储过程
，该存储过程再次具有相同创建临时表语句..在这种情况下会发生什么..
会引发任何错误，因为两个表的名称相同并且在单个连接中吗？

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?

建议最佳使用一些场景来声明/创建!!

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.

创建的临时表在 DSNDB07 中创建，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.

n是唯一允许用于a列的默认值创建的
临时表。

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通常更加灵活：

DTT are usually much more flexible:

• 已声明的临时表可以在上面定义索引和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.

现在为您编号的问题：

1 。 & 2.没有一个。我相信（而且我不确定100％是否正确，我们的商店
在所有情况下都使用DTT）CTT一次声明（由
a 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):

在不需要更新临时数据
并且访问临时数据纯粹是顺序的情况下，应首先考虑

（CTT）。

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

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