IDS Forum

Re: ER Shadow Columns, maxint issue?

Posted By: Art Kagel
Date: Wednesday, 10 October 2012, at 10:34 p.m.

In Response To: Re: ER Shadow Columns, maxint issue? (Paul Watson)

Can't alter the index, but you CAN alter the tabke and let the index get
dragged along.

Art
On Oct 10, 2012 10:30 PM, "Paul Watson" <paul@oninit.com> wrote:

> You can not use the alter because the index has a leading space.
>
> Paul Watson
> Oninit www.oninit.com
> +1 913 387 7529
>
> On Oct 10, 2012, at 20:54, "Art Kagel" <art.kagel@gmail.com> wrote:
>
> > Paul,
> >
> > If the index hits 2^24 pages before the table does, they can just
> fragment
> > the table using the ALTER FRAGMENT...INIT... syntax. That will drag the
> > index to become fragmented with the same expression as the table.
> >
> > You are correct, because it is a hidden index that we cannot "unhide"
> there
> > is no way to just fragment the index itself.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Wed, Oct 10, 2012 at 6:35 PM, Paul Watson <paul@oninit.com> wrote:
> >
> >> You run into the problem regardless of whether it is a PK or not - you
> hit
> >> the
> >> 16m limit then game over, the index has a leading space in the name
> >> therefore
> >> can not be fragmented, it can not dropped and recreated etc.
> >>
> >> Cheers
> >> Paul
> >>
> >> Paul Watson
> >> Oninit www.oninit.com
> >> +1 913 387 7529
> >>
> >> On Oct 10, 2012, at 16:47, "JACQUES RENAUT" <jrenaut@us.ibm.com> wrote:
> >>
> >>> Original Post:
> >>> Of course when Informix automatically add the ER key index and you
> exceed
> >>> the 16M row limit on an index you no longer have a system that
> >> replicates.
> >>>
> >>> AFAIK, when there is no PK on the table the erkeys are used as the PK
> so
> >> are
> >>> you allowed nulls ?
> >>>
> >>> Cheers
> >>> Paul
> >>>
> >>> Response:
> >>>
> >>> Well, I assume you mean the 16 million page limit for the max size of a
> >>> partition, which I guess you could run into if you tried to use stock
> >> erkey
> >> as
> >>> your primary key on a already large fragmented table.
> >>>
> >>> However, I took the original question to be that multiple tables were
> >> being
> >>> replicated and this ifx_erkey_2 value was being used across all of
> them,
> >> so
> >>> there was concern that after 2 billion rows were added to all the
> >> replicated
> >>> tables, there would be problems. Perhaps I read the original post
> >> incorrectly.
> >>>
> >>> As for your question, the column definitions for the ifx_erkey_#
> columns
> >> show
> >>> that nulls are allowed, but the composite index across all 3 columns is
> >>> defined as a unique index, and the server plugs in default values (and
> >> you
> >> are
> >>> not allowed to manually update these things normally) so the server
> >> doesn't
> >>> put a null in for any of the values but I'm not sure if there's
> anything
> >> else
> >>> in the code that specifically prohibits a null (other then it's not an
> >>> updatable column by normal methods and the server doesn't put nulls in
> >> them).
> >>>
> >>> Jacques Renaut
> >>> IBM Informix Advanced Support
> >>> APD Team
> >
>
> *******************************************************************************
> >>> Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> *******************************************************************************
> >> Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> > --14dae9340ef16eb5ab04cbbed86a
> >
> >
> >
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--14dae93406d754b2eb04cbbf674d

Messages In This Thread

• ER Shadow Columns, maxint issue?
  JEFFREY GENEGA -- Tuesday, 9 October 2012, at 8:47 a.m.
  • Re: ER Shadow Columns, maxint issue?
    JACQUES RENAUT -- Wednesday, 10 October 2012, at 4:57 p.m.
    • RE: ER Shadow Columns, maxint issue?
      Paul Watson -- Wednesday, 10 October 2012, at 5:28 p.m.
      • Re: RE: ER Shadow Columns, maxint issue?
        JACQUES RENAUT -- Wednesday, 10 October 2012, at 5:47 p.m.
        • Re: ER Shadow Columns, maxint issue?
          Paul Watson -- Wednesday, 10 October 2012, at 6:34 p.m.
          • Re: ER Shadow Columns, maxint issue?
            Art Kagel -- Wednesday, 10 October 2012, at 9:54 p.m.
            • Re: ER Shadow Columns, maxint issue?
              Paul Watson -- Wednesday, 10 October 2012, at 10:29 p.m.
              • Re: ER Shadow Columns, maxint issue?
                Art Kagel -- Wednesday, 10 October 2012, at 10:34 p.m.
        • Re: RE: ER Shadow Columns, maxint issue?
          JEFFREY GENEGA -- Thursday, 11 October 2012, at 9:21 a.m.
          • Re: RE: ER Shadow Columns, maxint issue?
            Madison Pruet -- Thursday, 11 October 2012, at 9:32 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.