DB2 9.7: Created global temporary tables

Learn how and when to use CGTTs

Understand the capabilities of created global temporary tables (CGTTs), a new feature in IBM® DB2® 9.7. This new type of temporary table utilizes persistent storage that can improve performance and manageability. This article covers the definition, syntax, specifics, advantages, and limitations of created global temporary tables. It also discusses how they are different from declared global temporary tables (available in previous versions of DB2), provides a short technical discussion on when and why to use them, and outlines some enablement aspects.

Share:

William B. McLaren, Senior Developer, IBM

William B. McLaren photoWilliam McLaren works in the DB2 Open Database Technologies team and specializes in SQL compatibility and migration analysis. William has over 15 years experience, including prior high OLTP performance tuning and database development on relational databases.



Ming W. Wu, DB2 QA Developer, IBM

Ming W. Wu photoMing Wu works in the DB2 LUW Database Functional Testing team. Ming has over 20 years experience in DB2, including OLTP performance, SAP, query optimizer, and concurrency optimization.



17 December 2009

Also available in Portuguese Spanish

Introduction

Develop skills on this topic

This content is part of a progressive knowledge path for advancing your skills. See DB2 fundamentals for Oracle professionals: Introduction to DB2

As also discussed in the DB2 9.7 for Linux, UNIX, and Windows Information Center (see Resources for a link), created global temporary tables are a new type of user-defined temporary tables introduced in DB2 9.7. Prior to this release, the declared temporary table (DGTT) was the only type of user-defined temporary table. The main difference between a DGTT and a CGTT is that the definition of a CGTT is stored persistently in the DB2 catalog. A DGTT is created and exist only for the duration of a user session. Unlike DGTTs, CGTTs are created in a session and persist after the session is terminated. The definition is shared across all concurrent sessions, even though the content of a created temporary table remains private to each session.

With both CGTT and DGTT, an application session can use a created temporary table to store intermediate result sets for manipulation or repeated references without interfering with concurrently running applications. The persistent definition of CGTT results in the following operational differences:

  • After an application session defines a created temporary table, concurrently running sessions do not have to redefine it.
  • You can reference a created temporary table in SQL functions, triggers, indexes, and views.

Also, any connection can refer to a created temporary table at any time without the need for a setup script to initialize the created temporary table. A connection can access only the rows that it inserts.

There are two major motivations for the creation of CGTT. First, CGTT behaves more like a regular table for SQL programmers, but with the potential of a performance advantage. CGTT tables can be defined up front, and users can use them much like regular tables. With data local to each session (hence no row locking required), and the no log option, CGTTs provide a potential performance advantage.

The second motivation for the CGTT offering is to assist the translations of non-DB2 temporary tables to DB2 temporary tables. It reduces the cost of such effort, hence making it easier to migrate to DB2.

Other DB2 family products, such as DB2 for z/OS®, and the SQL standard support created temporary tables.

The following sections will help to build your understanding of CGTTs and include a detailed technical discussion on when and why to use them; a description of their syntax and a working example on how to use them; and best practices for how to migrate DGTTs to CGTTs.

Prerequisites

This article is written for DB2 database developers and DB2 database administrators. A basic understanding of DB2 and scripting is required.

System requirements

DB2 9.7 Workgroup Server Edition for Linux®, UNIX®, and Windows®, or DB2 9.7 Enterprise Server Edition for Linux, UNIX, and Windows.


Technical discussion

This section discusses the behaviors of created temporary tables that should be taken into account.

CGTT has an option that allows no logging to occur. This can really help performance by setting the following option: not logged on commit delete rows. Not logged is the default behavior. This also means commit or rollback will result in truncating the table.

If the intent is to log all changes, then use: logged on commit preserve rows. The commit statement restores the session data to the previous commit or rollback statement. This option should be used if you want the same behavior as regular tables.

CGTT is much more efficient than regular tables. Because there is no locking requirement (all data is local), CGTT performs better than regular tables. If not logged is specified, the performances is even better. If you plan to truncate table, use the not logged option

If indexes are created on the CGTT table, the query compiler will take those indexes into account. Runstat in a particular session only takes the local data into account. This allows the potential of different access plans in each session.

