table statistics

[nkm]

Hi

I wanted to know is there a way of querying the allocation for a table in bytes and how much is used and how much of the space is free.

Also can you get the number of extents that are assigned for a table.

thanks!

 

[olded]

Hi:

You want to look at the database and table information available in the sysmaster database. The following script finds the number of extents assigned to a table and the size in pages:

database sysmaster;
select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size
from systabnames, sysptnext
where partnum = pe_partnum and dbsname = "testdb"
and tabname = "testtable"
group by 1, 2
order by 3 desc, 4 desc

Remove the tabname = "testtable" to get all tables.

I don't know this answer specifically:
>I wanted to know is there a way of querying the allocation >for a table in bytes and how much is used and how much of >the space is free.

I don't think Informix thought this was too inportant since when an extent fills, another one is assigned. I think you'll have to do this calculations yourself by getting the number of rows, size of rows, and size of indexs and determining it.

Regards,


Ed

 

[roelwe]

In the sysmaster database, you find the table sysptnhdr. This table contains three fields of your interest:
npused, nptotal, npdata.

Just to make myself clear: TBLspace is the total amount of extend allocated by a table or table partition. It is uniquely identified by a partnum.
hex(partnum)= 0x001000001
where 001 is the chunk-id and the rest is the id of the TBLspace in this chunk.

nptotal is the total amount of pages in the TBLspace of the table.
npused is the total amount of pages used in this TBLspace. Used means where there is at least one bit of data on the page.
npdata is the actual amount of data stored in this TBLspace.

you can join the sysptnhdr table with sysptprof for example to have the tablename.