473,727 Members | 2,033 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Performance of load from a cursor .... federated cursor?

Sorry for quoting an old post and probably I am reading out of context
so my concern is unfounded. But I would appreciate if I can get someone
or Serge to confirm. Also unlike the question asked in the post below,
my question involves non-partitioned table loads.

I want to know if, in general, loading from cursor is slower than
loading from a file? I was thinking cursor would normally be faster,
because of DB2's superior buffer/prefetch management than that of OS's
(AIX).

Now to my specific question. I need to transfer huge amount of data
from one DB to another DB, both are DB2 8.2.4 running on the same
server. Would I be better off exporting data to files and then loading
from the files, or, will it be faster to define nicknames and load from
federated cursors, since it will avoid having to create files?

Thanks

P Adhia
From: Serge Rielau - view profile
Date: Wed, Jan 26 2005 10:31 am
Email: Serge Rielau <srie...@ca.ibm .com>
Groups: comp.databases. ibm-db2
Not yet rated
Rating:
show options

Joachim Klassen wrote:
Hi all, first apologies if this question looks the same as another one I recently
posted - its a different thing but for the same szenario:-). We are having performance problems when inserting/deleting rows from a large
table.
My scenario: Table (lets call it FACT1) with 1000 million rows distributed on 12
Partitions (3 physical hosts with 4 logical partitions each).
Overall size of table is 350 GB. Each night 1.5 Million new rows will be
added
--snipped--

I'm not privy of index maintenance internals, but could it be the 7
indexes cause a spill of some heap? Maybe sort heap? Have you checked
the snapshots?
Have you verified that the plans are good? You shouldn't see any TQs.
Also are you sure you don't have any other complicating factors (SQL
Functions, Triggers, check or RI constraints) (The plans will show). PPS: We are parallel investigating in MDC tables, using smaller tables (and
combining them with a UNION ALL view) and the use of LOAD FROM CURSOR
instead of INSERT


Be careful with LOAD FROM CURSOR, the cursor is a bottle neck. To do
that in a scalable fashion you would fire up concurrent LOADs on each
node filtering the source by DBPARTITION.
You shouldn't need UNION ALL.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Apr 27 '06 #1
7 6568
P. Adhia wrote:
Sorry for quoting an old post and probably I am reading out of context
so my concern is unfounded. But I would appreciate if I can get someone
or Serge to confirm. Also unlike the question asked in the post below,
my question involves non-partitioned table loads. If I'm not someone. Does that make me a no-one? ;-)
I want to know if, in general, loading from cursor is slower than
loading from a file? I was thinking cursor would normally be faster,
because of DB2's superior buffer/prefetch management than that of OS's
(AIX). Loading from CURSOR is slower. I guess this has to do with the codepath
involved.
It's not about merely getting the data out of wherever it is and LOAD is
darn efficient reading that flat file fast.
In non DPF experiments (on DB2 V8 for LUW) indicate that cursor load is
about half as fast as ascii delimited which in turn is about half as
fast as IXF.
(e.g. 14GB/hr/CPU ... 51GB/hr/CPU on the reference system)
Now to my specific question. I need to transfer huge amount of data
from one DB to another DB, both are DB2 8.2.4 running on the same
server. Would I be better off exporting data to files and then loading
from the files, or, will it be faster to define nicknames and load from
federated cursors, since it will avoid having to create files?

Good question. I don't have data on EXPORT and that's the price you pay.
(Or the "High Performance Unloader" tools which is quite literally a
price you pay)
One question to answer: Do you have enough disk to hold the IXF or the
ASC file?

<DB2 Viper Advertisement>
You can use a new DATABASE option for the cursor load which improves
usability and performance:
If ABC.TABLE1 resides in a database different from the database
ABC.TABLE2 is in, the DATABASE, USER, and USING options of the DECLARE
CURSOR command can be used to perform the load. For example, if
ABC.TABLE1 resides in database DB1, and the user ID and password for
DB1 are user1 and pwd1 respectively, executing the following commands
will load all the data from ABC.TABLE1 into ABC.TABLE2:

db2 declare mycurs cursor database DB1 user user1 using pwd1 for
select two,one,three from abc.table1

db2 load from mycurs of cursor insert into abc.table2

