About 10 month ago I wrote a tip about global temporary tables in DB2. Unfortunately this tip only contains information about the kind of global temporary tables you create using SQL CREATE. In DB2 version 6 the possibility of using global temporary tables created using DECLARE were introduced. The primary difference is that temporary tables created using DECLARE only exists in DB2 from the first execution of the DECLARE of the table and until the DB2 thread is terminated. Normally this is for the duration of the program execution. After program termination all traces of the table will vanish into thin air.

I will recommend the use of DECLARE GLOBAL TEMPORARY TABLE prior to CREATE GLOBAL TEMPORARY TABLE. Before you start using the DECLARE version you need some extra information. First of all you might not be able to use the DECLARE version at your installation. The DB2 systems programmer can prevent the use of the DECLARE version, but you will not discover this until execution time. Maybe your test system allows it, but not the production system, so you have to try it first before you build your full blown application. Secondly you must use the creator SESSION in order to make a reference to your DECLAREd table in other SQL statements than the DECLARE. This might cause you problems in static SQL because SQL statements referring to a DECLAREd table will be bound at runtime and/or maybe your installation does not allow fully qualified table names. In dynamic SQL (SPUFI, QMF, SAS, DSNTIAUL and others) DECLAREd tables can be used without any problems.

One of the most important advantages by using the DECLARE version is that data in the DECLAREd table can survive COMMIT and ROLLBACK according to the normal rules for DB2 data integrity. Here is an example:

DECLARE GLOBAL TEMPORARY TABLE MYTABLE
(MYCOLUMN CHAR(8)) ON COMMIT PRESERVE ROWS;
INSERT INTO SESSION.MYTABLE VALUES('ROWNO. 1');
INSERT INTO SESSION.MYTABLE VALUES('ROWNO. 2');
COMMIT;
INSERT INTO SESSION.MYTABLE VALUES('ROWNO. 3');
SELECT * FROM SESSION.MYTABLE;

The last SELECT will fetch all three rows. If ON COMMIT PRESERVE ROWS is omitted the last SELECT will only fetch ROWNO. 3, because data will not survive the COMMIT under these circumstances. There are a lot of other possibilities when using the DECLARE version. Read more about them in the SQL reference.