RE: Whatcha' wanta have?????

This limitation comes from the fact that Informix is using 32-bit rowid's, where 24 bits are dedicated to the page number (this is where 16 mln page limitation comes from)and 8 bytes to the slot number (that is, max 256 slots per page) Interesting, that the same limitation exists in DB2. They also use 32-bit ROWID's. And, unlike Informix, this limitation can't be overcomed with fragmentation: there is no fragmentation in DB2. The only workaround is to increase the page size for the particular table, so that You can really fit 4 billion rows into it. Oracle is using 6-byte ROWID's (AFAIK). They do not have this limitation... What about MS SQL? Anybody knows? ------------------------------------------ Alexey Sonkin > -----Original Message----- > From: Neil Truby [mailto:neil.truby@ardenta.com] > Sent: Friday, July 02, 2004 6:25 PM > To: informix-list@iiug.org > Subject: Re: Whatcha' wanta have????? > > "Dave Griffen" <dgriffen@nospam.finishline.com> wrote in message > news:cc4irj$t9s$1@news.onecall.net... > > Back in February Madison asked for input as to what we want to see in > the > > IDS 9.6 release. Although this is almost certainly too late for IDS 9.6 > > consideration, I don't know of a better forum to request modifications. > > I've recently stumbled on something which I'd like to see changed. > > Specifically, I think the following limitations need to be dramatically > > increased. > > > > Table-Level Parameters (based on 2K page size) Maximum Capacity > per > > Table > > Data rows per fragment 4,277,659,295 > > Data pages per fragment 16,775,134 > > Data bytes per fragment (excludes Smart Large Objects (BLOB, CLOB) > and > > Simple Large Objects (BYTE, TEXT) created in Blobspaces) 33,818,671,136 > > > > Release notes on the IBM-Informix website show that these numbers have > > remained unchanged from OnLine 5.02 up through and including IDS 9.40. > > > > I can only think that these parameters were an oversight with IDS 9.4. > > After all, how practical can a 4TB chunk ever be if it can only hold > 32GB > of > > any single table? > > I'm absolutely with you on this, Dave. Although I haven't *recently* > discovered this - it bit me in the arse a few years ago - I still have to > spend time on larger bases using fragmentation for a purpose it simply was > not intended - splitting tables across dbspaces to get around the 32G > tablespace limit. With all the raised limits of 9.40, I was astonished to > find this hadn;t been addressed. sending to informix-list

I believe that DB2 and Informix are both in the process of addressing this limit. "Alexey Sonkin" <alexeis@grandvirtual.com> wrote in message news:cchebq$l63$1@news.xmission.com... > > This limitation comes from the fact that Informix > is using 32-bit rowid's, where 24 bits are dedicated > to the page number (this is where 16 mln page limitation > comes from)and 8 bytes to the slot number > (that is, max 256 slots per page) > > Interesting, that the same limitation exists in DB2. > They also use 32-bit ROWID's. And, unlike Informix, > this limitation can't be overcomed with fragmentation: > there is no fragmentation in DB2. The only workaround > is to increase the page size for the particular table, > so that You can really fit 4 billion rows into it. > > Oracle is using 6-byte ROWID's (AFAIK). > They do not have this limitation... > > What about MS SQL? Anybody knows? > > ------------------------------------------ > Alexey Sonkin > > > > -----Original Message----- > > From: Neil Truby [mailto:neil.truby@ardenta.com] > > Sent: Friday, July 02, 2004 6:25 PM > > To: informix-list@iiug.org > > Subject: Re: Whatcha' wanta have????? > > > > "Dave Griffen" <dgriffen@nospam.finishline.com> wrote in message > > news:cc4irj$t9s$1@news.onecall.net... > > > Back in February Madison asked for input as to what we want to see in > > the > > > IDS 9.6 release. Although this is almost certainly too late for IDS 9.6 > > > consideration, I don't know of a better forum to request modifications. > > > I've recently stumbled on something which I'd like to see changed. > > > Specifically, I think the following limitations need to be dramatically > > > increased. > > > > > > Table-Level Parameters (based on 2K page size) Maximum Capacity > > per > > > Table > > > Data rows per fragment 4,277,659,295 > > > Data pages per fragment 16,775,134 > > > Data bytes per fragment (excludes Smart Large Objects (BLOB, CLOB) > > and > > > Simple Large Objects (BYTE, TEXT) created in Blobspaces) 33,818,671,136 > > > > > > Release notes on the IBM-Informix website show that these numbers have > > > remained unchanged from OnLine 5.02 up through and including IDS 9.40. > > > > > > I can only think that these parameters were an oversight with IDS 9.4. > > > After all, how practical can a 4TB chunk ever be if it can only hold > > 32GB > > of > > > any single table? > > > > I'm absolutely with you on this, Dave. Although I haven't *recently* > > discovered this - it bit me in the arse a few years ago - I still have to > > spend time on larger bases using fragmentation for a purpose it simply was > > not intended - splitting tables across dbspaces to get around the 32G > > tablespace limit. With all the raised limits of 9.40, I was astonished to > > find this hadn;t been addressed. > > sending to informix-list

Alexey Sonkin wrote: > This limitation comes from the fact that Informix > is using 32-bit rowid's, where 24 bits are dedicated > to the page number (this is where 16 mln page limitation > comes from)and 8 bytes to the slot number > (that is, max 256 slots per page) > > Interesting, that the same limitation exists in DB2. > They also use 32-bit ROWID's. And, unlike Informix, > this limitation can't be overcomed with fragmentation: > there is no fragmentation in DB2. The only workaround > is to increase the page size for the particular table, > so that You can really fit 4 billion rows into it. > > Oracle is using 6-byte ROWID's (AFAIK). > They do not have this limitation... > > What about MS SQL? Anybody knows? > > ------------------------------------------ > Alexey Sonkin > > > A small correction on DB2 'fragmentation': DB2 does partitioning, which is functionally equivalent to "fragmentation" in Informix. The interesting thing is that you can get partitioned clustering out-of-the-box with DB2, you can't get that with Informix, unless you buy XPS, and god only knows if XPS is even being used anymore. Of course I'm talking about DB2 EEE, not workgroup. Partitioning in DB2 is also somewhat advanced in that it is not just disk partitioning, but also instance partitioning, something close to what XPS can do with virtual co-servers on one platform. The difference is that the partitioning on DB2 is still within one instance, it's not several instances as in XPS if you set up several coservers on one platform. XPS is somewhat primitive compared to DB2 in that regard ( or maybe 'simple' is a better word ). Pity XPS never made it into the mainstream, a classic case of sitting on your assets. With DB2 you get today what "arrowhead" was supposed to be, you don't have to wait for it. You also get table-level memory management with DB2, with Informix you do not, you get global instance-wide memory and a handful of other settings and that's it. Regarding MS-SQL, it only runs on Windows, so whatever platform it runs on, typically Intel x86, it will be limited by whatever limitation comes with 32-bit x86. Pages are typically 4K in MS-SQL, but can be changed--and I don't recall off the top of my head what circumstances or limitations there are with that.