By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,227 Members | 1,456 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,227 IT Pros & Developers. It's quick & easy.

Slow prepare times for inserts

P: n/a
TC
I am having an issue where inserts are taking a lot longer than I would
expect. So far it seem that it is just the first insert. (Although, I
have other people telling me it is happening more than just the firs
time) The times for a single insert are anywhere from 2-10 seconds. I
can't imagine what takes that long. We use JDBC and the bulk of this
time is on the prepare of the statment. Does anyone have any ideas on
what could be causing this?

Sep 27 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
TC wrote:
I am having an issue where inserts are taking a lot longer than I would
expect. So far it seem that it is just the first insert. (Although, I
have other people telling me it is happening more than just the firs
time) The times for a single insert are anywhere from 2-10 seconds. I
can't imagine what takes that long. We use JDBC and the bulk of this
time is on the prepare of the statment. Does anyone have any ideas on
what could be causing this?
Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 27 '06 #2

P: n/a
TC
Serge Rielau wrote:
Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).
Thanks for your response. We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge. We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit) These functions emulate equivilent functions that
exist in Oracle. Is it possible that these functions are causing this
issue? I will try to experiment with taking these out and running
another plan. If this is the cause, I am not sure what I am going to
do about it. We rely on these functions pretty heavily.

Sep 28 '06 #3

P: n/a
TC
Serge Rielau wrote:
Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).
Thanks for your response. We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge. We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit) These functions emulate equivilent functions that
exist in Oracle. Is it possible that these functions are causing this
issue? I will try to experiment with taking these out and running
another plan. If this is the cause, I am not sure what I am going to
do about it. We rely on these functions pretty heavily.

Sep 28 '06 #4

P: n/a
TC
Serge Rielau wrote:
Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).
Thanks for your response. We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge. We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit) These functions emulate equivilent functions that
exist in Oracle. Is it possible that these functions are causing this
issue? I will try to experiment with taking these out and running
another plan. If this is the cause, I am not sure what I am going to
do about it. We rely on these functions pretty heavily.

Sep 28 '06 #5

P: n/a
TC
Serge Rielau wrote:
Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).
Thanks for your response. We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge. We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit) These functions emulate equivilent functions that
exist in Oracle. Is it possible that these functions are causing this
issue? I will try to experiment with taking these out and running
another plan. If this is the cause, I am not sure what I am going to
do about it. We rely on these functions pretty heavily.

I just removed the calls to the MTK toolkit and the graph in the
explain plan became very simple.

Sep 28 '06 #6

P: n/a
TC
Serge Rielau wrote:
Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).
Thanks for your response. We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge. We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit) These functions emulate equivilent functions that
exist in Oracle. Is it possible that these functions are causing this
issue? I will try to experiment with taking these out and running
another plan. If this is the cause, I am not sure what I am going to
do about it. We rely on these functions pretty heavily.

BTW, I tested it and when I get rid of the MTK function calls the
explain graph gets very simple.

Sep 28 '06 #7

P: n/a
TC
Serge Rielau wrote:
Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).
Thanks for your response. We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge. We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit) These functions emulate equivilent functions that
exist in Oracle. Is it possible that these functions are causing this
issue? I will try to experiment with taking these out and running
another plan. If this is the cause, I am not sure what I am going to
do about it. We rely on these functions pretty heavily.

BTW, I tested it and when I get rid of the MTK function calls the
explain graph gets very simple.

Sep 28 '06 #8

P: n/a
TC wrote:
Serge Rielau wrote:
>Does the target table have triggers or RI constraints?
If there are triggers are some of them AFTER o INSTEAD OF triggers which
in turn modify tables with triggers, etc, etc.
A quick look at the plan will show whether the INSERT in simple (just a
few operators in visual explain) or nasty (pages of stuff in explain).