Unless stated in the list below, CGTT has the same support as regular tables. However, it is worth noting the following:

  • CGTT supports import/export, although it does not support load.
  • Grant and revoke are permitted.
  • Indexes added in one session are visible to other sessions. However, if the session data conflicts with a new index, (such as duplicate rows conflicting with a unique index), the index is not materialized for that session until the data is corrected. It is best to have all the indexes defined during the preparation phase to avoid these confusions.
  • CGTTs do not support unique constraints or primary keys, but you can create a unique index.
  • CGTT does not support constraints. If you create a CGTT table using the LIKE clause (create CGTT like a regular table), the statement will be successful, but the CGTT will not inherit the constraints of the original table.
  • Table compression is on by default (if you purchase the option). However, compressing frequently accessed data may not be the best performance option. You are able to disable compression of CGTT tables.

You can create triggers for CGTT. CGTTs can also be used in the body of the trigger. The content of the CGTT is local to the session. You should use CGTTs instead of DGTTs when:

  • They are created often
  • Indexes are desired
  • You want to use it in a view

CGTT does not support:

  • rename, alter, reorg, refresh, nickname, comments, or lock statements
  • RI
  • Typed table, range partitions, or MDC tables
  • Being the base table of a materialized view
  • Being the base table of a secured table
  • XML columns

DB2 has several advantages over Oracle's implementation. Oracle's syntax will work in DB2 and will not need to be migrated in most cases. With DB2, you can:

  • Hash partition the data
  • Roll back conditions (CGTT allows flexibility for this)
  • Specify a tablespace, but it must be a user temporary
  • Choose to log or not

There are many different uses for CGTTs and cases in which it is beneficial to use them. Let's take a look at a few examples where it what would be a good use of CGTT capabilities:

  • An online shopping cart, where each online user has a CGTT for their shopping cart that is programmatically created and destroyed for their session and copied to a real table when they buy the goods.
  • Class enrollment, where each student has all of the course schedule selections, each student having his own bucket in the CGTT. Once the student accepts the selections, the selections are then moved to the real table of all course selections.
  • A company's benefit enrollment, where employees are making their benefit selections (healthcare, dental, 401k, and so on) for the next fiscal year. Once they commit their selections, they are moved from the CGTT to the real tables.

CGTTs are easier to maintain than regular tables because regular tables would have to be re-organized and not logging changes can reduce log contention issues for applications that do many transactions. It can also simplify tuning over DGTT because of supported use of indexes and logging options.


How to use DB2 CGTT

The code listings in this section show how CGTTs works in DB2. All of these can be executed from DB2 APIs and tools, including CLP, CLPPlus, Optim Database Administrator, and Optim Development Studio. The listings can be run in your own DB2 database. Make sure you change the dbname in the script to the correct name for your database, and then create the users user1 and user2 before running the test. This section shows that even after a commit, the data is not accessible from another session.

Listing 1 shows the SQL sytax for creating CGTT:

Listing 1. DB2 syntax
Create GLOBAL TEMPORARY TABLE <table_name> (
<column_name> <column_datatype>,
<column_name> <column_datatype>,
…  )
ON COMMIT [PRESERVE|DELETE] ROWS
[NOT LOGGED|LOGGED] ON ROLLBACK [PRESERVE|DELETE] ROWS 
DISTRIBUTE BY HASH ( col1,..)
IN <tspace-name>;

Listing 2 shows how to connect to the database, drop/create the global temp table, create aliases, grant all authorities to public, insert data into the table, and select a count of rows in the table.

Again, please make sure you replace dbname with your database name and user1 with your username in all the listings.

Listing 2. Connect to database and other actions
|--------10--------20--------30--------40--------50--------60--------70--------80--------|
CONNECT TO dbname USER user1;

DROP TABLE user1.gtt_test;

CREATE GLOBAL TEMPORARY TABLE user1.gtt_test
   ( col1   number(5),
     Col2   date default sysdate) 
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK DELETE ROWS;

DROP PUBLIC ALIAS gtt_test;

CREATE PUBLIC ALIAS gtt_test FOR user1.gtt_test;

GRANT ALL ON gtt_test TO PUBLIC;

INSERT INTO gtt_test ( col1 ) select rownum from syscat.tables where rownum < 100;

COMMIT;

SELECT COUNT(*) FROM gtt_test;

Listing 2 creates a public alias for the newly create global temporary table so that it can be referred to as gtt_test without having to qualify the table name. It also grants access to public, inserts data, and then does a select from the table.

