Sizing a database

Quick easy question ... I presume.... We are looking to migrate a database from one server to another - what is the easiest way to determine the size of the current database? Is it as simple as 'onstat -d', adding up the number of pages for each chunk & multiply by the page size? Thanks in advance

Hi, that's the case when you're migrating the whole instance of the server to another machine. It's also pretty accurate if you have just this one database in your instance and you're moving that database only (assuming that the size of your database is much bigger than the size of the system databases, e.g. sysmaster and sysutils which usually add only negligible overhead). However, if you have several databases in your server instance and you're talking about migrating only one of them, then the numbers of "onstat -d" are generally not of much help, because they comprise the space used by all databases in the system. The exception to that rule is when you have your (several) databases separated into different dbspaces, so that each database occupies it's own dbspace. In that case you can select the appropriate numbers (i.e. chunks of the respective dbspace(s)) from "onstat -d" output to do the calculation. If you have several databases that are not separated in different dbspaces, then you probably want to use some "oncheck" command to get the desired numbers. Look at the oncheck options and the output produced by them to find the format that suites your needs best. Alas, as far as I know there's nothing that gives a single, straight number. I'd probably utilize "oncheck -pe" and send this output through a some "sed"/"awk"/"grep"/"bc" scripts, but that's "a matter of (UNIX) taste" ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions forum.subscriber@iiug.org wrote on 29.04.2004 17:55:22: > Quick easy question ... I presume.... > > We are looking to migrate a database from one server to another - what is the easiest way to determine the size of the current database? Is it as simple as 'onstat -d', adding up the number of pages for each chunk & multiply by the page size? > > Thanks in advance >

Hi there, I am using the following script to find the total space and the balance free space, might be useful. Script will run from command-prompt on unix, modify it as per the requirement. Our Env. IDS 9.30 UC3 AIX 5.1. -------------------------------------------------------------------------------- ---------------------------------------------- dbaccess sysmaster - <<! 2>/dev/null select d.dbsnum,name dbspace, -- name truncated to fit on one line sum(chksize) pages_size, -- sum of all chuncks size pages sum(chksize) - sum(nfree) pages_used, sum(nfree) pages_free, -- sum of all chunks free pages round ((sum(nfree)) / (sum(chksize)) * 100, 2) free_space from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and d.is_blobspace = 0 group by 1, 2 having round ((sum(nfree)) / (sum(chksize)) * 100, 2) > 0 order by 6 into temp A; select dbspace[1,18], pages_size, pages_free, free_space from A; -------------------------------------------------------------------------------- ----------------------------------- Sushil... >From: "Martin Fuer...." <MARTINFU@de.ibm.com> >To: ids@iiug.org >Subject: Re: Sizing a database [2910] Date: Fri, 30 Apr 2004 04:15:21 >-0400 (EDT) >Received: from mc4-f27.hotmail.com ([65.54.190.163]) by mc4-s14.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Fri, 30 Apr 2004 01:26:11 -0700 >Received: from ace.iiug.org ([216.177.38.212]) by mc4-f27.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6824); Fri, 30 Apr 2004 01:24:04 -0700 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i3U8Pxqb021046;Fri, 30 Apr 2004 04:26:02 >-0400 (EDT) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i3U8FL90020682;Fri, 30 Apr 2004 04:15:21 -0400 (EDT) >X-Message-Info: 0jbW5ANosZLHz6xq4zKnSDJI9khPjQXt >Message-Id: <200404300815.i3U8FL90020682@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 30 Apr 2004 08:24:04.0895 (UTC) >FILETIME=[7FE0BAF0:01C42E8C] > >Hi, > >that's the case when you're migrating the whole instance of the >server to another machine. > >It's also pretty accurate if you have just this one database in your >instance and you're moving that database only (assuming that the >size of your database is much bigger than the size of the system >databases, e.g. sysmaster and sysutils which usually add only >negligible overhead). > >However, if you have several databases in your server instance >and you're talking about migrating only one of them, then the numbers >of "onstat -d" are generally not of much help, because they comprise >the space used by all databases in the system. >The exception to that rule is when you have your (several) databases >separated into different dbspaces, so that each database occupies >it's own dbspace. In that case you can select the appropriate numbers >(i.e. chunks of the respective dbspace(s)) from "onstat -d" output to >do the calculation. >If you have several databases that are not separated in different >dbspaces, then you probably want to use some "oncheck" command >to get the desired numbers. Look at the oncheck options and the output >produced by them to find the format that suites your needs best. Alas, >as far as I know there's nothing that gives a single, straight number. >I'd probably utilize "oncheck -pe" and send this output through a some >"sed"/"awk"/"grep"/"bc" scripts, but that's "a matter of (UNIX) taste" ... > >Regards, >Martin >-- >Martin Fuerderer >IBM Informix Development Munich >Data Management Solutions > >forum.subscriber@iiug.org wrote on 29.04.2004 17:55:22: > > > Quick easy question ... I presume.... > > > > We are looking to migrate a database from one server to another - what >is the easiest way to determine the size of the current database? Is it as >simple as 'onstat -d', adding up the number of pages for each chunk & >multiply by the page size? > > > > Thanks in advance > > > > _________________________________________________________________ Check out the coupons and bargains on MSN Offers! http://youroffers.msn.com

Hi, may be this script can help you select sum((c.chksize - c.nfree)*4) nofreepagesKB from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and name in ('rootdbs', 'ol_economicas1', ...dbspaces names) Paola Mensaje citado por "Martin Fuer...." <MARTINFU@de.ibm.com>: > Hi, > > that's the case when you're migrating the whole instance of the > server to another machine. > > It's also pretty accurate if you have just this one database in your > instance and you're moving that database only (assuming that the > size of your database is much bigger than the size of the system > databases, e.g. sysmaster and sysutils which usually add only > negligible overhead). > > However, if you have several databases in your server instance > and you're talking about migrating only one of them, then the numbers > of "onstat -d" are generally not of much help, because they comprise > the space used by all databases in the system. > The exception to that rule is when you have your (several) databases > separated into different dbspaces, so that each database occupies > it's own dbspace. In that case you can select the appropriate numbers > (i.e. chunks of the respective dbspace(s)) from "onstat -d" output to > do the calculation. > If you have several databases that are not separated in different > dbspaces, then you probably want to use some "oncheck" command > to get the desired numbers. Look at the oncheck options and the output > produced by them to find the format that suites your needs best. Alas, > as far as I know there's nothing that gives a single, straight number. > I'd probably utilize "oncheck -pe" and send this output through a some > "sed"/"awk"/"grep"/"bc" scripts, but that's "a matter of (UNIX) taste" ... > > Regards, > Martin > -- > Martin Fuerderer > IBM Informix Development Munich > Data Management Solutions > > forum.subscriber@iiug.org wrote on 29.04.2004 17:55:22: > > > Quick easy question ... I presume.... > > > > We are looking to migrate a database from one server to another - what > is the easiest way to determine the size of the current database? Is it as > simple as 'onstat -d', adding up the number of pages for each chunk & > multiply by the page size? > > > > Thanks in advance > > > > > >