EXCESSIVE TIME TO LOAD DB2/6000 TABLE.
ITEM: RTA000037266
QUESTION:
I have just installed a 7013-580 with 512MB of memory and 12GB of
disk and installed DB2/6000. I have a question about the time it takes
to load a table. My customer is planning on having several large tables
on this machine (running from 24MB up to 200MB) that will be downloaded
from their mainframe nightly and loaded onto the RS/6000. We calculated
the network time and that seems stable but it is taking us a very long
time to load the tables. The 24MB table took over 2 hours. I was wond-
ering if it has anything to do with logging or any other performance
aspect of DB2/6000. Nothing else is running on the machine and the time
the loads are taking is unacceptable.
---------- ---------- ---------- --------- ---------- ----------
A: I agree we can get better performance than this, but there will
be no one "magic bullet" that improves performance.
All the following are areas to investigate that will improve
performance:
1. Configuration parameters - The system as installed comes configured
adequately for smaller systems and smaller databases. Larger
systems such as yours definitely need to change these configuration
parameters. They are documented in the Administration Guide,
Chapter 9.
2. There is a chapter of the Administration Guide covering this
topic. See Chapter 8 - Performance Considerations.
3. I assume you are using the import command. Increase the
commit count to group loading of rows into larger commit points.
4. Consider purchasing the Bridge Corp. product: Bridge FastLoad.
This product provides very fast bulk loading through bypassing
the DB2 internal processes and logging.
5. Pre-Sort the input data according to the table clustering index.
6. If you are going to replace the table with a new version, the
best strategy is to drop the table indexes, perform the load,
re-create the indexes, and then perform runstats on the table.
Creating the index before the load causes the database to
have to process the index during load which will slow down
the load operation and potentially result in a poorly organized
index.
7. Move the logging devices to a device that does not also have
database tables. And move the jfslogs to the center of a device
that does not have database tables. When loading rows to a table
the data being loaded has to be logged as well as added to the
database table. All writes to AIX journaled filesystems have to
be logged in the jfslog. For best performance, these three
should not be on the same disk head or there will be excessive
head movement and iowait.
8. You may be encountering poor system performance in general from
the AIX standpoint. While the load is running, enter the
command "vmstat 5 10" and give me the results. Let's see what
AIX thinks is your bottleneck.
---------- ---------- ---------- --------- ---------- ----------
This item was created from library item Q649794 CNPMG
Additional search words:
CNPMG DB2 EXCESSIVE IX JAN94 LOAD LOADER OZNEW RISCDB2 RISCSYSTEM
SOFTWARE TABLE TIME TIMER 6000
WWQA: ITEM: RTA000037266 ITEM: RTA000037266
Dated: 01/1995 Category: RISCDB2
This HTML file was generated 99/06/24~12:43:14
Comments or suggestions?
Contact us