dbspace space status

Hi Another problem with Informix v5. Cannot insert data on table. It appeared from applicaton log that there is no free space. So after deleting some old data the problem is resolved. It happened twice on two tables. But strangely at that point of time tbstat -d shows that there is plenty of space available on each of the chunks Chunks address chk/dbs offset size free bpages flags pathname a096f66c 1 1 0 450000 288136 PO- /dev/informix/rootcen a096f704 2 2 0 480000 23434 PO- /dev/informix/logcen a096f79c 3 3 0 160000 155200 PO- /dev/informix/rootdal a096f834 4 4 0 160000 154792 PO- /dev/informix/rootn a096f8cc 5 5 0 160000 155480 PO- /dev/informix/rootnw a096f964 6 6 0 650000 403277 PO- /dev/informix/datadal a096f9fc 7 7 0 650000 345048 PO- /dev/informix/datan a096fa94 8 8 0 650000 502766 PO- /dev/informix/datanw I there any way to find the correct space status? Someone please reply Prab

It might be that the table in question has too many extents. All of a table's extent locations must be recorded on the table's TABLESPACE or partition page along with the key details of any attached indexes (in OnLine 5.xx there are only attached indexes so that's all indexes). For a table with no indexes that would be about 240 extents but the more indexes and the longer the index keys that define those indexes the fewer extents the partition page will have room to catalog. So, what you have to do is to reorganize the table into fewer extents by unloading the data then dropping, recreating, and reloading the table with a larger extent size (I would set that to the number of pages the table is currently occupying) and a sufficiently large next size setting that it will accumulate perhaps one or two new extents a year. You can get all the information you need from the tbcheck -pe report. There you can count up the number of extents to see if it is close to the theoretical limit and you can add up the number of pages in those extents to see how to resize the initial extent when you recreate the table. Note that the maximum practical extent size is the size of a completely free chunk or of the largest free extent. Note that the free space in your existing dbspaces may be highly fragmented (you can see that from the tbcheck -e report as well as free extents are reported there also) and there may not be a large enough free extent to hold the single large extent you will want to allocate. If that is so, you will either have to unload and reorg many tables (perhaps the whole database) or you will have to create a new empty dbspace to hold the reorganized table. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 10, 2009 at 8:59 AM, PRABAL DAS <d.prabal@gmail.com> wrote: > Hi > > Another problem with Informix v5. > > Cannot insert data on table. It appeared from applicaton log that there is > no > free space. So after deleting some old data the problem is resolved. It > happened twice on two tables. > > But strangely at that point of time tbstat -d shows that there is plenty of > space available on each of the chunks > > Chunks > address chk/dbs offset size free bpages flags pathname > a096f66c 1 1 0 450000 288136 PO- /dev/informix/rootcen > a096f704 2 2 0 480000 23434 PO- /dev/informix/logcen > a096f79c 3 3 0 160000 155200 PO- /dev/informix/rootdal > a096f834 4 4 0 160000 154792 PO- /dev/informix/rootn > a096f8cc 5 5 0 160000 155480 PO- /dev/informix/rootnw > a096f964 6 6 0 650000 403277 PO- /dev/informix/datadal > a096f9fc 7 7 0 650000 345048 PO- /dev/informix/datan > a096fa94 8 8 0 650000 502766 PO- /dev/informix/datanw > > I there any way to find the correct space status? > Someone please reply > > Prab > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0ce0f3f6ece82004733a211f

Thanks for your valuable advice. To determine no. of extents we need downtime as tbcheck might lock the table. Just another question, is it true that running UPDATE STATISTICS on a daily basis update correct space information in catalog tables so that tbcheck -d always give correct information?

There is no data reported by either oncheck/tbcheck or onstat/tbstat that is affected by UPDATE STATISTICS. All of the values that are reported by those utilities are acquired either from in-memory values that are kept up-to-date or from scanning disk structures directly and making actual counts. In OnLine 5 the only data that update statistics maintains are in the database catalog: - In systable: Number of rows, number of pages, number of data pages - In syscolumns: Second largest and second smallest values for the column - In sysindexes: Number of nodes, number of keys, number of unique keys, number of levels in the B+Tree So, some of the information you need to make decisions about reorganizing tables etc. can be obtained by querying systables in each database after running update statistics, yes, but not all. For instance the number of extents is not recorded in the system catalog at all. You can only get that by examining the tbcheck -pe report in OnLine 5.xx (also on the oncheck -pt and -pT reports but those are only available in later releases of IDS - 7.30/9.21 and later IB but it may have been earlier in IDS don't remember back that far). I used to have a program that I wrote for OnLIne 5.xx that scanned through a table's extent records and its bitmap pages and produced a report that was surprisingly similar to the IDS oncheck -pt report that came later, but the source is lost. It's progeny, the printfreeB.ec utility, is in my utils2_ak package but that uses sysmaster tables rather than read the disks directly so it will only work on 7.xx and later. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Sep 11, 2009 at 6:01 AM, PRABAL DAS <d.prabal@gmail.com> wrote: > Thanks for your valuable advice. To determine no. of extents we need > downtime > as tbcheck might lock the table. > Just another question, is it true that running UPDATE STATISTICS on a daily > basis update correct space information in catalog tables so that tbcheck -d > always give correct information? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015173ff2e037ddd604734d8129