Creating a dbspace with a non-default page size

Hi, My environment : IDS 12.10 FC6 with HP-UX 11.31 I'm creating a new instance for a datawarehouse and I want to use the option -k pagesize of the command "onspaces". I want to use the non-default size only for data dbspaces, not for root, logical, physical or temporary dbspaces. My question is: What are the pitfalls of create dbspaces, for example of pagesize of 16K. I'm concern about wasted space and excessive use of buffers. Please provide any clues according your experience. Best regards, Roger

General rules: - Indexes work best on wide pages (all but the tiniest indexes anyway), so 16K is good. - Data tables should be placed on the page size the minimizes waste. That could be any size and depends on the row size and whether there are any variable length columns in the table. For fixed size rows, you can calculate the waste as follows: ((pagesize - 24) MOD (rowsize + )4) = bytes wasted per page. Divide that by the number of rows per page to get wasted bytes per row and compare that number across the various page sizes available to you (so 4, 8, 12, or 16K on AIX, MacOS, & Windows or 2, 4, 6, 8, 10, 12, 14, 16K on other platforms) to find the best fit. Over all, you may want to settle for the near best for some tables to minimize the number of different page sizes you have to support or you may decide to avoid putting data on 16K pages and reserve those dbspaces for indexes only. - Each page size has its own buffer cache, so there is additional management overhead to maintaining the right cache size for multiple page sizes without wasting too much memory. However, this means that you can use page size to isolate your busiest tables from each other and isolate index cache from data cache. This can be a big help on a busy system. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on 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, Dec 21, 2016 at 11:58 AM, ROGER VILCA <rvilca@luzdelsur.com.pe> wrote: > Hi, > > My environment : IDS 12.10 FC6 with HP-UX 11.31 > > I'm creating a new instance for a datawarehouse and I want to use the > option > -k pagesize of the command "onspaces". > I want to use the non-default size only for data dbspaces, not for root, > logical, physical or temporary dbspaces. > > My question is: > What are the pitfalls of create dbspaces, for example of pagesize of 16K. > > I'm concern about wasted space and excessive use of buffers. > > Please provide any clues according your experience. > > Best regards, > Roger > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b86c6de0baeec05442e53df

Just to add to this excellent advice, if you plan to use table compression you'll probably end up sticking to 2 kb or 4 kb dbspaces to avoid wasting space and actually getting the benefit of compression as the 255 rows/page limit still applies. For my benefit did Informix 10.00, 11.10 and 11.50 support creating an index on a table and placing it in a dbspace of a different page size? 11.70 and 12.10 certainly do. Ben.