Solved

DB2 Load spend same time for small and big table

Posted on 2006-06-20
4
613 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

803 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