Re: rowid

Posted in 2003

John_Thomas@ao.uscourts.gov — Tue, 20 May 2003 09:24:38 -0400 — source: Usenet: comp.databases.informix

This is a multipart message in MIME format. --=_alternative 0049C6E985256D2C_= Content-Type: text/plain; charset="us-ascii" If the person who asked the original question (the thread is kinda confusing) DID use RowIDs as keys, there at least is a fairly easy way to simulate this in Informix Online software. 1. Alter each table that uses a RowId as a key, such that the new table has a SERIAL column (which will function as the substitute (but non-changeable!) primary key. 2. Unload all data from the "old tables" including the RowIDs. 3. Upload the data such that the RowID columns are placed into the newly created SERIAL columns in the Online system. We have been using a schema designed this way for about 5 years now and it's caused very little difficulties (except that the users don't like the meaningless serial key, so we need to cross-reference the serial key to a user-meaningful key). But the main thing is: using this approach (i.e., build your own RowID), it doesn't matter how many times Informix changes its own internal RowID, your synthetic RowID remains the same ! Hope this helps --John 202-502-2599 (or x 2599) rkusenet <rkusenet@sympatico.ca> Sent by: owner-informix-list@iiug.org 05/20/2003 09:12 AM To: Michael Mueller <michael.mueller01@kay-mueller.de>, wolphie@hotmail.com cc: informix-list@iiug.org Subject: Re: rowid Michael, You are right about it. I think it is extremely stupid programming to remember rowid as a static data and I can be almost sure that this is not what Joesph was asking. I think what he meant was using rowid to process data in cursors (instead of primary keys). Ravi ----- Original Message ----- From: "Michael Mueller" <michael.mueller01@kay-mueller.de> To: <wolphie@hotmail.com> Cc: "rkusenet" <rkusenet@sympatico.ca>; <informix-list@iiug.org> Sent: Tuesday, May 20, 2003 09:06 Subject: Re: rowid > Joseph, > > I am not sure that this is relevant. But there has been another change > in this area. In the olden days a row was guaranteed to keep it's rowid > for it's life time (until it was deleted). This is not true anymore. All > versions that support the inplace alter table feature may internally > delete and reinsert rows for tables that have been altered in the past > using "alter table". This happens during updates of rows that happen to > be on the same disk page. And it results in a change of rowid because > the rowid is built from <logical page> + <slot number>. > > This deleting and reinserting is done when shorter rows were altered > into longer ones. When the page is finally converted to the new format > (it's always done for the whole page) there may not be enough space on > it for all the rows. > > For programs that remember rowids this can be quite a surprise. > > Michael > > rkusenet wrote: > > "Smitty" <wolphie@hotmail.com> wrote in message news:4f39af7f.0305190604.414c07a@posting.google.com... > > > >>We have 1800+ 4GL programs written using Informix SE 4.0 and have > >>needed to upgrade for a LONG time. We have a 5-user version of > >>Informix IDS 7.31.UD5-1 to test with. Someone told us that the newer > >>versions of Informix don't support "rowid", which is included in a lot > >>of our code thanks to the FourGen CASE tools. We are having trouble > >>getting a definitive answer to this question, does anyone know when or > >>if Informix products stopped using rowid? > >>Thanks! > > > > > > rowid will not work if your table is fragmented, unless you create the > > table WITH ROWID clause. Now given that you are currently using SE 4.0, > > if you move your schema as it is, then there is no question of fragmentation. > > Hence your old programs can safely use rowids, as long as you don't change > > the schema and start fragmenting. > > > > Ravi > > > > > > > > > -- > > === Michael Mueller ================== > Tel. + 49 8171 63600 > Fax. + 49 8171 63615 > Web: http://www.mm.kay-mueller.de > http://www.planets.kay-mueller.de > ====================================== > --=_alternative 0049C6E985256D2C_= Content-Type: text/html; charset="us-ascii" If the person who asked the original question (the thread is kinda confusing) DID use RowIDs as keys, there at least is a fairly easy way to simulate this in Informix Online software. 1.  Alter each table that uses a RowId as a key, such that the new table has a SERIAL column (which will function as the      substitute (but non-changeable!) primary key. 2. Unload all data from the "old tables" including the RowIDs. 3. Upload the data such that the RowID columns are placed into the newly created SERIAL columns in the Online system. We have been using a schema designed this way for about 5 years now and it's caused very little difficulties (except that the users don't like the meaningless serial key, so we need to cross-reference the serial key to a user-meaningful key). But the main thing is: using this approach (i.e., build your own RowID), it doesn't matter how many times Informix changes its own internal RowID, your synthetic RowID remains the same ! Hope this helps --John 202-502-2599 (or x 2599) <table width=100%> <tr valign=top> <td> <td>rkusenet <rkusenet@sympatico.ca> Sent by: owner-informix-list@iiug.org 05/20/2003 09:12 AM <td>                To:        Michael Mueller <michael.mueller01@kay-mueller.de>, wolphie@hotmail.com         cc:        informix-list@iiug.org         Subject:        Re: rowid</table> Michael, You are right about it. I think it is extremely stupid programming to remember rowid as a static data and I can be almost sure that this is not what Joesph was asking. I think what he meant was using rowid to process data in cursors (instead of primary keys). Ravi @@NL