Administering Informix Dynamic Server, Building the Foundation

Table Sizing Documents

As mentioned in Appendix A, the table sizing worksheets and programs can be found here. There are two documents/Java methods you can use. The first, referred to as the "simple" approach is for tables with a row size smaller than the page size (one or more rows to a dbspace page). If your table row length is longer than a page, you need to use the "full" approach which will calculate full and partial remainder pages. The better option though is to place the table in a dbspace with a larger page size.

From a Java-method perspective, the "simple" program will only calculate values for tables with a row size smaller than the page size. If the row length is longer, you'll get an error message for that particular table in the sizing report output. The error notwithstanding, the program will continue unitl it reaches end-of-file. The "full" program calculates both the "simple" cases as well as the more complex cases where a row spans multiple pages. As such, if you want to use just one program, the "full" program would be the best. Realize though, it will not warn you about rows that are too long for the page, an inefficient and costly way of designing a database.

The Java methods accept a single parameter -- a pathed input file name. By default, it will look in the local directory first so you don't have to fully path if the file is in your current working directory. For example:

java FullTableSizing long_sample_in.txt


java SimpleTableSizing /my_data/extract/long_sample_in.txt

The sizing report output generated goes to STANDARD OUT (or your screen) unless you redirect it to a file or some other mechanism.

The methods are designed to calculate the first and next size for multiple tables at one time. The source file must have the following fields, whitespace or tab separated:

table_name varchar(250) not null
dbspace_page_size smallint, values = 2,4,6,8,10,12,14,16
initial_row_count integer, values 0 to positive number
growth_row_count integer, values 0 to positive number
row_length, integer, values 1 to positive number

For example:


The output file will have the table name, dbspace page size, first and next extent sizes as well as an approximation of the wasted space per page. Depending on the length of the table name, this information might be on one output row or the table name could be on one row followed by the sizing information on another row as shown here:


When looking at the wasted space number, you should consider this value to determine if you might be able to more efficiently store the table's data in a dbspace with a different page size.

The Java methods are courtesy of JACQUES ROY, Informix and programming wizard extraordinarie, who was kind enough to donate his time and skills to putting the sizing algorithms into an easy-to-use form. Thank you, thank you, thank you!

If you have any comments, please send them to me.

Download the full table sizing worksheet here

Download the simple table sizing worksheet here

Download the full table sizing Java method (FullTableSizing.class) here. Use right-click / "save as"

Download the simple table sizing Java method (SimpleTableSizing.class) here. Use right-click / "save as"