Re: DBSpace Use by Database
On Oct 8, 10:42 am, "Art Kagel" <art.ka...@gmail.com> wrote:
> You need to query sysextents and join to syschunks and sysdbspaces in the
> sysmaster database. Q&D:
>
> select e.dbsname, d.name, c.chknum, (c.chksize * c.pagesize) as
> ChnkTotalSpace, sum(e.size * c.pagesize) as DBSpaceUsedbyDatabase
> from sysdbspaces d, syschunks c, sysextents e
> where d.dbsnum = c.dbsnum
> and c.dbsnum = e.chunk
> group by 1, 2, 3, 4
> order by 1, 2, 3;>
> That will get this for you by chunk. If you want it be by dbspace either
> save the results into a temp table and select grouped by dbspace or use this
> query as a derived table.
>
> Now, that's using the features and table structure of IDS 11.50. Since you
> did not provide your version and platform information I've used the latest
> version. If you have earlier releases that do not support multiple page
> sizes, substitute 2048 for c.pagesize above. It is always a good idea to
> post your version and platform information even if it seems irrelevant.
>
> Art
>
> On Wed, Oct 8, 2008 at 9:09 AM, red_val...@yahoo.com
> <red_val...@yahoo.com>wrote:
>
>
>
> > Does anyone have a query, maybe vs. sysmaster, that will return space
> > usage of a database by dbspace? I'm not looking for dbspace size and
> > usage, which is well documented, but rather a result set that looks
> > like this:
>
> > Database DBSpacename DBSpaceTotalSize DBSpaceUsedbyDatabase
> > Accounting indexdbspace1 10,000 MB 500 MB
> > Accounting tabledbspace1 10,000 MB 1,000 MB
> > Marketing indexdbspace1 10,000 MB 1,000 MB
> > Marketing tabledbspace1 10,000 MB 2,000 MB
> > Marketing tabledbspace2 15,000 MB 3,000 MB
> > .
> > .
> > .
>
> > After much fruitless search, many close-but-no-cigars, and a couple of
> > lame efforts to concoct it myself, I yield to the superior collective
> > knowledge of the group.
> > _______________________________________________
> > Informix-list mailing list
> > Informix-l...@iiug.org
> >http://www.iiug.org/mailman/listinfo/informix-list
>
> --
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (a...@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 so much for the reply.
In manipulating your query, the result set was suspiciously regular,
so I tweaked the predicate so that this join:
and c.dbsnum = e.chunk
-- became this:
and c.chknum = e.chunk
Seems to be different now, but accurate? Need a verify. Others?
Forgot standard C.D.I. protocol: We're stuck with IDS 9.4x on HPUX 11
due to PeopleSoft; and IDS 10.x on Linux (RHAS/RHEL 3/4/5) elsewhere.