When you run Listing 2, you should see the same results as in Listing 3. Listing 3 shows the result from the select statement. You should see a count of 99 from the select count(*) statement:

Listing 3. select count(*) results
>db2 -tvf gtt_1.sql 


SELECT COUNT(*) FROM gtt_test

1          

-----------

         99


  1 record(s) selected.

If you run the code in Listing 4 from another session, you will see that data is not shared between different sessions. In Listing 4, it connects to the database as a different user and selects count(*) from the created global temp table gtt_test. There is an alias on the table, so you do not need to specify the owner of the table.

Listing 4. Connecting to database as a different user
|--------10--------20--------30--------40--------50--------60--------70--------80--------|
CONNECT TO dbname USER user2;

SELECT count(*) FROM gtt_test;

When you run Listing 4, you should see the same results as in the Listing 5—a count of 0 from the select count(*) statement.:

Listing 5. select count(*) results
db2 "select count(*) from db2inst1.gtt_test";

1          

-----------

          0


  1 record(s) selected.

Listing 6 should be run from the first session established when running Listing 2. This selects all the data from table for the first few rows.

Listing 6. Selecting data from first few rows of table
|--------10--------20--------30--------40--------50--------60--------70--------80--------|
SELECT * FROM gtt_test where rownum < 5;

Run Listing 6 from the user1 session. This shows that the defaults work in Created Global Temporary Table and that the date was set in every row. Your results should look like Listing 7, where there are four rows displayed:

Listing 7. Results
$ db2 "SELECT * FROM gtt_test where rownum < 5";

COL1    COL2               

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

     1. 2009-07-10-12.34.41

     2. 2009-07-10-12.34.41

     3. 2009-07-10-12.34.41

     4. 2009-07-10-12.34.41


  4 record(s) selected

The next step is to run Listing 8, again from the user 1 session. This will show that truncating a Created Global Temporary Table works correctly.

Listing 8. Truncating a CGTT
|--------10--------20--------30--------40--------50--------60--------70--------80--------|
SELECT COUNT(*) from gtt_test;

TRUNCATE TABLE gtt_test;

SELECT * FROM gtt_test where rownum < 5;

This code selects all from the table, truncates it, and then selects all from it again to show that the data is gone. Verify that your results are the same as in Listing 9:

Listing 9. Results
db2inst1@mclaren-laptop:/home/mclaren/sql$ db2 -tvf gtt_4.sql 

SELECT COUNT(*) from gtt_test

1          
-----------
         99



  1 record(s) selected.



TRUNCATE TABLE gtt_test

DB20000I  The SQL command completed successfully.



SELECT * FROM gtt_test where rownum < 5


COL1    COL2               

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

  0 record(s) selected.

As you can see from the results, the data is truncated correctly.


Convert a declared global temporary table (DGTT) to a created global temporary table (CGTT)

DB2 supports DGTTs (declared global temporary tables) and CGTTs (create global temporary tables). Both are used to support per-session data. For some programming models, converting DGTTs to CGTTs can remove the necessity to declare temporary tables at the start of each user session. Because CGTTs are persistent, they can be created and shared for all sessions during system setup.

Compared to CGTT, DGTT has the following limitations:

  • DGTT is declared in a session. The table definition is persistent only to that session.
  • DGTT is not in the system catalog. This means that for all statements referencing DGTT, they have to be compiled prior to use. CGTT essentially moves the table setup process outside the session.
  • DGTT has the fixed schema SESSION. CGTT follows the schema rules as regular tables.

Listing 9 shows an example of a declared temporary table. You will convert this DGTT to a CGTT in a later listing.

Listing 10. Example declared global temporary table
declare global temporary table dgttbase1 
           ( B1_c1	integer,
             B1_C2	varchar(20),
	     B1_c3      varchar(20)
           )  on commit preserve rows;
insert into SESSION.dgttbase1 values (1,'R2_c1 is 1', 'inserted row');
insert into SESSION.dgttbase1 values (2,'R2_c1 is 2', 'inserted row');
connect reset;

connect to dbname;
insert into SESSION.dgttbase1 values (3,'R2_c1 is 3', 'inserted row');
select * from SESSION.dgttbase1;
connect reset;

