Slow prepare times for inserts

[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?

[Serge Rielau]

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/

[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.

[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.

[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.

[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.

[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.

[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.

[Serge Rielau]

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/

[ChrisC]

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

[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.

[Knut Stolze]

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