Solved

DB2 Load spend same time for small and big table

Posted on 2006-06-20
4
604 Views
Last Modified: 2008-01-09
We are using db2 load to import data to a new database. We follow IBM Db2 Load guide and create all tables and indexes and start db2 load.
After 2h30 it imports just 15Gb (original database = 60Gb).
Looking the db2diag.log we can see how much time db2 load spend for each table.
For a small table (just 1 row) it spends 3 seconds. For a big table it spends 1 minute to import 1 million rows.

Anyone could help us with this thing???

Thanks in advance.

Emerson
0
Comment
Question by:tobar
4 Comments
 
LVL 3

Expert Comment

by:granbajo
ID: 16940902
Emerson,

  In reality, DB2 has to do certain things for each table no matter how bif or small it is... like opening the input file, read the data, load the table, release and close the file, so there is going to be a certain overhead there!

  In my experience, I found that it is quicker to load 1 big table than many small tables with the same amount of data.

  One thing that it might be worth checking, is the data local or are you having to get it from a network drive?  3 seconds seems a bit much for a table with one row.  Are there any dependencies?
0
 

Author Comment

by:tobar
ID: 16940980
I agree with you about the process (open, read load....), but in this case all of files are located on databae server and there is no dependencies.
Another detail is for each table imported DB2 writes 24 lines on db2diag.log. May be this process retards the import?

Regards
0
 
LVL 57

Expert Comment

by:giltjr
ID: 16948198
Logging on an initial load will cause performance issues.  Not sure about the platform you are running on, but I know on z/OS when you do an initial load of a table you can have it not log.

 If you have to have it log, then make sure that the log is on a different physical drive or array, that is reduce I/O contention as much as possible.

You should also make sure that the input file is on a different physical drive or arrary.
0
 
LVL 13

Accepted Solution

by:
ghp7000 earned 125 total points
ID: 16951111
you must specify what you mean by load or import-DB2 can do both. If you are importing large tables you are using the slowest method possible-large tables must be loaded using the load command, not the import command.
So please specify which utility you are using in your command ---LOAD or IMPORT?
Could you also specify platform/version and post a sampe of the db2diag.log entry please
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now