how to monitor alter columns on a large table
Challenger212 asked how to monitor progress of a 24-hour alter of two columns on a 126-million-row table. Andreas said there is no official monitoring and suggested locating BLDCL log records with onlog and checking onstat -x. The operation was stopped with onclean and recovery followed; Art Kagel said it could take as long. Rollback took 30 minutes, and export, alter and reload via external table was faster.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, I have launch an alter of two coloumns on a large tables 126 000 000 rows, now its been 24hours that this command is processing, so how can I monitor the processing in order to estimate the pourcentage of this task. thanks
Hummm, no official monitoring for this .... quite obviously a 'slow alter' =
(as opposed to an in-place alter) is occurring, that is the data is copied =
from old partition(s) (and old schema) to new partition(s) with new=20
schema, moreover the indexes will be re-built.
What you possibly could do is following:
- in the log current at the beginning of the ALTER, find a (or multiple)=20
BLDCL log record(s) with your table's name, using 'onlog' - this would be=20
the creation of the copy's target partition.
- you might also find a large/old transaction in 'onstat -x' and use it's =
begin=5Flogpos for locating the BLDCL record(s)
(maybe the #locks for this transaction could also serve as an=20
indication for how many rows already got copied)
- monitor sysmaster:sysptnhdr.nrows for the partnum(s) found in the BLDCL =
record(s)
My few cents on this,
Andreas
From: "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com>
To: ids@iiug.org
Date: 04.03.2017 19:11
Subject: how to monitor alter columns on a large table [38707]
Sent by: ids-bounces@iiug.org
Hi, I have launch an alter of two coloumns on a large tables 126 000 000=20
rows,=20
now its been 24hours that this command is processing, so how can I monitor =
the=20
processing in order to estimate the pourcentage of this task.=20
thanks=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Ok, I have stopped the operation, by onclean, and when I restart the instance , It's now on recovery, should the recovery take as long as the operation does?
Yes and possibly longer. Art On Mar 4, 2017 19:19, "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> wrote: > Ok, I have stopped the operation, by onclean, and when I restart the > instance > , It's now on recovery, should the recovery take as long as the operation > does? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11442ee419ed380549fcda04
Hi, the system have make the rollbakc on 30 minutes, now I have export with external table the datas and I've altered the columns, and nopw I'm reloading the datas and I will create the indexes after, Its more quick then altering the table.