Informix

I am working part time at a small consulting firm.  They had a very old version of Informix with a 4GL interface dating back probably 20 years that hadn't been updated.  There's a lot of code and almost all the searches are configured using ROWID.  I've set up a VM using a modern version of Centos, upgraded to a current version of Informix, moved over the data, and recompiled the code.  All seems well except that the new tables all start at rowid 257 instead of 1.  I know using rowid isn't a good way to do things, and I'll start going through the code fixing that, but there's a lot of code and I'm only one part time guy.  Is there a way to change the tables to start at rowid 1 in the meantime while I start unraveling all this old code?  Tables are unfragemented.  Any help greatly appreciated.

------------------------------
James Hughes
------------------------------

You didn't mention the old version (was it even Informix Dynamic Server?), but I'd assume this wasn't any different back then:  the built-in rowid typically isn't really a counter, but rather encodes the row's position, as its slot number s on a given page p and, in hexadecimal representation, as 0xppppppss.  So the very first row in a table would be 0x00000101 which is 257.   As you can see up to 0xff = 255 rows can be logically accommodated on a given page, so up to rowid 0x1ff = 511.  If rows are large relative to page size, fewer rows might actually fit on a page before it needs to go to a different page, so don't be surprised by rowid "gaps".  And there's also no guarantee subsequent inserts would be placed adjacent to each other, so new rowids can 'jump' back and forth. Also:  if rows get deleted, their place can be taken by new rows which would then "inherit" an old row's rowid.

As I said, this is the same since ... more than 20 years.

And then there's still that other variant of rowid, with so called "fragmented tables":  these would indeed start at 1, increment steadily and be totally independent from location - so different beast, for applications like this one, depending on some non-data-based row identifier, when using table fragmentation - likely not their case.

HTH,

 Andreas

------------------------------
Andreas Legner

Original Message:
Sent: Fri September 22, 2023 08:37 AM
From: James Hughes
Subject: ROWID question

I am working part time at a small consulting firm.  They had a very old version of Informix with a 4GL interface dating back probably 20 years that hadn't been updated.  There's a lot of code and almost all the searches are configured using ROWID.  I've set up a VM using a modern version of Centos, upgraded to a current version of Informix, moved over the data, and recompiled the code.  All seems well except that the new tables all start at rowid 257 instead of 1.  I know using rowid isn't a good way to do things, and I'll start going through the code fixing that, but there's a lot of code and I'm only one part time guy.  Is there a way to change the tables to start at rowid 1 in the meantime while I start unraveling all this old code?  Tables are unfragemented.  Any help greatly appreciated.

------------------------------
James Hughes
------------------------------

I am working part time at a small consulting firm.  They had a very old version of Informix with a 4GL interface dating back probably 20 years that hadn't been updated.  There's a lot of code and almost all the searches are configured using ROWID.  I've set up a VM using a modern version of Centos, upgraded to a current version of Informix, moved over the data, and recompiled the code.  All seems well except that the new tables all start at rowid 257 instead of 1.  I know using rowid isn't a good way to do things, and I'll start going through the code fixing that, but there's a lot of code and I'm only one part time guy.  Is there a way to change the tables to start at rowid 1 in the meantime while I start unraveling all this old code?  Tables are unfragemented.  Any help greatly appreciated.

Hi James. Welcome to the community. Unfortunately in the current Informix engine, the ROWID is not an ordinal row number but the row's actual address within the table. It consists of the ordinal page number in the first 24 bits and the row's slot number on the page in the last byte. So, the first row in any table is 0x00000101 or 257. Vis:

> select first 5 rowid, hex(rowid) from extents;


     rowid (expression)  

       257 0x00000101
       258 0x00000102
       259 0x00000103
       260 0x00000104
       261 0x00000105

So, rows stored on the table's second page would have rowids starting at 0x00000201 or 513. Since each page by default holds 2020 bytes of data, there will normally be fewer than 255 rows on a page, so there will be numbering gaps between the last rowid on one page and the first rowid on the next page in the table.

So there is no way to change that. It sounds like the original system was written for the original Informix engine, which for a time was known as the Informix Standard Engine, or SE, which would have been Informix versions up to 3.10. In SE, the ROWID pseudo-column was indeed an ordinal starting at 1. Starting with the Informix Turbo Engine v4.00 and Informix OnLine v5.01 rowids have been handled like this.

I am working part time at a small consulting firm.  They had a very old version of Informix with a 4GL interface dating back probably 20 years that hadn't been updated.  There's a lot of code and almost all the searches are configured using ROWID.  I've set up a VM using a modern version of Centos, upgraded to a current version of Informix, moved over the data, and recompiled the code.  All seems well except that the new tables all start at rowid 257 instead of 1.  I know using rowid isn't a good way to do things, and I'll start going through the code fixing that, but there's a lot of code and I'm only one part time guy.  Is there a way to change the tables to start at rowid 1 in the meantime while I start unraveling all this old code?  Tables are unfragemented.  Any help greatly appreciated.

Yes, it was using SE.  Okay, that's kind of what I was afraid of so I guess the only way to get it working is to slog through all the code.  Thanks for the help!

Hi James. Welcome to the community. Unfortunately in the current Informix engine, the ROWID is not an ordinal row number but the row's actual address within the table. It consists of the ordinal page number in the first 24 bits and the row's slot number on the page in the last byte. So, the first row in any table is 0x00000101 or 257. Vis:

> select first 5 rowid, hex(rowid) from extents;


     rowid (expression)  

       257 0x00000101
       258 0x00000102
       259 0x00000103
       260 0x00000104
       261 0x00000105

So, rows stored on the table's second page would have rowids starting at 0x00000201 or 513. Since each page by default holds 2020 bytes of data, there will normally be fewer than 255 rows on a page, so there will be numbering gaps between the last rowid on one page and the first rowid on the next page in the table.

So there is no way to change that. It sounds like the original system was written for the original Informix engine, which for a time was known as the Informix Standard Engine, or SE, which would have been Informix versions up to 3.10. In SE, the ROWID pseudo-column was indeed an ordinal starting at 1. Starting with the Informix Turbo Engine v4.00 and Informix OnLine v5.01 rowids have been handled like this.

I am working part time at a small consulting firm.  They had a very old version of Informix with a 4GL interface dating back probably 20 years that hadn't been updated.  There's a lot of code and almost all the searches are configured using ROWID.  I've set up a VM using a modern version of Centos, upgraded to a current version of Informix, moved over the data, and recompiled the code.  All seems well except that the new tables all start at rowid 257 instead of 1.  I know using rowid isn't a good way to do things, and I'll start going through the code fixing that, but there's a lot of code and I'm only one part time guy.  Is there a way to change the tables to start at rowid 1 in the meantime while I start unraveling all this old code?  Tables are unfragemented.  Any help greatly appreciated.

Yes, it was using SE.  Okay, that's kind of what I was afraid of so I guess the only way to get it working is to slog through all the code.  Thanks for the help!

Hi James. Welcome to the community. Unfortunately in the current Informix engine, the ROWID is not an ordinal row number but the row's actual address within the table. It consists of the ordinal page number in the first 24 bits and the row's slot number on the page in the last byte. So, the first row in any table is 0x00000101 or 257. Vis:

> select first 5 rowid, hex(rowid) from extents;


     rowid (expression)  

       257 0x00000101
       258 0x00000102
       259 0x00000103
       260 0x00000104
       261 0x00000105

So, rows stored on the table's second page would have rowids starting at 0x00000201 or 513. Since each page by default holds 2020 bytes of data, there will normally be fewer than 255 rows on a page, so there will be numbering gaps between the last rowid on one page and the first rowid on the next page in the table.

So there is no way to change that. It sounds like the original system was written for the original Informix engine, which for a time was known as the Informix Standard Engine, or SE, which would have been Informix versions up to 3.10. In SE, the ROWID pseudo-column was indeed an ordinal starting at 1. Starting with the Informix Turbo Engine v4.00 and Informix OnLine v5.01 rowids have been handled like this.

I am working part time at a small consulting firm.  They had a very old version of Informix with a 4GL interface dating back probably 20 years that hadn't been updated.  There's a lot of code and almost all the searches are configured using ROWID.  I've set up a VM using a modern version of Centos, upgraded to a current version of Informix, moved over the data, and recompiled the code.  All seems well except that the new tables all start at rowid 257 instead of 1.  I know using rowid isn't a good way to do things, and I'll start going through the code fixing that, but there's a lot of code and I'm only one part time guy.  Is there a way to change the tables to start at rowid 1 in the meantime while I start unraveling all this old code?  Tables are unfragemented.  Any help greatly appreciated.