</DB2 Viper Advertisement>

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 28 '06 #2
PS: Here is the link to the paper I'm parroting:
ftp.software.ib m.com/software/data/pubs/papers/loaderperf.pdf

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 28 '06 #3
In article <4b************ *@individual.ne t>, sr*****@ca.ibm. com says...

db2 declare mycurs cursor database DB1 user user1 using pwd1 for
select two,one,three from abc.table1


Hi Serge,
I found some info about this way of defining a cursor at
http://tinyurl.com/gk8v7 but not in the 'DECLARE CURSOR' docs. Should it
be there and is this going to be updated or is it described somewhere
else?
It would be great if it would also be possible in a regular select
statement.
Apr 28 '06 #4
Gert van der Kooij wrote:
In article <4b************ *@individual.ne t>, sr*****@ca.ibm. com says...
db2 declare mycurs cursor database DB1 user user1 using pwd1 for
select two,one,three from abc.table1


Hi Serge,
I found some info about this way of defining a cursor at
http://tinyurl.com/gk8v7 but not in the 'DECLARE CURSOR' docs. Should it
be there and is this going to be updated or is it described somewhere
else?
It would be great if it would also be possible in a regular select
statement.

It has the smell of a CLP command This is not your regular cursor
definition.
Am I right that you are gearing towards 4 part names without need to
create nicknames? _dbname_.schema .table.column

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 28 '06 #5
In article <4b************ *@individual.ne t>, sr*****@ca.ibm. com says...
Gert van der Kooij wrote:
In article <4b************ *@individual.ne t>, sr*****@ca.ibm. com says...
db2 declare mycurs cursor database DB1 user user1 using pwd1 for
select two,one,three from abc.table1


Hi Serge,
I found some info about this way of defining a cursor at
http://tinyurl.com/gk8v7 but not in the 'DECLARE CURSOR' docs. Should it
be there and is this going to be updated or is it described somewhere
else?
It would be great if it would also be possible in a regular select
statement.

It has the smell of a CLP command This is not your regular cursor
definition.
Am I right that you are gearing towards 4 part names without need to
create nicknames? _dbname_.schema .table.column

Cheers
Serge


You're right, I found it at http://tinyurl.com/gbpeg.
The 4 part name would be a great solution, but I guess authentication
against the remote database could be an issue.
Apr 28 '06 #6
Gert van der Kooij wrote:
In article <4b************ *@individual.ne t>, sr*****@ca.ibm. com says...
Gert van der Kooij wrote:
In article <4b************ *@individual.ne t>, sr*****@ca.ibm. com says...

db2 declare mycurs cursor database DB1 user user1 using pwd1 for
select two,one,three from abc.table1

Hi Serge,
I found some info about this way of defining a cursor at
http://tinyurl.com/gk8v7 but not in the 'DECLARE CURSOR' docs. Should it
be there and is this going to be updated or is it described somewhere
else?
It would be great if it would also be possible in a regular select
statement.

It has the smell of a CLP command This is not your regular cursor
definition.
Am I right that you are gearing towards 4 part names without need to
create nicknames? _dbname_.schema .table.column

Cheers
Serge


You're right, I found it at http://tinyurl.com/gbpeg.
The 4 part name would be a great solution, but I guess authentication
against the remote database could be an issue.

4 part name would provide the usability solution but apparently not the
performance boost since the data would need to be selected from the
remote DB, then pipe through DB2's runtime engine and then into the LOAD
utility.
In the DB2 Viper case the load utility connects directly to the remote DB.

Cheers
Serge

PS: This doesn't mean that 4-part-names aren't good in their own right.
DB2 Vipers main improvement for federation (as I see it) is full 2-phase
commit support. That is you can finally write e.g. triggers which modify
nicknames.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 28 '06 #7
Serge Rielau wrote:
If I'm not someone. Does that make me a no-one? ;-)
That makes you a quite-a-one :) (some<->quite -- I know, poor attempt at
humor)
Loading from CURSOR is slower. I guess this has to do with the codepath
involved.
OK. I am a bit surprised. I would have thought improved IO throughput at
the cost of higher CPU utilization is a better trade-off (assuming
system is not CPU constrained.)
It's not about merely getting the data out of wherever it is and LOAD is
darn efficient reading that flat file fast.
In non DPF experiments (on DB2 V8 for LUW) indicate that cursor load is
about half as fast as ascii delimited which in turn is about half as
fast as IXF.
(e.g. 14GB/hr/CPU ... 51GB/hr/CPU on the reference system)
Thanks for saving me the trouble of benchmarking myself (which wouldn't
be as accurate anyway).

