IDS Forum

RE: Record Size

Posted By: Jarrod Teale
Date: Tuesday, 25 March 2008, at 4:03 p.m.

In Response To: RE: Record Size (Marcus Haarmann)

oncheck -pt <database>:<table> will also give you a good idea as to the
current 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/

Messages In This Thread

• Record Size
  PAUL GATHOGO -- Tuesday, 25 March 2008, at 2:48 a.m.
  • RE: Record Size
    Marcus Haarmann -- Tuesday, 25 March 2008, at 5:55 a.m.
    • RE: Record Size
      Jarrod Teale -- Tuesday, 25 March 2008, at 4:03 p.m.
  • Re: Record Size
    Walter Lowich -- Tuesday, 25 March 2008, at 6:39 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.