IDS Forum

Re: ER Shadow Columns, maxint issue?

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

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

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

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.