So can I interpret this as, cursor+load is still better than
cursor(export)+ write_file+read _file+load? That is, if the source data
resides in a DB2 table and needs to be loaded to another table. Or is
the combination cursor+load like a negative synergy?
Good question. I don't have data on EXPORT and that's the price you pay.
(Or the "High Performance Unloader" tools which is quite literally a
price you pay)
One question to answer: Do you have enough disk to hold the IXF or the
ASC file?


Yes I do have enough space. Unloading to ixf files is what I have been
doing.

I don't have much hands-on experience with federation, so I wasn't sure
if federation will add any significant overhead, to basic cursor+load
process. For example, does federation use networking services even if
federated DB resides on the same physical machine?

Thanks for the prompt answers and the link for the load performance doc,
it definitely looks to be very helpful for what I am doing.

P Adhia

PS: Just some non-scientific numbers: one iteration I ran, involved
exporting 350G+ DB2 data, not counting indexes, to ixf and it took about
6 hours with 6 processes running concurrently on 2 CPU PowerPC 1.65GHz
64 bit machine. The load of the same data on same machine took 36 hours,
with significant time spent in building indexes. Due to lack of temp
space, I couldn't run multiple loads concurrently, but when I run next
iteration, I'll add more temp space and run multiple loads concurrently.
Apr 28 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
2962
by: Terry | last post by:
Hi All, Im trying to run db2expln over some sql in order to find out why its so slow. I receive the following error message. LINE MESSAGES FOR DYNEXPLN.sqc ------ -------------------------------------------------------------------- SQL0060W The "C" precompiler is in progress.
0
1765
by: Joerg Ammann | last post by:
hi, os: aix 4.3.3 DB2: version 7 FP3 we are using a federated DB setup, datasource and fed-Db are both V7FP3 (in fact they are on the same server) and are having massiv performance problems. i tracked it back to the way the queries are push-downed to the
3
6917
by: Joachim Klassen | last post by:
Hi all, first apologies if this question looks the same as another one I recently posted - its a different thing but for the same szenario:-). We are having performance problems when inserting/deleting rows from a large table. My scenario: Table (lets call it FACT1) with 1000 million rows distributed on 12
4
606
by: uthuras | last post by:
Greetings all, Is it possible to have federated db feature implemented among DB2 family? I intend to create federated within DB2 databases. I have 2 databases TestA and TestB. I have some base tables in TestA database and the reference tables in TestB database. Now i want to select data from TestB database referencing base table in TestA database. One of the way would be Federated Database. I've seen some red books on setting up...
5
3342
by: Klemens | last post by:
I get SQL30090 reason 18 by trying to do an insert in a federated table and an update in a local table in one transaction Do I have to change some settings to get done or ist this not possible by definition? Thanks Klemens
0
4412
by: Peter | last post by:
When I issue call sqlj.install_jar('file:///f:/jars/mail.jar','MAIL'); I get the messages SQL4301N Java or .NET interpreter startup or communication failed, reason
14
4441
by: peteh | last post by:
Hi All; We have many production jobs that "load from cursor" to a UDB/AIX 8.2 (with dpf) data warehouse from source tables residing Oracle 9i. Since Oracle dates are (roughly) equivalent to DB2 timestamps, we frequently use the date() function to "convert" from the Oracle date datatype to the DB2 date datatype. We have used this technique on over 20 Oracle tables for several months with no problem. One table in particular fails with a...
4
4616
by: esmith2112 | last post by:
I have a query running on a federated database that takes the form select col1, col2 from nickname1 where <conditions exist> union all select col1,col2 from nickname2
7
1778
by: Andres Rormoser | last post by:
I'm having a performance Issue with my Production SQL Server (2 x Xeon Dual-Core Woodcrest 1.6Ghz, 2GB RAM, IIS, SQL Server). In general the querys take much longer than the querys in my development server . For example a recursive UDF takes 20s in my development server and 2m in my production server (both with same users load) but my production server it's much hardware powerfull than the other server. I start monitoring an realized...
0
8891
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9406
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9260
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9185
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8103
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6703
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3228
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.