Saturday, June 22, 2013

0 DGTT and CGTT

DGCC: Does not appear in the system catalog, which makes them not persistent for use by other application/session. The table would disappear after session is terminate.

CGTT: Does exist in the system catalog. Can share by other application.

Both temporary table are created in the USER temporary table space. NOT in SYSTEM temporary table space.

 

DGTT

To refer the DGTT, we must use SESSION as qualify schema name.

db2 "declare global temporary table DGTT_DEMO1( c1 integer,  c2  varchar(20) )  on commit preserve rows"
 
db2 "insert into SESSION.DGTT_DEMO1 values (1,'row1')"
db2 "insert into SESSION.DGTT_DEMO1 values (2,'row2')"
db2 "select * from SESSION.DGTT_DEMO1"
 
db2 connect reset
 
db2 connect to sample
 
db2 "select * from SESSION.DGTT_DEMO1"

image

If we reset the connection, the table would not be present and get the SQL0204 error when we try to use it.

CGTT

The CGTT exist in the system catalog with the type ‘G’. The table would be preserve but the data would be gone after session reset.

db2 "create global temporary table CGTT_DEMO1( c1 integer,  c2  varchar(20) )  on commit preserve rows"
 
db2 "insert into CGTT_DEMO1 values (1,'row1')"
db2 "insert into CGTT_DEMO1 values (2,'row2')"
db2 "select * from CGTT_DEMO1"
 
db2 connect reset
 
db2 connect to sample
 
db2 "select * from CGTT_DEMO1"
db2 "select tabschema,tabname ,type from syscat.tables  where tabschema=current schema  and tabname = 'CGTT_DEMO1'"

image

Reference

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates