Record Size

All, Guys how can i calculate the size of a record in a table i.e. in terms of disk space it occupies? Am trying to project the my DB growth rate and plan for future disk upgrade. Paul.

Hi, Easiest way is to create a dbschema for the db. It will give you the row size for each table in the resulting script. You have to calculate how many records will fit into one page approximately (depends on your page size, standard is 2K) and then calculate the number of pages you might need for your data to estimate the size lets say per year. (e.g. a row size of 1500 bytes will occupy one 2k page). At this stage, it makes sense to organize table extents in a way to pre-allocate lets say one extent per year (which we do for huge tables to prevent running out of extents). You have to take into calculation also the index size output, cause your indexes also occupy space ;) Same rule for records per page applies here. Marcus -----Original Message----- From: PAUL GATHOGO [mailto:pgathogo@gmail.com] Sent: Tuesday, March 25, 2008 7:48 AM To: ids@iiug.org Subject: Record Size [11700] All, Guys how can i calculate the size of a record in a table i.e. in terms of disk space it occupies? Am trying to project the my DB growth rate and plan for future disk upgrade. Paul. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

oncheck -pt <database>:<table> will also give you a good idea as to thecurrent usage. Look for "Number of data pages" and multiply this by the page size on your system (2k typically) The indexes are also listed in here, so they will show the space in "Number of pages allocated". If the table is fragmented, then queries to the sysfragments table may be easier than the oncheck output. Something like (will work for both fragmented and non fragmented tables.) The sub suery can be refined to only look at specific tables / indexes rather than the entire database. -- Pages allocated (and extents) SELECT SUM(pe_size) pages, COUNT(*) extents FROM sysmaster:sysptnext WHERE pe_partnum in ( SELECT partn FROM <database>:sysfragments where dbspace ='<dbspace_of_table>' ) -- Pages used for indexes / data rows. SELECT COUNT(*) usedpages FROM sysmaster:systabpagtypes WHERE tp_partnum in ( SELECT partn FROM <database>:sysfragments WHERE dbspace ='<dbspace_of_table>' ) AND tp_type IN (8,12); -- The 8 and 12 are data pages (rows) and index pages. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Marcus Haarmann Sent: Tuesday, 25 March 2008 10:55 p.m. To: ids@iiug.org Subject: RE: Record Size [11701] Hi, Easiest way is to create a dbschema for the db. It will give you the row size for each table in the resulting script. You have to calculate how many records will fit into one page approximately (depends on your page size, standard is 2K) and then calculate the number of pages you might need for your data to estimate the size lets say per year. (e.g. a row size of 1500 bytes will occupy one 2k page). At this stage, it makes sense to organize table extents in a way to pre-allocate lets say one extent per year (which we do for huge tables to prevent running out of extents). You have to take into calculation also the index size output, cause your indexes also occupy space ;) Same rule for records per page applies here. Marcus -----Original Message----- From: PAUL GATHOGO [mailto:pgathogo@gmail.com] Sent: Tuesday, March 25, 2008 7:48 AM To: ids@iiug.org Subject: Record Size [11700] All, Guys how can i calculate the size of a record in a table i.e. in terms of disk space it occupies? Am trying to project the my DB growth rate and plan for future disk upgrade. Paul. ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/

Use the record length (assuming no VARCHARs), add 4 and divide that into 2020, assuming a 2K page size. That will tell you how many rows per page will be allowed. Add the index length (sum of all column sizes) to determine how many pages will be needed for indexes. The calculations get more complex if VARCHARs are involed. PAUL GATHOGO <pgathogo@gmail.com> wrote: All, Guys how can i calculate the size of a record in a table i.e. in terms of disk space it occupies? Am trying to project the my DB growth rate and plan for future disk upgrade. Paul. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! --------------------------------- Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.