Re: Size of database (was: Re: DB Tuning...)

Great answer !!! I would like to add the size of a complete instance (an easy one), just make a level 0 backup and check the size (easier if it is done in a file in the disk). Regards ----- Original Message ----- From: "Claus Samuelsen" <csa@eye-bee-em.com> To: <informix-list@iiug.org> Sent: Monday, August 02, 2004 1:26 PM Subject: Size of database (was: Re: DB Tuning...) > McCabe-Reed, Barbara SIK wrote: > > Can someone please tell me how to acquire the size of an Informix database? > > The database version is 7.3 and the AIX operating system is 4.3. Thanx. > > > Hmm, this can be answered in several ways, and the question have been > asked many times over the years. > The answer depends on what sizes you're asking for. Here's a couple of a > little more specific questions: > What is the size in KB that the database occupies on disk in quiescent mode? > What is the size of the database when exported in ascii (with dbexport)? > What is the size of raw data? > > Here's some answers. > > What is the size of raw data in KB? > Raw data is the data as defined by the table schemas. No indexes, no > temporary data, no meta data of any kind. The answer is: > > select round(sum(rowsize * nrows)/1024,0) as size_in_kb > from systables > where tabid > 99; > > The number returned would also be a good guess to the question: > What is the size of the database when exported in ascii (with dbexport)? > Usually an ascii export will be less than the raw data size, especially > if the tables contain many char columns. On the other hand, if the > database contains blobs that are exported as hex ascii, then the > exported data can much larger. > > What is the size in KB that the database occupies on disk in quiescent mode? > The purpose of this 'silly' question is to stress that the size of the > database my vary when in use. For example, a query that joins several > tables and has an 'order by' may create temporary tables or sort files > that doubles the total size while running. > An easier question would be: > How many pages does the database occupy? > > select sum(size) as size_in_pages > from sysmaster:sysextents > where dbsname = 'database_name'; > > This is argueable, because the value reflects the number of allocated > pages, not the number of pages filled with data and indexes. Suppose you > have a 'next extent' value of 100,000 and a new extent has just been added. > > To avoid this problem, you can use: > > select sum(p.nptotal) as total_pages, > sum(p.npused) as pages_in_use, > sum(p.npdata) as data_pages > from sysmaster:sysdatabases d, sysmaster:sysptnhdr p > where trunc(d.partnum/1000000, 0) = trunc(p.partnum/1000000, 0) > and d.name = 'database_name'; > > > Of course, if you just wanna brag about the size of yours, then just > calculate the total size of allocated disk space: > > select sum(chksize) as total_number_of_pages > from sysmaster:syschunks; > > > NB! Before running any of the above examples, be sure to run update > statistics. > > PS. The sysmaster tables may vary from version to version of IDS, > especially undocumented tables like sysptnhdr. Take a look in > INFORMIXDIR/etc/sysmaster.sql for a description of your specific version. > sending to informix-list