calcualting table & index size in MB
IBM Informix Dynamic Server Version 11.50.FC6
HP-UX B.11.31 ia64 (BL870c integrity server)
I've been asked by management and developers (many times) the size of tables
with and without the index space. There sometimes other information is also
asked for like number of rows or row size. But for the most part the question
is how big is table X.
So I'm trying to come up with a sql statement or multiple statements if needed
that I can put into a shell script that they can run.
Below is what I've come up with but I am no guru of the sysmaster DB so I am
not sure if I'm thinking correctly, especially about the index space if
detached.
Do I have it, am I totally off, or am I so close, BUT?
database sysmaster;
set isolation to dirty read;
select
n.tabname[1,18] as table,
n.dbsname[1,18] as database,
h.nrows,
round(h.npused*h.pagesize/1024/1024 , 2) as MB,
(select round(sum(npused*pagesize)/1024/1024,2) from sysptnhdr s
where s.lockid = h.partnum) as indexMB
from sysptnhdr h, systabnames n
where tabname in ("table1", "table2") and
h.partnum = n.partnum
John Adamski
Sr. Network Specialist
Graceland University
Not quite. Your calculation for index pages will only work for tables that
are not fragmented. A table fragment (other than the first) has a
different partnum than the table itself (which is the partnum of the first
fragment). Only the first fragment's partnum will be the same as the
lockid which is the table's overall partnum.
The best way is to link back to the table's tabid in the sysindices table
in the actual database to get the tabname and idxname. So, the indexMB
clause has to be:
(
select round(sum(npused*pagesize)/1024/1024,2)
from sysptnhdr s, mydatabase:sysindices i, mydatabase:systables t
where s.dbsname = 'mydatabase'
and s.tabname = i.idxname
and t.tabid = i.tabid
and t.tabname = n.tabname
) as indexMB
Also, to allow for fragmented tables, you need to change the 'MB' clause to
be:
round(sum(h.npused*h.pagesize)/1024/1024,2) as MB
So you add up all of the fragments. Otherwise fragmented tables will have
several output lines all with the same tabname.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.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 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 Fri, Mar 30, 2012 at 4:02 PM, John Adamski <adamski@graceland.edu> wrote:
> IBM Informix Dynamic Server Version 11.50.FC6
>
> HP-UX B.11.31 ia64 (BL870c integrity server)
>
> I've been asked by management and developers (many times) the size of
> tables
> with and without the index space. There sometimes other information is also
> asked for like number of rows or row size. But for the most part the
> question
> is how big is table X.
>
> So I'm trying to come up with a sql statement or multiple statements if
> needed
> that I can put into a shell script that they can run.
>
> Below is what I've come up with but I am no guru of the sysmaster DB so I
> am
> not sure if I'm thinking correctly, especially about the index space if
> detached.
>
> Do I have it, am I totally off, or am I so close, BUT?
>
> database sysmaster;>
> set isolation to dirty read;>
> select
>
> n.tabname[1,18] as table,
>
> n.dbsname[1,18] as database,
>
> h.nrows,
>
> round(h.npused*h.pagesize/1024/1024 , 2) as MB,
>
> (select round(sum(npused*pagesize)/1024/1024,2) from sysptnhdr s
>
> where s.lockid = h.partnum) as indexMB
>
> from sysptnhdr h, systabnames n
>
> where tabname in ("table1", "table2") and
>
> h.partnum = n.partnum
>
> John Adamski
>
> Sr. Network Specialist
>
> Graceland University
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae934081569464e04bc7ccc19