sizes
A user wanted SQL (rather than GUI tools like Server Studio or OAT) to report database, table and index sizes. Art Kagel supplied sysmaster queries: joining sysextents to syschunks and summing size*pagesize, grouped by dbsname for database size and by dbsname/tabname for table and index size. For allocated vs. used space he gave a query joining systabnames to sysptnhdr using npdata/npused * pagesize; the poster noted nptotal works better than npdata for total allocated. Art added that Server Studio's size figures are considered accurate.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hello except using the tools server studio, oat ...etc i want to know how to have a database size, table size, index size i guess it's on sysmaster database but i don't have the time please need ur help thank you
John:
Try these queries:
Database Size:
select dbsname, sum(size * pagesize)
from sysextents se, syschunks sc
where se.chunk = sc.chknum
group by 1
order by 1;
Table/Index Size:
select dbsname, tabname, sum(size * pagesize)
from sysextents se, syschunks sc
where se.chunk = sc.chknum
group by 1, 2
order by 1, 2;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, Feb 2, 2016 at 6:39 AM, SMITH JOHN <daylight@webmails.com> wrote:
> hello
> except using the tools server studio, oat ...etc i want to know how to
> have a
> database size, table size, index size
>
> i guess it's on sysmaster database but i don't have the time please
>
> need ur help
>
> thank you
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f642bd8a37e37052ac81b36
thanks art another request please for this tbsname, total_size allocated, total size used
Here:
select dbsname, tabname, sum(npdata * pagesize) size, sum(npused *pagesize) used
from systabnames st, sysptnhdr sp
where st.partnum = sp.partnum
and st.dbsname = 'mydatabase'
and st.tabname = 'mytable' -- Could be: st.tabname matches
'*tablespec*' if you want.
;
Divide the sizes by 1024 if you want to report in KB.
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, Feb 2, 2016 at 7:34 AM, SMITH JOHN <daylight@webmails.com> wrote:
> thanks art
> another request please for this
>
> tbsname, total_size allocated, total size used
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0103de4e017d7e052ac8f3aa
thank you so much
just for this one the filed needed was nptotal inplace of ndpdata
thank you again
select dbsname, tabname, sum(npdata * pagesize) size, sum(npused *pagesize) used
from systabnames st, sysptnhdr sp
where st.partnum = sp.partnum
and st.dbsname = 'mydatabase'
and st.tabname = 'mytable' -- Could be: st.tabname matches
'*tablespec*' if you want.
;
hi art do you think that tools like server studio give a right information about database and table sizes ?
John: AFAIK Server Studio reports sizes correctly. If not, email support@agsltd.com and they'll fix it. They are very responsive. If they give you any trouble, let me know and I'll get it fixed. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Fri, Feb 26, 2016 at 9:18 AM, SMITH JOHN <daylight@webmails.com> wrote: > hi art > do you think that tools like server studio give a right information about > database and table sizes ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013a230a3f76c4052cad12f3