npused per fragment monitoring

Hi all, I'm trying to find a way how to monitor current filling status of all the tablespaces using 2k pages. (IDS<10) At the moment i'm using following sql to find tablespaces running out of available pages/fragment (IDS npused=16777215 limitation) : #################### database sysmaster;select spt.tabname tabname, dbinfo('dbspace', spt.partnum) dbspace, sum(sti.ti_nptotal) sumnptot from sysptprof spt, systabinfo sti, sysdbspaces sdb where spt.partnum = sti.ti_partnum and sdb.name = dbinfo('dbspace', spt.partnum) and spt.dbsname not in ("sysmaster", "sysutils", "onpload") {* omit system databases *} and not spt.tabname matches "*tmp*" {* omit temporary tables *} and not spt.tabname matches "*temp*" {* omit temporary tables *} and spt.tabname <> "TBLSpace" {* omit control tables *} and sdb.is_temp = 0 {* omit temporary dbspaces *} and sdb.dbsnum <> 1 {* omit root database space *} group by 1,2 order by sumnptot desc,dbspace,tabname #################### My problem is that for some reason, when data reach 16777215pg/frag limitation and after purged, the systabinfo.ti_nptotal is not updated by system. This means once the limit is hit, or everytime data purged, the above monitoring is worthless. Neither oncheck -pt is showing correct values of the npalloc, npused columns. It looks to me like IDS problem with deallocation of used space, but i'd like to ask you for your opinion, or maybe some trick how to udpate these syscolumns. kind regards Lubor

It's not a problem, IDS is designed that way. Data pages that become empty through deletions are not returned to the common free pool, in addition, just because you deleted a large amount of data does not mean that you have completely emptied any pages completely so that those pages would become unused. Finally, once a page has been used for data, it is not removed from the npused count. Only a full table reorg will do that. You can run oncheck -pT <database>:<table> to see the actual number of free pages. My prinfreeB.ec utility in the utils2_ak package produces a similar report. Like oncheck it reads the bitmap pages and determines the fullness of each page. If you don't like the format of the oncheck -pT report you can download utils2_ak and modify the output of printfreeB.ec to your needs. Art On Thu, Aug 7, 2008 at 9:54 AM, LUBOR NECHANICKY <lubor.nechanicky@dhl.com>wrote: > Hi all, > > I'm trying to find a way how to monitor current filling status of all the > tablespaces using 2k pages. (IDS<10) > At the moment i'm using following sql to find tablespaces running out of > available pages/fragment (IDS npused=16777215 limitation) : > > #################### > database sysmaster;> select > > spt.tabname tabname, > > dbinfo('dbspace', spt.partnum) dbspace,> > sum(sti.ti_nptotal) sumnptot > from sysptprof spt, systabinfo sti, sysdbspaces sdb > where spt.partnum = sti.ti_partnum > and sdb.name = dbinfo('dbspace', spt.partnum) > and spt.dbsname not in ("sysmaster", "sysutils", "onpload") {* omit system > databases *} > and not spt.tabname matches "*tmp*" {* omit temporary tables *} > and not spt.tabname matches "*temp*" {* omit temporary tables *} > and spt.tabname <> "TBLSpace" {* omit control tables *} > and sdb.is_temp = 0 {* omit temporary dbspaces *} > and sdb.dbsnum <> 1 {* omit root database space *} > group by 1,2 > > order by sumnptot desc,dbspace,tabname > #################### > > My problem is that for some reason, when data reach 16777215pg/frag > limitation > and after purged, the systabinfo.ti_nptotal is not updated by system. This > means once the limit is hit, or everytime data purged, the above monitoring > is > worthless. > Neither oncheck -pt is showing correct values of the npalloc, npused > columns. > > It looks to me like IDS problem with deallocation of used space, but i'd > like > to ask you for your opinion, or maybe some trick how to udpate these > syscolumns. > > kind regards > Lubor > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- 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.

Art, thanks for clarification, i'll check your utils. Lubor

LUBOR NECHANICKY wrote: > > My problem is that for some reason, when data reach 16777215pg/frag limitation > and after purged, the systabinfo.ti_nptotal is not updated by system. This > means once the limit is hit, or everytime data purged, the above monitoring is > worthless. > Neither oncheck -pt is showing correct values of the npalloc, npused columns. > npused never gets decremented over the lifetime of a table.