?
Solved

DB2 Load spend same time for small and big table

Posted on 2006-06-20
4
Medium Priority
?
633 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 375 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

771 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