Resolving In Place Alters
11.50xC4 Is there a better way to resolve in place alters than blindly updating every row in a table with pending in place alters? I would like to identify pages with pending in place alters via sysmaster and perform a dummy update for just 1 row that lives on that page if possible. I know how to identify the tables with pending in place alters, just don't know how to find the right row to update. Thanks in advance, Andrew
Andrew: This is probably not worth the effort. How page versions are recorded in the server is not documented. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Nov 7, 2014 at 12:41 PM, Andrew Ford <andrew@informix-dba.com> wrote: > 11.50xC4 > > Is there a better way to resolve in place alters than blindly updating > every > row in a table with pending in place alters? > > I would like to identify pages with pending in place alters via sysmaster > and perform a dummy update for just 1 row that lives on that page if > possible. > > I know how to identify the tables with pending in place alters, just don't > know how to find the right row to update. > > Thanks in advance, > > Andrew > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0158b61c78ce63050748dab1
I spent a SUBSTANTIAL amount of time and effort looking into this, and eventually gave up. It is not worth it, the cost involved in identifying outstanding alters is greater than the cost of updating every row. -- Regards Spokey > On 7 Nov 2014, at 17:41, Andrew Ford <andrew@informix-dba.com> wrote: > > 11.50xC4 > > Is there a better way to resolve in place alters than blindly updating every > row in a table with pending in place alters? > > I would like to identify pages with pending in place alters via sysmaster > and perform a dummy update for just 1 row that lives on that page if > possible. > > I know how to identify the tables with pending in place alters, just don't > know how to find the right row to update. > > Thanks in advance, > > Andrew > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Art and Spokey, Thanks for confirming I had not lost my mind or was just too dense to see an obvious answer right in front of me. The best I could come up with was start updating rows one by one and stop when sum(pta_totpgs) in sysactptnhdr for the partnum in question reaches 0. Back to updating a billion rows 1 at a time in small transactions with a sleep to throttle the updates so I don't kill production... It might be finished in time for the conference in April. Ha! I bet you didn't think I could sneak in a plug for the conference in here, did you? Call for Presentations is open: http://www.iiug2015.org/speakers/ Come speak and be a living legend, all we need right now is 1) Your willingness to present and 2) A one sentence abstract about what you want to present on. There's a conference pass in it for you... Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Spokey Wheeler Sent: Friday, November 07, 2014 1:50 PM To: ids@iiug.org Subject: Re: Resolving In Place Alters [34126] I spent a SUBSTANTIAL amount of time and effort looking into this, and eventually gave up. It is not worth it, the cost involved in identifying outstanding alters is greater than the cost of updating every row. -- Regards Spokey > On 7 Nov 2014, at 17:41, Andrew Ford <andrew@informix-dba.com> wrote: > > 11.50xC4 > > Is there a better way to resolve in place alters than blindly updating > every row in a table with pending in place alters? > > I would like to identify pages with pending in place alters via > sysmaster and perform a dummy update for just 1 row that lives on that > page if possible. > > I know how to identify the tables with pending in place alters, just > don't know how to find the right row to update. > > Thanks in advance, > > Andrew > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
I wanted to make sure people are aware of new functionality in
version 12.10 which simplifies the updating of outstanding
in-place alters. There is a new command update_ipa which
can be provided to the task/admin command in sysadmin to
remove outstanding in-place alters. In addition, to ensuring
you do not run into long transactions, this command can run
in parallel.
Example:
EXECUTE FUNCTION task('table update_ipa parallel, 'my_table')
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 11/07/2014 12:03:26 PM:
> From: "Andrew Ford" <andrew@informix-dba.com>
> To: ids@iiug.org
> Date: 11/07/2014 12:03 PM
> Subject: RE: Resolving In Place Alters [34127]
> Sent by: ids-bounces@iiug.org
>
> Art and Spokey,
>
> Thanks for confirming I had not lost my mind or was just too dense to see
an
> obvious answer right in front of me.
>
> The best I could come up with was start updating rows one by one and stop
> when sum(pta_totpgs) in sysactptnhdr for the partnum in question reaches
0.
>
> Back to updating a billion rows 1 at a time in small transactions with a
> sleep to throttle the updates so I don't kill production...
>
> It might be finished in time for the conference in April.
>
> Ha! I bet you didn't think I could sneak in a plug for the conference in
> here, did you?
>
> Call for Presentations is open: http://www.iiug2015.org/speakers/
>
> Come speak and be a living legend, all we need right now is 1) Your
> willingness to present and 2) A one sentence abstract about what you want
to
> present on.
>
> There's a conference pass in it for you...
>
> Andrew
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Spokey
> Wheeler
> Sent: Friday, November 07, 2014 1:50 PM
> To: ids@iiug.org
> Subject: Re: Resolving In Place Alters [34126]
>
> I spent a SUBSTANTIAL amount of time and effort looking into this, and
> eventually gave up. It is not worth it, the cost involved in identifying
> outstanding alters is greater than the cost of updating every row.
>
> --
> Regards
> Spokey
>
> > On 7 Nov 2014, at 17:41, Andrew Ford <andrew@informix-dba.com> wrote:
> >
> > 11.50xC4
> >
> > Is there a better way to resolve in place alters than blindly updating
> > every row in a table with pending in place alters?
> >
> > I would like to identify pages with pending in place alters via
> > sysmaster and perform a dummy update for just 1 row that lives on that
> > page if possible.
> >
> > I know how to identify the tables with pending in place alters, just
> > don't know how to find the right row to update.
> >
> > Thanks in advance,
> >
> > Andrew
> >
> >
> >
>
****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>