Does anyone have experience with the efficiency of global temporary tables?

[Chuck Crews]

I am interested in declaring a global temporary table within an
application. The application processes 1 set of 600 or less rows each
iteration.
Multiple programs can and do call this one application at the same
time. Today, we are using a permanent DB2 table and getting all
kinds of locking even with row level locking on. In addition, our
performance is slow as we do two inserts and two deletes on each row
per program iteration. The program can be called up to 5 or 6
thousand time in one run. Or, it may only be called once.

The bottom line: What potential problems are there with declaring a
temporary table on the fly? Is this efficient or does it cost much
overhead?

Chuck
Russell Corp.

[Serge Rielau]

Which platform are you on?
Cheers
Serge

[Roger]

Chcuk, yes we are using global temporary tables in our app (ERP) and
the performance is good. My recomendations are :
- Create a separate tablespace for the session tables
- DECLARE the GLOBAL TEMPORARY TABLE with ON COMMIT PRESERVE ROWS IN
your_table_space_for_session tables to avoid deleting all rows when
you commit a transaction. Default behavior of a declared temporary
table is to delete all rows from the table when you commit a
transaction.
- Add NOT LOGGED to the DECLARE statement to avoid logging of the
tables

Read more here http://publib.boulder.ibm.com/infoce...help/index.jsp
,search for DECLARE GLOBAL TEMPORARY