Table SESSION.dgttbase1 is not visible to the second session. To use dgttbase1 in the second session, you have to recreate the table in the session again. Listing 11 shows the output from the commands. At the end of the session, DGTT is no longer there.

Listing 11. Command output
declare global temporary table dgttbase1 ( B1_c1      integer, 
                                           B1_C2      varchar(20),
                                           B1_c3      varchar(20) 
                                         )  on commit preserve rows
DB20000I  The SQL command completed successfully.

insert into SESSION.dgttbase1 values (1,'R2_c1 is 1', 'inserted row')
DB20000I  The SQL command completed successfully.

insert into SESSION.dgttbase1 values (2,'R2_c1 is 2', 'inserted row')
DB20000I  The SQL command completed successfully.

onnect reset
DB20000I  The SQL command completed successfully.

connect to dbname

insert into SESSION.dgttbase1 values (3,'R2_c1 is 3', 'inserted row')
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "SESSION.DGTTBASE1" is an undefined name.  SQLSTATE=42704

select * from SESSION.dgttbase1
SQL0204N  "SESSION.DGTTBASE1" is an undefined name.  SQLSTATE=42704

connect reset
DB20000I  The SQL command completed successfully.

Listing 12 shows how the DGTT table can be converted to CGTT. The syntax for CGTT is very similar to DGTT. Contrasting to DGTT, during the second session, the definition of cgttbase1 is still valid.

Listing 12. Converting DGTT to CGTT
Connect to <dbname>
create global temporary table cgttbase1 
           ( B1_c1	integer,
             B1_C2	varchar(20),
             B1_c3      varchar(20)
           )  on commit preserve rows;
connect reset;

insert into cgttbase1 values (1,'R2_c1 is 1', 'inserted row');
insert into cgttbase1 values (2,'R2_c1 is 2', 'inserted row');

Connect reset;
Connect to <dbname>
insert into cgttbase1 values (3,'R2_c1 is 3', 'inserted row');
insert into cgttbase1 values (4,'R2_c1 is 4', 'inserted row');
select * from cgttbase1;
connect reset;

Once CGTT table cgttbase1 is created, it is persistent to all sessions that follow.

Listing 13. Output from the above statements
Connect to dbname
create global temporary table cgttbase1 ( B1_c1     integer, 
                                          B1_C2     varchar(20), 
                                          B1_c3     varchar(20) 
                                        )  on commit preserve rows
DB20000I  The SQL command completed successfully.
connect reset


connect to dbname
insert into cgttbase1 values (1,'R2_c1 is 1', 'inserted row')
DB20000I  The SQL command completed successfully.

insert into cgttbase1 values (2,'R2_c1 is 2', 'inserted row')
DB20000I  The SQL command completed successfully.
Connect reset

Connect to dbname
insert into cgttbase1 values (3,'R2_c1 is 3', 'inserted row')
DB20000I  The SQL command completed successfully.
insert into cgttbase1 values (4,'R2_c1 is 4', 'inserted row')
DB20000I  The SQL command completed successfully.

select * from cgttbase1

B1_C1       B1_C2                B1_C3               
----------- -------------------- --------------------
          3 R2_c1 is 3           inserted row        
          4 R2_c1 is 4           inserted row        

  2 record(s) selected.
connect reset

Conclusion

Created global temporary tables (CGTTs) are a new feature in DB2 9.7 that provide the following new capabilities:

  • Index support
  • Trigger support
  • Support for using in views
  • Persistent definition
  • Logging options
  • Hash partition option
  • Rollback flexibility

CGTTs provide global persistent definition, allowing all user sessions to access the same table definition without having to declare it at startup. For some existing DB2 applications, CGTTs can replace DGTTs to simplify their programming model. For example, being able to use views and trigger support can make writing the application less complex by having these options available. It is easier to maintain than regular tables because CGTTs reduce the need for maintenance. Tuning can be made easier with the CGTT support of indexes. And, CGTTs, when compared with regular and DGTT tables, offer greater flexibility and performance by exercising the new logging commit options.

CGTTs are also useful when you are migrating applications from other databases to DB2. For example, Oracle temporary tables can be easily adapted as DB2 CGTTs.

There are many advantages to CGTTs. You should now have the understanding you need to use them in your applications.

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=454801
ArticleTitle=DB2 9.7: Created global temporary tables
publish-date=12172009