Thanks for your response. We do not have triggers or RI contstraints.
I had just noticed an article that said you could do an explain plan on
inserts. I ran one and it was huge. We did migrate from Oracle so we
have made use of a set of UDFs provided by IBM called the MTK
(Migration Toolkit) These functions emulate equivilent functions that
exist in Oracle. Is it possible that these functions are causing this
issue? I will try to experiment with taking these out and running
another plan. If this is the cause, I am not sure what I am going to
do about it. We rely on these functions pretty heavily.

BTW, I tested it and when I get rid of the MTK function calls the
explain graph gets very simple.
Care to post the INSERT statement.
While ORA8.* SQL UDF will certainly blow up you graph I'm surprised that
teh result is huge unless you have a lot of them. then th eqyestion is: Why?

After you have successfully migrated the function with MTK you need to
take alook at teh slo areas and first decide whether soem emulatiosn of
the MTK are really needed.
E.g. all the code injected by MTK for "emtpy string equals NULL" won't
be needed if you don't use NULLs.
The MTK is very liberally adding ORA8.* functions even if the
differences to DB2 functions are pathological in nature.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 28 '06 #9

P: n/a

TC wrote:
I am having an issue where inserts are taking a lot longer than I would
expect. So far it seem that it is just the first insert. (Although, I
have other people telling me it is happening more than just the firs
time) The times for a single insert are anywhere from 2-10 seconds. I
can't imagine what takes that long. We use JDBC and the bulk of this
time is on the prepare of the statment. Does anyone have any ideas on
what could be causing this?
By any chance is this the only client attached to the database? And is
the connection made at the begining of this insert? DB2 can shut
itself down if there are no connections made, and will wake up with the
first connection, which takes a bit of time, which could explain this,
I think. If that is so, there are ways to make DB2 stay awake, if you
need to.

-Chris

Sep 28 '06 #10

P: n/a
TC
Care to post the INSERT statement.

insert
into canpck
( cangrp, wh_id, wrkref, wrktyp, schbat,
srcloc, srcare, dstare, ship_line_id, ship_id,
client_id, ordnum, ordlin, ordsln, concod,
cmbcod, lblseq, pckqty, appqty, pcksts,
prtnum, prt_client_id, invsts, lodlvl, untcas,
untpak, ftpcod, visflg, splflg, locflg,
lodflg, subflg, dtlflg, prtflg, orgflg,
revflg, lotflg, qtyflg, adddte, candte,
can_usr_id, cancod, remqty, invsts_prg, frsflg,
min_shelf_hrs, pckdte, catch_qty_flg, pipflg, list_id,
list_seqnum)
values
( 'grp1', 'WMD1', 'TC_22', 'R', NULL,
'x', 'x', 'x', 'x', 'x',
'x', 'x', 'x', 'x', 'x',
'x', 'x', 0, 0, 'x',
'x', 'x', 'x', 'x', 0,
0, 'x', 0, 0, 0,
0, 0, 0, 0, 0,
0, 0, 0, ora.to_date('20060807120000', 'YYYYMMDDHH24MISS'),
ora.to_date('20060807120000', 'YYYYMMDDHH24MISS'),
'x', 'x', 0, 'x', 0,
0, ora.to_date('20060807120000', 'YYYYMMDDHH24MISS'), 0, 0, 'x',
0)
By any chance is this the only client attached to the database? And is
the connection made at the begining of this insert? DB2 can shut
itself down if there are no connections made, and will wake up with the
first connection, which takes a bit of time, which could explain this,
I think. If that is so, there are ways to make DB2 stay awake, if you
need to.
No, I tested this many times by forcing it to recompile by adding a
space character.

Sep 29 '06 #11

P: n/a
TC wrote:
>By any chance is this the only client attached to the database? And is
the connection made at the begining of this insert? DB2 can shut
itself down if there are no connections made, and will wake up with the
first connection, which takes a bit of time, which could explain this,
I think. If that is so, there are ways to make DB2 stay awake, if you
need to.

No, I tested this many times by forcing it to recompile by adding a
space character.
I guess the question was if you disconnect between the insert statements or
if you keep at least one connection alive and, thus, the database
activated.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Sep 29 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.