RE: ALTER TABLE performance issues

I tend to agree with you. However, we have another app that consumes some of our data for an Oracle based function. It (PowerExchange) doesn't deal well with rebuilding the files. It's something to do with the way they register files so it's forcing us to upgrade them in place.

Thanks for the support on the idea.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Tuesday, November 16, 2010 7:44 AM
To: Midrange Systems Technical Discussion
Subject: RE: ALTER TABLE performance issues

I'm really not sure that I like the idea of using ALTER TABLE to manage
database change. IMO, the problem with ALTER TABLE is that, if you had
to re-create the file object, you would need to create the object from
its original source, then apply ALL alter table statements. From a
change management standpoint, it's MUCH BETTER to always modify the
object build source, create the new file, then migrate any data as
applicable. This is a consistent process, where your source and objects
are always maintained together.

Just one opinion...
-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Kruse, Kat
Sent: Monday, November 15, 2010 4:45 PM
To: 'Midrange Systems Technical Discussion'
Subject: ALTER TABLE performance issues

Our developers have finally started using ALTER TABLE instead of
rename/copy method of changing files. One of the things we noticed is
that there are times that there are significant differences in the
performance of the change.

We've already suggested to them that instead of multiple passes with
only one change at a time, they should be doing all the changes in a
single pass. We've also noted where the differences can be accounted
for by files with many/few columns and by files with many/few rows.

After taking the above into consideration we're still finding that the
ALTER TABLE give very inconsistent results. For example, for a given
file which had 0 records, in library A it took 5 seconds to finish while
in library B (also 0 records) it to 22 seconds to finish.

FWIW, we're at V6R1 on Power7.

So, has anyone got suggestions at what we can look at or do to improve
our performance?

Thanks,
Kat Kruse
Qualcomm, Inc.
Systems Support Engineer