Re: dbspace stat

The only time that ti_npused should go down would be when a table or fragment has been recreated somehow, as with ALTER FRAGMENT or ALTER INDEX TO CLUSTER. It's also possible that it would go down if you dropped and recreated the table. But without one of these conditions being met, I'm not aware of any circumstance under which it would decrease. To answer the original question, my answer has always been to "ballpark" the amount of space used, by dividing 2020 by the size of a row to determine how many rows fit on a page, truncating any decimals and calling this number 'rp.' I then take the number of rows in the table divided by 'rp' to give me the total number of data pages being used. The underlying concept? "Rows" divided by "Rows Per Page" gives "Pages." Admittedly this is a bit crude, but it's effective. Note that it only counts data pages used, not index pages, which gets more complicated. But it's definitely a good way to see how much data you actually have. Also note that minor adjustments must be made if a row is actually larger than a page (i.e., > 2020 bytes). John H. Frantz wrote in message <365AD028.4260E4E5@rl.is>... >oncheck -pT takes some time for larger tables which suggests to me that>it scans the table rather than simply querying the sysmaster tables. It >would seem that the current number of pages used in a table can't be >obtained through the sysmaster tables. > >However, I have noticed on occasion a *decrease* in ti_npused for a >given table. Could it be that ti_npused is in fact set to a currently >correct value under certain conditions? Here are some candidate >conditions: > > - Update statistics (nope, tested that). > - Reboot of the database engine (nope, tested that). > - Version upgrade of the database engine. > >Maybe someone from Informix could say. > >June Tong wrote: >> >> mosserp@WellsFargo.COM wrote: >> >> > Recently, we have been trying to find out how to get not only total_space >> > (sum (chksize)) and allocated_space (sum (chksize - nfree)), but also the >> > amount of space actually USED within the extents. The closest we've come is >> > sysmaster:systabinfo.ti_npused (comes from sysptnhdr.npused), which is at >> > the tablespace/partition level. This is the same datum that shows up in >> > oncheck -pT output.>> > >> > The problem with this is that ti_npused, according to Informix, is the >> > MAXIMUM number of pages that have been used SO FAR, i.e., it's basically a >> > "high-water" mark. That means that large deletes, freeing up large numbers >> > of pages, would NOT reduce npused! But something that I saw in a posting >> > from June recently SEEMED to imply that npused is intended to go up AND DOWN >> > as pages are used/freed. June, can you comment? Or anyone else for that >> > matter? >> >> *I* said that??? I think I was talking about npdata, which does go up and >> down. It is definitely true that npused never goes down. >> >> I always use oncheck -pT to show the number of data, index, bit-map, BLOB and >> free pages. AFAIK, this is always accurate. This information must be in >> sysmaster somewhere, but I don't know where. >> >> June >> -- >> june_t@hotmail.com >> Grounded in Palo Alto, living on M&M's (plain) >________________________________________________________________ >John H. Frantz Power-4gl: Extending Informix-4gl >john@rl.is http://www.rl.is/~john/pow4gl.html