DB2临时表空间与DGTT、CGTT临时表

最新推荐文章于 2023-08-25 16:50:36 发布

转载最新推荐文章于 2023-08-25 16:50:36 发布 · 1.3k 阅读

文章标签：

#数据库 #操作系统

本文详细介绍了DB2数据库中临时表空间的概念及其使用限制，并对比了系统临时表空间与用户临时表空间的区别。同时深入探讨了临时表的两种类型——已声明的临时表和已创建的临时表，包括它们的定义方式、特性及应用场景。

一、临时表空间

DB2的临时表空间分为系统临时表空间和用户临时表空间。系统临时表空间主要用于磁盘排序，用户临时表空间用于创建、声明临时表使用。

在系统临时表空间创建常规表会报错：

[DWE3:/home/maint1]db2 "create table maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M like PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M in TEMPSPACE"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0284N The table was not created because the table space "TEMPSPACE"
following the clause "IN" is a "SYSTEM TEMPORARY" table space. SQLSTATE=42838

在系统临时表空间声明临时表会报错：

[DWE3:/home/maint1]db2 "declare global temporary table session.TMP_DWU_PAR_MOBILE_USER_STAT_M like PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M in TEMPSPACE"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0284N The table was not created because the table space "TEMPSPACE"
following the clause "IN" is a "SYSTEM TEMPORARY" table space. SQLSTATE=42838
[DWE3:/home/maint1]

在用户临时表空间创建常规表会报错：

[DWE3:/home/maint1]db2 "create table maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M as (select * from PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M) definition only in userspace"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0284N The table was not created because the table space "USERSPACE"
following the clause "IN" is a "USER TEMPORARY" table space. SQLSTATE=42838

[DWE3:/home/maint1]db2 list tablespaces

Tablespaces for Current Database

Tablespace ID                        = 0
Name                                 = SYSCATSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Regular table space.
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 1
Name                                 = TEMPSPACE1
Type                                 = System managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 2
Name                                 = SYSTOOLSTMPSPACE
Type                                 = System managed space
Contents                             = User Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 3
Name                                 = SYSTOOLSPACE
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 5
Name                                 = TBS_CDE
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 6
Name                                 = TBS_ETL
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 12
Name                                 = USERSPACE
Type                                 = Database managed space
Contents                             = User Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 13
Name                                 = TEMPSPACE
Type                                 = Database managed space
Contents                             = System Temporary data
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 16
Name                                 = I3_EXPLAIN
Type                                 = System managed space
Contents                             = All permanent data. Regular table space.
State                                = 0x0000
   Detailed explanation:
     Normal

Tablespace ID                        = 17
Name                                 = TBS_PRT
Type                                 = Database managed space
Contents                             = All permanent data. Large table space.
State                                = 0x0000
   Detailed explanation:
     Normal

DB21011I In a partitioned database server environment, only the table spaces
on the current node are listed.

二、临时表

临时表分为已声明的临时表和已创建的临时表。

已声明临时表通过DECLARE GLOBAL TEMPORARY TABLE语句定义，不显示在系统目录表中。只在当前会话中有效，其他连接会话看不到此临时表定义，会话终止时侯会自动删除表中数据并且删除表的定义。默认声明的临时表在执行提交操作后会删除记录，可通过ON COMMIT DELETE ROWS或ON COMMIT PRESERVE ROWS选项指定。

已创建临时表使用CREATE GLOBAL TEMPORARY TABLE语句创建，所有连接到服务器的会话都能看到此表定义并使用。表中数据在每个会话中有效，会话连接终止时侯自动删除。和声明的临时表类似，默认创建的临时表在执行提交操作后会删除记录，可通过ON COMMIT DELETE ROWS或ON COMMIT PRESERVE ROWS选项指定。

1. 已声明临时表例子

我们可以在用户临时表空间上声明临时表，临时表必须使用session模式限定，否则报错：

[DWE3:/home/maint1]db2 "declare global temporary table maint1.TMP_DWU_PAR_MOBILE_USER_STAT_M as (select * from PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M) definition only in userspace"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0079N The Schema name for the declared global temporary table or index
"TMP_DWU_PAR_MOBILE_USER_STAT_M" must be SESSION, not "MAINT1".
SQLSTATE=428EK

[DWE3:/home/maint1]db2 "declare global temporary table session.TMP_DWU_PAR_MOBILE_USER_STAT_M as (select * from PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M) definition only in userspace>
DB20000I The SQL command completed successfully.

默认声明的临时表数据会在语句提交后自动删除
[DWE3:/home/maint1]db2 "insert into session.TMP_DWU_PAR_MOBILE_USER_STAT_M select * from PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M"
DB20000I The SQL command completed successfully.

插入数据，但我们查询发现表中无记录：
[DWE3:/home/maint1]db2 "select count(*) from session.TMP_DWU_PAR_MOBILE_USER_STAT_M"

1
-----------
0

1 record(s) selected.

这是因为db2命令行默认打开了自动提交属性，没执行完一条语句会自动执行提交操作，可通过db2 list command options命令查看命令行设置

[DWE3:/home/maint1]db2 list command options

Command Line Processor Option Settings

Backend process wait time (seconds)        (DB2BQTIME) = 1
No. of retries to connect to backend        (DB2BQTRY) = 60
Request queue wait time (seconds)          (DB2RQTIME) = 5
Input queue wait time (seconds)            (DB2IQTIME) = 5
Command options                           (DB2OPTIONS) =

