For obvious reasons I don't know how many of you who are familiar with global temporary tables in DB2. The first time I read about them they seemed pretty useless to me. Then some day they came in handy for me and I will share this experience with you.

I will start with the disadvantages. A global temporary table can only contain data within the same unit of work. The data cannot be shared with other units of work and they are your private data. You cannot issue SQL UPDATE against a global temporary table and you cannot create an index on a global temporary table.

An now a useful example. Imagine a SQL statement where you have to use the same subselect twice or more. This subselect contains a WHERE clause that is often subject to changes and the WHERE clause drastically reduces the number of rows that the subselect returns compared to the total number of rows in the table from which the subselect reads. Maybe the original SQL statement looks like this:

SELECT *
FROM TABLE_A TA, TABLE_B TB
WHERE TA.COLUMN_A IN
( SELECT COLUMN_X FROM TABLE_X
  WHERE COLUMN_Y = 'DELTA'
) AND TB.COLUMN_B IN
( SELECT COLUMN_X FROM TABLE_X
  WHERE COLUMN_Y = 'DELTA'
) AND TA.JOIN_COLUMN = TB.JOIN_COLUMN

Now you want to optimize this SQL statement. First thing is to create a global temporary table containing the column that your subselect returns. This is done only once by issuing the following SQL statement:

CREATE GLOBAL TEMPORARY TABLE GLOBAL_X
(COLUMN_X INTEGER NOT NULL)

COLUMN_X must of course have the same column type as i TABLE_X. If you like others to use your brilliant table GLOBAL_X you must remember to issue a GRANT ALL ON GLOBAL_X TO OTHERS. You have to decide who OTHERS are but PUBLIC is always good to use. Now we can translate the original SQL statement into two SQL statements. The last of these two statements will return the same result as the original one:

INSERT INTO GLOBAL_X (COLUMN_X)
SELECT COLUMN_X FROM TABLE_X
WHERE COLUMN_Y = 'DELTA'
;
SELECT *
FROM TABLE_A TA, TABLE_B TB
WHERE TA.COLUMN_A IN
( SELECT COLUMN_X FROM GLOBAL_X )
AND TB.COLUMN_B IN
( SELECT COLUMN_X FROM GLOBAL_X )
AND TA.JOIN_COLUMN = TB.JOIN_COLUMN

This is a very useful trick in SPUFI, especially if you often are changing 'DELTA' to other values. And if GLOBAL_X contains a small percentage of the rows i TABLE_X you will also experience that your query runs a lot faster.

I have used the method above to make some almost recursive SQL where I needed the same subselect for every new recursion. The results of the individual recursions were gathered together into a single result using a lot of UNION's. You can read more about CREATE GLOBAL TEMPORARY TABLE by clicking here.