update taking forever

Hello Gentlemen,

I have a table which is the highest level table in my DB
design...ie...all tables have foreign keys that if the chain is
followed, lead to this table.

This table had quite a few indexes (8) as well, however we have balanced
the issue of SELECT performance to INSERT performance and we have
internaly justified each indexe and removed some redundant ones.

Originaly my programmers would write UPDATE statments that updated all
the columns of the table, even though only 2 columns on that table could
ever have been affected by that code snipet. This update, because of
the load on that table, would take sometimes over a minute to complete,
if it didn't rollback because of a timeout.

My first step was to have them rewrite their code to only update the 2
columns that could ever change in that snipet of code. My logic behind
that was because some other columns, even though they didn't change,
would force the indexes to update themselves, causing a longer transaction.

None of the 2 columns left in the update are referenced by FKs, so I
assumed there is no need for an integrity check, all the way down the
pipeline.

However, its this assuption I am questioning. When DB2 UDB 7.2 (NO
fixpak....so 7.1 FP3) does an update.....in the background, is it doing
a "phantom" DELETE/INSERT which is forcing a check of the integrity of
all the FKs underneath it? Or is it doing just an UPDATE on the columns
specified in the command?

Any ideas?

Thank you in advance

Christos Kalantzis

re: update taking forever

"Christos Kalantzis" <christos2@intertrade.com> wrote in message
news:Evg_d.51210$KI2.6064@clgrps12...[color=blue]
> Hello Gentlemen,
>
> I have a table which is the highest level table in my DB
> design...ie...all tables have foreign keys that if the chain is
> followed, lead to this table.
>
> This table had quite a few indexes (8) as well, however we have balanced
> the issue of SELECT performance to INSERT performance and we have
> internaly justified each indexe and removed some redundant ones.
>
> Originaly my programmers would write UPDATE statments that updated all
> the columns of the table, even though only 2 columns on that table could
> ever have been affected by that code snipet. This update, because of
> the load on that table, would take sometimes over a minute to complete,
> if it didn't rollback because of a timeout.
>
> My first step was to have them rewrite their code to only update the 2
> columns that could ever change in that snipet of code. My logic behind
> that was because some other columns, even though they didn't change,
> would force the indexes to update themselves, causing a longer[/color]
transaction.[color=blue]
>
> None of the 2 columns left in the update are referenced by FKs, so I
> assumed there is no need for an integrity check, all the way down the
> pipeline.
>
> However, its this assuption I am questioning. When DB2 UDB 7.2 (NO
> fixpak....so 7.1 FP3) does an update.....in the background, is it doing
> a "phantom" DELETE/INSERT which is forcing a check of the integrity of
> all the FKs underneath it? Or is it doing just an UPDATE on the columns
> specified in the command?
>
> Any ideas?
>
> Thank you in advance
>
> Christos Kalantzis[/color]

DB2 is only updating the columns. However, if the columns are varchar, and
the column is now larger, the row may not fit back in the same space, or may
not fit on same page, so DB2 has to put the row somewhere else (without
getting into details).

Also , if an index column is updated, the DB2 does have to delete the old
index value and insert a new one. If there is no space on the correct index
page for the new value, then a page split may occur. There could be a lot of
extra steps involved.

You should definitely move to the latest fixpak. I don't know if your
problems are related to any APARs that have fixed with later fixpaks, but
that is always a possibility.

re: update taking forever

On Thu, 17 Mar 2005 14:29:56 GMT, Christos Kalantzis <christos2@intertrade.com>
wrote:

:Hello Gentlemen,
:
:I have a table which is the highest level table in my DB
:design...ie...all tables have foreign keys that if the chain is
:followed, lead to this table.
:
:This table had quite a few indexes (8) as well, however we have balanced
:the issue of SELECT performance to INSERT performance and we have
:internaly justified each indexe and removed some redundant ones.
:
:Originaly my programmers would write UPDATE statments that updated all
:the columns of the table, even though only 2 columns on that table could
:ever have been affected by that code snipet. This update, because of
:the load on that table, would take sometimes over a minute to complete,
:if it didn't rollback because of a timeout.
:
:My first step was to have them rewrite their code to only update the 2
:columns that could ever change in that snipet of code. My logic behind
:that was because some other columns, even though they didn't change,
:would force the indexes to update themselves, causing a longer transaction.
:
:None of the 2 columns left in the update are referenced by FKs, so I
:assumed there is no need for an integrity check, all the way down the
:pipeline.
:
:However, its this assuption I am questioning. When DB2 UDB 7.2 (NO
:fixpak....so 7.1 FP3) does an update.....in the background, is it doing
:a "phantom" DELETE/INSERT which is forcing a check of the integrity of
:all the FKs underneath it? Or is it doing just an UPDATE on the columns
:specified in the command?
:
:Any ideas?
:
:Thank you in advance
:
:Christos Kalantzis

You might want to post this q on the db2 email user's group list. Lots of
experience and ideas there - see www.herethey.com or
http://www2.herethey.com:8888/mailman/listinfo/db2eug to subscribe.