RE: DB Tuning...

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. -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org]On Behalf Of vavavoom_th14@yahoo.co.uk Sent: Monday, August 02, 2004 8:36 AM To: informix-list@iiug.org Subject: Re: DB Tuning... Hi Art , Can I recommend that the explinations you have put for BTR and RA be put into the FAQ at the IIUG org. The metrices are a great help to all Informix users and the more people that know about the better. Cheers Traveller2003 sending to informix-list

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.

Sorry, forgot to tell that page size is 4 KB on AIX (and windows), most others are 2 KB. select (bufsize / 1024) as page_size_in_kb from sysmaster:sysshmhdr;