Sysmaster query for determining additional extents availble per table/index.

Question

Is there a Sysmaster query for determining additional extents availble per table/index?

Cause

Here is a simple method to see information about additional extents, extent size and next size. The query can be easily modified to show the information for specific tables or dbspaces.

With this query you can avoid reaching Error -136 ISAM error: no more extents.

Answer

SET ISOLATION TO DIRTY READ;

SELECT dbsname as dbname,
tabname as dbobject,
TRUNC((pg_frcnt/8),0) additional_extents,
count(*) num_of_extents,
sum( pe_size ) total_size,
ti_nptotal pages_allocated,
ti_npused pages_used,
ti_npdata data_pages,
ti_nrows data_rows,
ti_rowsize as row_size,
ti_fextsiz first_extent_size,
ti_nextsiz next_extent_size,
hex(partnum) partnum
FROM systabnames, sysptnext, systabinfo, syspaghdr
WHERE
dbsname != "HASHTEMP"
AND tabname not like "sys%"
AND partnum = pe_partnum
AND partnum = ti_partnum
AND pg_partnum = ((TRUNC(partnum/1048576,0)*1048576)+1)
AND pg_pagenum = (partnum-(TRUNC(partnum/1048576,0)*1048576))
GROUP BY 1, 2, 3, 6, 7, 8, 9, 10, 11, 12, 13
ORDER BY 3 asc, 4 desc;

https://www-304.ibm.com/support/docview.wss?uid=swg21469625