468,140 Members | 1,463 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,140 developers. It's quick & easy.

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

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.
Nov 12 '05 #1
2 2131
Which platform are you on?
Cheers
Serge
Nov 12 '05 #2
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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by David Parker | last post: by
reply views Thread by dataguy | last post: by
2 posts views Thread by Bruce Lawrence | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.