How Find Growth of Database?
Hi ! I will be thankful if any one can help me with a query which i can run against the sys* databases on daily/monthly basis to find the growth of my database. Or any better way to do it . I am working on Informix Dynamic Server Version 11.10.FC1X1 and 9.40.UC8W7. Best Regards, Vicky.
Growth implies history. So you will want to capture the results of the
query over time somewhere - perhaps a table.
select today, chknum, chksize, nfree
from syschunks;
Alas that is at a disk level, tells you how much space has been allocated,
not how much of the allocated space is unused.
select today, nptotal-npused npfree
from sysptnhdr;
That is how much of your allocated space is unused. So let's combine them:
database sysmaster;
insert into my_database:my_growth_table
select today, a.chknum, a.chksize, a.nfree, sum(b.nptotal-b.npused) npfree
from syschunks a, sysptnhdr b, systabextents c
where b.partnum=c.te_partnum
and c.te_chunk=a.chknum
group by 1,2,3,4
If you want to use filenames or dbspacenames instead of chunk number:
database sysmaster; { Filename version }
insert into my_database:my_growth_table
select today, a.fname, a.chksize, a.nfree, sum(b.nptotal-b.npused) npfree
from syschunks a, sysptnhdr b, systabextents c
where b.partnum=c.te_partnum
and c.te_chunk=a.chknum
group by 1,2,3,4
database sysmaster; { dbspacename version }
insert into my_database:my_growth_table
select today, d.name[1,12], a.chksize, a.nfree, sum(b.nptotal-b.npused)npfree
from syschunks a, sysptnhdr b, systabextents c, sysdbspaces d
where b.partnum=c.te_partnum
and c.te_chunk=a.chknum
and a.dbsnum=d.dbsnum
group by 1,2,3,4
(expression) name chksize nfree npfree
06/13/2008 data_dbs 500000 231542 1325
06/13/2008 rootdbs 250000 249393 262
06/13/2008 tmpdb 262144 262091 48
06/13/2008 rootdbs 12800 2558 1112
06/13/2008 logsp 256000 97227 48
06/13/2008 sbspace 12800 554 351
06/13/2008 dbsp1 250 5 310
06/13/2008 dbsp1 500 49 352
j.
Sane ego te vocavi. Forsitan capedictum tuum desit.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
VICKY H
Sent: Friday, June 13, 2008 6:33 AM
To: ids@iiug.org
Subject: How Find Growth of Database? [12412]
Hi !
I will be thankful if any one can help me with a query which i can run
against
the sys* databases on daily/monthly basis to find the growth of my database.
Or any better way to do it .
I am working on Informix Dynamic Server Version 11.10.FC1X1 and 9.40.UC8W7.
Best Regards,
Vicky.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Vicky.
Try this:
select dbsname, sum(nptotal) as totalpages, sum(npused) usedpages,sum(npdata) datapages
from sysptnhdr a, systabnames b
where a.partnum = b.partnum
group by 1;
Run the query periodically and put the output in some table in your own
"statistic_database". Then you may compare the values.
Remark: The dbspaces you have created will take up more room because you
will leave some space free for growth.
HTH, Reinhard.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
> VICKY H
> Sent: Friday, June 13, 2008 12:33 PM
> To: ids@iiug.org
> Subject: How Find Growth of Database? [12412]
>
>
> Hi !
>
> I will be thankful if any one can help me with a query which
> i can run against
> the sys* databases on daily/monthly basis to find the growth
> of my database.
>
> Or any better way to do it .
>
> I am working on Informix Dynamic Server Version 11.10.FC1X1
> and 9.40.UC8W7.
>
> Best Regards,
> Vicky.
>
>
> **************************************************************
> *****************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>