how do I figure possible extents and used extents to avoid running out

[WiccaChic]

Hey all. Anyone have a good query that will list by table the number of extents that table is using and the number of extents left to it before error?

 

[tm88gt]

I don't remember off-hand the maximum number of extents a table can have (i think it's 220+), but here's a query to determine the number of extents allocated to your tables:


database sysmaster;
select dbsname[1,10] database,
tabname[1,15] tablename,
count(*) num_of_extents,
sum( pe_size ) total_size
from systabnames, sysptnext
where partnum = pe_partnum
group by 1, 2
order by 3 desc, 4 desc;

 

[vpshriyan]

Hi,

Here is one more version of SQL that shows the status for the existing extents:

SELECT stn.dbsname,
stn.tabname,
sti.ti_nextns,
sti.ti_nrows,
ROUND(
sti.ti_nptotal *
(
select sh_pagesize from sysmaster:sysshmvals
)/1024/1024, 4
) mb_size

FROM sysmaster:systabnames stn, sysmaster:systabinfo sti
WHERE stn.partnum = sti.ti_partnum
ORDER BY sti.ti_nextns DESC;

Regards,
Shriyan

 

[WiccaChic]

Thanks fellas, I have seen similar versions of do that but my problem is that I am unsure how to use this info to know when I need to worry about running out of extents. Dumb question surely, but when I run these queries, what do I compare the results to in order to know I am in good standing?

 

[mjldba]

Hi,

If you have any IDS documentation, look for the "Performance Guide for IBM Informix Dynamic Server" book.

There's a section in "Table Performance Considerations" (Ch. 6 in my book) that gives you some oncheck stuff to execute on a table-by-table basis so you can avoid the dreaded error -136 (No More Extents) error.

Good luck

 

[mjldba]

I think this'll help if you don't have IDS documentation

http://www.informix.com.ua/doc/9.40/ct1t9na.pdf

and search for "Managing Extents