Created global temporary tables
Learn how and when to use CGTTs
This content is part # of # in the series: DB2 9.7
This content is part of the series:DB2 9.7
Stay tuned for additional content in this series.
As also discussed in the DB2 9.7 for Linux, UNIX, and Windows Information Center (see Related topics 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.
This article is written for DB2 database developers and DB2 database administrators. A basic understanding of DB2 and scripting is required.
DB2 9.7 Workgroup Server Edition for Linux®, UNIX®, and Windows®, or DB2 9.7 Enterprise Server Edition for Linux, UNIX, and Windows.
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
LIKEclause (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
- 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(*) 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(*) 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
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.
- DB2 for Linux, UNIX, and Windows area on developerWorks: Get the resources you need to advance your skills on DB2 9.7.
- DB2 9.7 for Linux, UNIX, and Windows Information Center: Find information describing how to use the DB2 family of products and features, as well as related WebSphere Information Integration products and features.
- IBM DB2 for Linux, UNIX, and Windows: Download a free trial version of DB2 for Linux, UNIX, and Windows.
- IBM DB2 Express-C: Download DB2 Express-C, free.