By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,823 Members | 730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,823 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
Which platform are you on?
Cheers
Serge
Nov 12 '05 #2

P: n/a
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.