Option Description                               Current Setting
------ ---------------------------------------- ---------------
   -a    Display SQLCA                             OFF
   -c    Auto-Commit                               ON
   -d    Retrieve and display XML declarations     OFF
   -e    Display SQLCODE/SQLSTATE                  OFF
   -f    Read from input file                      OFF
   -i    Display XML data with indentation         OFF
   -l    Log commands in history file              OFF
   -m    Display the number of rows affected       OFF
   -n    Remove new line character                 OFF
   -o    Display output                            ON
   -p    Display interactive input prompt          ON
   -q    Preserve whitespaces & linefeeds          OFF
   -r    Save output to report file                OFF
   -s    Stop execution on command error           OFF
   -t    Set statement termination character       OFF
   -v    Echo current command                      OFF
   -w    Display FETCH/SELECT warning messages     ON
   -x    Suppress printing of column headings      OFF
   -z    Save all output to output file            OFF

我们可以临时通过db2 -c- 或db2 +c取消自动提交：

[DWE3:/home/maint1]db2 +c
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 ADCL 9.1.5

You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => insert into session.TMP_DWU_PAR_MOBILE_USER_STAT_M select * from PDW.TMP_DWU_PAR_MOBILE_USER_STAT_M
DB20000I The SQL command completed successfully.
db2 => select count(*) from session.TMP_DWU_PAR_MOBILE_USER_STAT_M

1
-----------
2021477

1 record(s) selected.

插入数据后能查询到记录，执行提交操作后，临时表数据自动删除

db2 => commit
DB20000I The SQL command completed successfully.
db2 => select count(*) from session.TMP_DWU_PAR_MOBILE_USER_STAT_M

1
-----------
0

1 record(s) selected.

新打开一个连接创建，查询此表，会报此表不存在：

[DWE3:/home/maint1]db2 connect to bssdb

Database Connection Information

Database server        = DB2/AIX64 9.1.5
SQL authorization ID   = MAINT1
Local database alias   = BSSDB

[DWE3:/home/maint1]db2 "select count(*) from session.TMP_DWU_PAR_MOBILE_USER_STAT_M"
SQL0204N "SESSION.TMP_DWU_PAR_MOBILE_USER_STAT_M" is an undefined name.
SQLSTATE=42704

断开连接后，表自动删除：

db2 => terminate
DB20000I The TERMINATE command completed successfully.
[DWE3:/home/maint1]db2 -c-
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 ADCL 9.1.5

You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd

For more detailed help, refer to the Online Reference Manual.

db2 => select count(*) from session.TMP_DWU_PAR_MOBILE_USER_STAT_M
SQL1024N A database connection does not exist. SQLSTATE=08003
db2 => connect to bssdb

Database Connection Information

Database server        = DB2/AIX64 9.1.5
SQL authorization ID   = MAINT1
Local database alias   = BSSDB

db2 => select count(*) from session.TMP_DWU_PAR_MOBILE_USER_STAT_M
SQL0204N "SESSION.TMP_DWU_PAR_MOBILE_USER_STAT_M" is an undefined name.
SQLSTATE=42704

2. 已创建的临时表（db2v9.1不支持）

# su - db2inst1

$ db2 connect to sample

Database Connection Information

Database server = DB2/LINUX 9.7.3

SQL authorization ID = DB2INST1

Local database alias = SAMPLE

$ db2 connect to bssdb

SQL1013N The database alias name or database name "BSSDB" could not be found.

SQLSTATE=42705

$ db2 connect to sample

Database Connection Information

Database server = DB2/LINUX 9.7.3

SQL authorization ID = DB2INST1

Local database alias = SAMPLE

创建一个临时表

$ db2 -c- "create global temporary table temp_emp like emp"

DB20000I The SQL command completed successfully.

取消自动提交状态下插入数据，能正常查询表中记录

$ db2 -c- "insert into temp_emp select * from emp"

DB20000I The SQL command completed successfully.

$ db2 -c- "select count(*) from temp_emp"

-----------

1 record(s) selected.

新开一个窗口，能正常访问此表，但无记录

Last login: Tue Apr 19 15:19:04 2011 from 130.30.3.214

# su - db2inst1

$ db2 connect to sample

Database Connection Information

Database server = DB2/LINUX 9.7.3

SQL authorization ID = DB2INST1

Local database alias = SAMPLE

$ db2 -c- "select count(*) from temp_emp"

-----------

1 record(s) selected.

返回初始窗口，执行提交操作后，再次查询，表记录自动删除：

$ db2 commit;

DB20000I The SQL command completed successfully.

$ db2 -c- "select count(*) from temp_emp"

-----------

1 record(s) selected.

查询此表定义

$ db2 "select tabschema,tabname,owner,tbspace,type,tbspaceid,tableid from syscat.tables where tabname='TEMP_EMP'"

TABSCHEMA TABNAME OWNER TBSPACE TYPE TBSPACEID TABLEID

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

DB2INST1 TEMP_EMP DB2INST1 SYSTOOLSTMPSPACE G -6 -32768

1 record(s) selected.

Type中G = Created temporary table，表所在表空间为SYSTOOLSTMPSPACE

查看表空间信息，SYSTOOLSTMPSPACE为用户临时表空间

$ db2 list tablespaces

Tablespaces for Current Database

Tablespace ID = 0

Name = SYSCATSPACE

Type = Database managed space

Contents = All permanent data. Regular table space.

State = 0x0000

Detailed explanation: