Kat -
If you first DROP any indexes and either delete the LF's or remove the
members from the logical files, I would estimate a considerable performance
increase, depending on the number of indexes and logical files, resulting in
much less time required to perform the ALTER TABLE.
I don't believe that views contribute any overhead to the file rebuild time
(unless one is open), since a view is simply an SQL statement stored in a
system object which only gets executed when the view is opened by a program
or an SQL statement.
You'll obviously need to recreate the indexes and either recreate the LF's
or add back the logical file members after the physical table is rebuilt.
I base my above statements on a situation where it was taking 10-12 hours to
copy a couple of million records into a physical file which had around 40
logical files built over it. It was very slow because of the concurrent
maintenance being performed on the logical files as the CPYF was running.
By performing the following steps:
1) Removing all of the logical file members,
2) then using the CPYF to load the file, and
3) then submitting jobs to add back the logical file members (ADDLFM) once
the CPYF completed,
It took less than 2 hours to run, including the recreation of the logical
file members.
This process was encapsulated into a command that I call FASTCPYF.
- sjl
Kat wrote:
As I mentioned in another response, we did rule out locks and system load.
The issue of indices is intriguing as these files do have a lot of
logicals/views.
Do you know of way to improve this timing? Would it be faster to delete
the
logicals/views, do the ALTER TABLE, then rebuild them?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Lennon_s_j@xxxxxxxxxxx
Sent: Monday, November 15, 2010 4:23 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: ALTER TABLE performance issues
Try getting an exclusive lock on the object first, like this:
ALCOBJ OBJ((MYLIB/MFILE *FILE *EXCL)) CONFLICT(*RQSRLS)
The CONFLICT parm intent is to free up any system locks.
Then see how long the change takes.
And I suspect that if you alter field which is part of an index than the
index will have to be rebuilt and that will add time. Presumably for
your test the table in both libraries had all the same indexes, views
and constraints.
Sam
On 11/15/2010 5:44 PM, Kruse, Kat wrote:
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
midrange.com
