Maximum number of pages for a partition

10.0FC10 on Solaris I have a table fragmented round-robin over three dbspaces,. close to hitting the 16775000 page limit (see oncheck below). In order to avoid it stopping when it hits the limit I can obviously refregment the table over more spaces (or use a larger page size). But that will take many, many hours. Are there any alternatives anyone can thionk of, adding anothter partition to a table fragmented by round-robin etc...? Thanks Neil ************************************************* Table fragment partition table_part1 in DBspace dbspace11 Physical Address 20:5 Creation date TBLspace Flags 800902 Row Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 508 Number of special columns 6 Number of keys 0 Number of extents 8 Current serial value 354653767 Current SERIAL8 value 1 Current REFID value 1 Pagesize (k) 2 First extent size 699050 Next extent size 699050 Number of pages allocated 16252910 Number of pages used 15624579 Number of data pages 15620703 Number of rows 118100854 (repeated for 2 further partitions).

Hello Mr Neil! Our experience with IDS limits were solved only when we increased our page sizes. Since your table is already fragmented over dbspaces, I don´t think you´ll have another option, really. But I could always be wrong, it´s just my historical option, ok? Best regards. Em 06/10/2010 12:41, Neil Truby escreveu: > 10.0FC10 on Solaris > > I have a table fragmented round-robin over three dbspaces,. close to hitting > the 16775000 page limit (see oncheck below). > > In order to avoid it stopping when it hits the limit I can obviously > refregment the table over more spaces (or use a larger page size). But that > will take many, many hours. > > Are there any alternatives anyone can thionk of, adding anothter partition > to a table fragmented by round-robin etc...? > > Thanks > Neil > > ************************************************* > Table fragment partition table_part1 in DBspace dbspace11 > > Physical Address 20:5 > Creation date > TBLspace Flags 800902 Row Locking > TBLspace contains VARCHARS > TBLspace use 4 bit bit-maps > Maximum row size 508 > Number of special columns 6 > Number of keys 0 > Number of extents 8 > Current serial value 354653767 > Current SERIAL8 value 1 > Current REFID value 1 > Pagesize (k) 2 > First extent size 699050 > Next extent size 699050 > Number of pages allocated 16252910 > Number of pages used 15624579 > Number of data pages 15620703 > Number of rows 118100854 > > (repeated for 2 further partitions). > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Alexandre Marini Tecnologia da Informação - DBA msn: alexandre_marini@hotmail.com SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix Cert-Info-Mgmt_color IBM Informix Dynamic Server Certified Professional V10 / V11

If you attach a new fragment to a ROUND ROBIN fragmented table IB that it may rebalance the rows across the now four partitions which would lock the table for a LOOONNG time. And if the engine does not rebalance the rows, then inserts to the original three partitions will still fail when they hit the 16million page limit. I would do the following: - Rename the current table - Create a new table with four or more fragments (still with a different name) - Replace the original tablename with a VIEW on a UNION of SELECT * FROM original_table and SELECT * FROM new_table. - Create an INSTEAD OF insert trigger on the view that inserts the rows into the new table. - Over time move rows from the original table to the new one. - Drop the original table - Rename the new table to the original name replacing the VIEW. That will keep the table online as much as possible. I just tested this and it works in any version that supports INSTEAD OF triggers (10.00 & later IB). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 6, 2010 at 12:41 PM, Neil Truby <neil.truby@ardenta.com> wrote: > 10.0FC10 on Solaris > > I have a table fragmented round-robin over three dbspaces,. close to > hitting > the 16775000 page limit (see oncheck below). > > In order to avoid it stopping when it hits the limit I can obviously > refregment the table over more spaces (or use a larger page size). But > that > will take many, many hours. > > Are there any alternatives anyone can thionk of, adding anothter partition > to a table fragmented by round-robin etc...? > > Thanks > Neil > > ************************************************* > Table fragment partition table_part1 in DBspace dbspace11 > > Physical Address 20:5 > Creation date > TBLspace Flags 800902 Row Locking > TBLspace contains VARCHARS > TBLspace use 4 bit bit-maps > Maximum row size 508 > Number of special columns 6 > Number of keys 0 > Number of extents 8 > Current serial value 354653767 > Current SERIAL8 value 1 > Current REFID value 1 > Pagesize (k) 2 > First extent size 699050 > Next extent size 699050 > Number of pages allocated 16252910 > Number of pages used 15624579 > Number of data pages 15620703 > Number of rows 118100854 > > (repeated for 2 further partitions). > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Oct 6, 6:41 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > 10.0FC10 on Solaris > > I have a table fragmented round-robin over three dbspaces,. close to hitting > the 16775000 page limit (see oncheck below). If you can upgrade to v11.x, you will have the option to use compression. Depending on the variation of the data you may see a compression percentage better than 50%. There is a small tool that can be used on version 7, 9 and 10 to calculate an expected compression ratio. http://www-01.ibm.com/software/sw-library/en_US/detail/L181272S36452U64.html

Compression is still an extra cost option even in ultimate edition. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Thu, Oct 7, 2010 at 2:55 PM, Claus Samuelsen <claus.samuelsen@gmail.com>wrote: > On Oct 6, 6:41 pm, "Neil Truby" <neil.tr...@ardenta.com> wrote: > > 10.0FC10 on Solaris > > > > I have a table fragmented round-robin over three dbspaces,. close to > hitting > > the 16775000 page limit (see oncheck below). > > If you can upgrade to v11.x, you will have the option to use > compression. Depending on the variation of the data you may see a > compression percentage better than 50%. There is a small tool that can > be used on version 7, 9 and 10 to calculate an expected compression > ratio. > http://www-01.ibm.com/software/sw-library/en_US/detail/L181272S36452U64.html > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >