tidbit:DB2:Global temporary table indexes

Global temporary table is a great way to store session level data in DB2. However, it is important to understand, that these  are not 'in-memory' tables and performance of global temporary tables is much lower than arrays (DB2 9.5) or collections (DB2 9.7).

Another very important factor is to make sure to use indexes on global temporary tables. For example:

DECLARE GLOBAL TEMPORARY TABLE session.lookup_data ( row_id integer, value varchar(4000) ) NOT LOGGED on commit PRESERVE rows;

CREATE unique index session.lookup_data_idx ON session.lookup_data (row_id );

Index on global temporary table will provide huge performance improvement.

Another option is "NOT LOGGED" on table, which improves performance, but not drastically.

From a practical perspective, global temporary table are useful when we need to store millions of rows on a session level.

If the need is to store hundreds of rows, arrays are much faster.

As a practical case, recently I switched from session level tables to arrays and data processing performance jumped from 25 records/second to 550 records/second with the use of arrays.