DB Size

Does anyone have a quick SMI query that will total up the space allocated and used for a particular database...understanding that some tables have different page sizes ...including tables and indexes ... Found a couple on the IBM site, but they don't seem to be working properly .. Thanks in advance ... Peter Logan Senior Database Administrator Phone: 616/878-8309

select dbsname as database, tabname as table, name as dbspace, sum(size * sd.pagesize) from sysextents se, syschunks sc, sysdbspaces sd where se.chunk = sc.chknum and sc.dbsnum = sd.dbsnum and tabname not matches '*[A-Z]*' group by 1, 2, 3 order by 1, 2, 3; If you do not want the space usage broken down by dbspace then: select dbsname as database, tabname as table, sum(size * sd.pagesize) from sysextents se, syschunks sc, sysdbspaces sd where se.chunk = sc.chknum and sc.dbsnum = sd.dbsnum and tabname not matches '*[A-Z]*' group by 1, 2 order by 1, 2; The tabname filter removes overhead extents like the tablespace tablespace in each dbspace and the Smart Large Object meta data blocks. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. 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 Tue, Dec 13, 2011 at 11:57 AM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > Does anyone have a quick SMI query that will total up the space allocated > and used for a particular database...understanding that some tables have > different page sizes ...including tables and indexes ... > > Found a couple on the IBM site, but they don't seem to be working properly > ... Thanks in advance ... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340cef296cbe04b3fc5c8d

A quick one: SELECT round(sum(size*pagesize)/(2048*2048),2) as TOTAL_MB FROM sysmaster:sysextents e, sysmaster:syschunks c WHERE c.chknum = e.chunk and dbsname=dbinfo('dbname') ; Cheers Ali