RE:npused in fragmented tables

Robert Donat wrote: >>Does anyone know the exact details of the columns in each? What I need >>it to know the following: >> >>Of a given extent, for a given fragmented table, HOW MUCH IS USED? (not >>just how much is allocated). >> >>The best I've been able to determine is the ti_nptotal and ti_npused >>columns of the systabinfo table where ti_partnum is in the fragments for >>the table I'm interested in. But, for tables on different servers with >>slightly different numbers of rows but the same extents and definition >>(roughly 20 pages worth of rows exist on one but not the other), the >>numbers seem to match. When I insert a large number of rows, the >>numbers stay the same. Any definitions on these columns, or ti_npdata >>as well? You could muck around $INFORMIXDIR/etc/sysmaster.sql to find out more about the system table columns. Here is a sysmaster SQL statement that I use to show allocated versus used pages in tables fragmented across dbspaces. set isolation to dirty read; select a.tabname as Table ,c.name[1,10] as DBSpace ,b.nptotal as PagesAllocated ,b.npused as PagesUsed ,(b.nptotal - b.npused) as PagesRemaining ,trunc(((b.npused / b.nptotal) * 100)) as PctUsed ,b.nrows as NbrRows ,b.nextns as NbrExtents ,b.fextsiz as FirstExtSize ,b.nextsiz as NextExtSize from sysptnhdr b ,systabnames a ,sysdbstab c where c.dbsnum = trunc(b.partnum/1048576) and b.partnum = a.partnum and a.owner='dwadmin' order by a.tabname ,c.name; ------------------------------------------------------------------- Jeff Craig | dbINTELLECT Technologies, Inc. Senior Consultant | Golden, CO | (303) 275-6954 ext. 8008 | email: settler@dbintellect.com | fax: (303) 275-2134 -------------------------------------------------------------------