ER Shadow Columns, maxint issue?

Hi All, I have an Enterprise Replication environment using flexible grid so that when tables are created on the primary machine, they are also created on the backup. This means that the shadow columns are automatically generated for the tables, as follows: Column name Type Nulls ifx_erkey_1 integer yes ifx_erkey_2 integer yes ifx_erkey_3 smallint yes This is fine, but I have a lot of data being added to the database each day (about 26 million rows). The shadow column values for one of the rows is as follows: ifx_erkey_1 ifx_erkey_2 ifx_erkey_3 85 26519045 50 I am wondering where these values come from. My sqlhosts file look like this: myapp_server group - - i=50 myapptcp onsoctcp saturn 9005 g=myapp_server myappalias onipcshm saturn myapp_saturn myappbk_server group - - i=22 myapptcpbk onsoctcp spcgmttac01 9005 g=myappbk_server myappaliasbk onipcshm spcgmttac01 myapp_spcgmttac01 I am guessing that ifx_erkey_3 is the group number of the primary server. I have no idea where the 85 value for ifx_erkey_1 comes from. And, from what I can tell, ifx_erkey_2 is unique across all of the replicated tables (about a hundred of them), and it looks like it is incremented by one each time a new row is added. I also noticed that ifx_erkey_1 and ifx_erkey_3 are the same for all rows in all of the replicated tables. So, if the ifx_erkey_2 field is being incremented every time a new row is added, what will happen in about 80 days when this field reaches the maximum integer size? Will ifx_erkey_1 and/or ifx_erkey_3 change in order to allow ifx_erkey_2 to start again from 0? This is IBM Informix Dynamic Server Version 11.70.FC5GE. Thanks, Jeff Genega

Original Post: Hi All, I have an Enterprise Replication environment using flexible grid so that when tables are created on the primary machine, they are also created on the backup. This means that the shadow columns are automatically generated for the tables, as follows: Column name Type Nulls ifx_erkey_1 integer yes ifx_erkey_2 integer yes ifx_erkey_3 smallint yes This is fine, but I have a lot of data being added to the database each day (about 26 million rows). The shadow column values for one of the rows is as follows: ifx_erkey_1 ifx_erkey_2 ifx_erkey_3 85 26519045 50 I am wondering where these values come from. My sqlhosts file look like this: myapp_server group - - i=50 myapptcp onsoctcp saturn 9005 g=myapp_server myappalias onipcshm saturn myapp_saturn myappbk_server group - - i=22 myapptcpbk onsoctcp spcgmttac01 9005 g=myappbk_server myappaliasbk onipcshm spcgmttac01 myapp_spcgmttac01 I am guessing that ifx_erkey_3 is the group number of the primary server. I have no idea where the 85 value for ifx_erkey_1 comes from. And, from what I can tell, ifx_erkey_2 is unique across all of the replicated tables (about a hundred of them), and it looks like it is incremented by one each time a new row is added. I also noticed that ifx_erkey_1 and ifx_erkey_3 are the same for all rows in all of the replicated tables. So, if the ifx_erkey_2 field is being incremented every time a new row is added, what will happen in about 80 days when this field reaches the maximum integer size? Will ifx_erkey_1 and/or ifx_erkey_3 change in order to allow ifx_erkey_2 to start again from 0? This is IBM Informix Dynamic Server Version 11.70.FC5GE. Thanks, Jeff Genega Response: From a quick look in code, it does look like ifx_erkey_1 would be changed so that then ifx_erkey_2 could go back to 0. It might not happen exactly when ifx_erkey_2 hits max signed int but fairly close (so you could end up with some large negative values after it flips from positive to negative). Jacques Renaut IBM Informix Advanced Support APD Team Jacques Renaut IBM Informix Advanced Support APD Team

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 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of JACQUES RENAUT Sent: Wednesday, October 10, 2012 3:57 PM To: ids@iiug.org Subject: Re: ER Shadow Columns, maxint issue? [28469] Original Post: Hi All, I have an Enterprise Replication environment using flexible grid so that when tables are created on the primary machine, they are also created on the backup. This means that the shadow columns are automatically generated for the tables, as follows: Column name Type Nulls ifx_erkey_1 integer yes ifx_erkey_2 integer yes ifx_erkey_3 smallint yes This is fine, but I have a lot of data being added to the database each day (about 26 million rows). The shadow column values for one of the rows is as follows: ifx_erkey_1 ifx_erkey_2 ifx_erkey_3 85 26519045 50 I am wondering where these values come from. My sqlhosts file look like this: myapp_server group - - i=50 myapptcp onsoctcp saturn 9005 g=myapp_server myappalias onipcshm saturn myapp_saturn myappbk_server group - - i=22 myapptcpbk onsoctcp spcgmttac01 9005 g=myappbk_server myappaliasbk onipcshm spcgmttac01 myapp_spcgmttac01 I am guessing that ifx_erkey_3 is the group number of the primary server. I have no idea where the 85 value for ifx_erkey_1 comes from. And, from what I can tell, ifx_erkey_2 is unique across all of the replicated tables (about a hundred of them), and it looks like it is incremented by one each time a new row is added. I also noticed that ifx_erkey_1 and ifx_erkey_3 are the same for all rows in all of the replicated tables. So, if the ifx_erkey_2 field is being incremented every time a new row is added, what will happen in about 80 days when this field reaches the maximum integer size? Will ifx_erkey_1 and/or ifx_erkey_3 change in order to allow ifx_erkey_2 to start again from 0? This is IBM Informix Dynamic Server Version 11.70.FC5GE. Thanks, Jeff Genega Response: >From a quick look in code, it does look like ifx_erkey_1 would be changed so that then ifx_erkey_2 could go back to 0. It might not happen exactly when ifx_erkey_2 hits max signed int but fairly close (so you could end up with some large negative values after it flips from positive to negative). Jacques Renaut IBM Informix Advanced Support APD Team Jacques Renaut IBM Informix Advanced Support APD Team **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

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

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.

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

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.

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

Original Post: <clip> 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. <clip> Jacques Renaut IBM Informix Advanced Support APD Team Response: Jacques, That is exactly what I meant - your interpretation of my question is correct. FYI, in my particular application, the data eventually "ages out", so even though tens of millions or rows are added daily, they don't hang around all that long and get deleted. I don't know if ER reuses erkeys once they are deleted, but I didn't want to confuse the issue. Thanks for taking the time to look into this, I very much appreciate it. Jeff

That's why there are two integer columns. You'd have to create more th= an 2 billion new rows per second before there would be an issue. From: "JEFFREY GENEGA" <jeffrey.genega@spirent.com> To: ids@iiug.org, Date: 10/11/2012 08:24 AM Subject: Re: RE: ER Shadow Columns, maxint issue? [28477] Sent by: ids-bounces@iiug.org Original Post: <clip> However, I took the original question to be that multiple tables were b= eing 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. <clip> Jacques Renaut IBM Informix Advanced Support APD Team Response: Jacques, That is exactly what I meant - your interpretation of my question is correct. FYI, in my particular application, the data eventually "ages out", so e= ven though tens of millions or rows are added daily, they don't hang around= all that long and get deleted. I don't know if ER reuses erkeys once they a= re deleted, but I didn't want to confuse the issue. Thanks for taking the time to look into this, I very much appreciate it= . Jeff ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =