IBM Data Management Community Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems. Join / Log in
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.
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.
Thanks, Adreas, but if I understand what you mean by "I'd assume this wasn't any different back then" it actually was different back then. Table data started at rowid = 1, and (I'm told) using rowid was a pretty common way to start sorting through data. Obviously that's not the case today and Art's response below clears up when this changed and why this isn't really possible today. Thanks for the help though!
------------------------------Andreas LegnerOriginal Message:Sent: Fri September 22, 2023 08:37 AMFrom: James HughesSubject: ROWID question
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.
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!
Making my own rowid sounds like a relatively quick and easy initial fix. Thanks!