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

一、临时表空间

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 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 => 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         

-----------

         42

 

  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         

-----------

          0

 

1 record(s) selected.

 

 

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

$ db2 commit;

DB20000I  The SQL command completed successfully.

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

 

1         

-----------

          0

 

  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:

     Normal

 

 Tablespace ID                        = 1

 Name                                 = TEMPSPACE1

 Type                                 = System managed space

 Contents                             = System Temporary data

 State                                = 0x0000

   Detailed explanation:

     Normal

 

 Tablespace ID                        = 2

 Name                                 = USERSPACE1

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

 

 Tablespace ID                        = 3

 Name                                 = IBMDB2SAMPLEREL

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

 

 Tablespace ID                        = 4

 Name                                 = IBMDB2SAMPLEXML

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

 

 Tablespace ID                        = 5

 Name                                 = SYSTOOLSPACE

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

 

 Tablespace ID                        = 6

 Name                                 = SYSTOOLSTMPSPACE

 Type                                 = System managed space

 Contents                             = User Temporary data

 State                                = 0x0000

   Detailed explanation:

     Normal

 

DB21011I  In a partitioned database server environment, only the table spaces

on the current node are listed.

 

 

 

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

转载于:http://blog.itpub.net/665930/viewspace-692857/