RE: How to get available tablespaces

Try: database sysmaster; select name[1,10] dbspace, -- name truncated to fit on one line sum(chksize)*2 KB_size, -- sum of all chuncks size pages sum(chksize)*2 - sum(nfree)*2 KB_used, sum(nfree)*2 KB_free, -- sum of all chunks free pages round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum group by 1 order by 1 into temp dbs; select * From dbs;select sum(KB_size) sum_KB_size, sum(KB_used) sum_KB_used, sum(KB_free) sum_KB_free from dbs But you will have to change the name[1,10] to fit your requirements And the *2 might be *4 - depending on the page size on your platform -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of inpreet Sent: 27 March 2006 01:43 PM To: informix-list@iiug.org Subject: How to get available tablespaces I am working on one assignment, which checks and provides total number of available tablespaces in informix. I don't need command line utilities. I need to fire query and get results. Facing some problem in getting exact results I fire query: 1. SELECT unique dbinfo("DBSPACE",partnum) dbspace FROM sysdatabases; It returns tablespaces created by informix administrator. 2. Select name, sum(nfree * 2/1024) from sysdbspaces d, syschunks c where d.dbsnum = c.dbsnum and (nfree * 2/1024) > 3000 group by 1 order by 1; It returns total tablespaces along with space available in them. My main motive is to get total number of tablespaces created by informix administrator and their free available disk space. Please help me how to get it in one go _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list > The information on this e-mail including any attachments relates to the official business of DigiCare (Pty) Ltd. The information is confidential and legally privileged and is intended solely for the addressee. Access to this e-mail by anyone else is unauthorised and as such any disclosure, copying, distribution or any action taken or omitted in reliance on it is unlawful. Please notify the sender immediately if it has inadvertently reached you and do not read, disclose or use the content in any way. > No responsibility whatsoever is accepted by DigiCare (Pty) Ltd if the information is, for whatever reason, corrupted or does not reach its intended destination. The views expressed in this e-mail are the views of the individual sender and should in no way be construed as the views of DigiCare (Pty) Ltd, except where the sender has specifically stated them to be the views of DigiCare (Pty) Ltd. >

Thanks alot, this is what I was looking for. One more query, it shows total number of dbspaces, including logical and temp dbspaces. Is there any issue if user tries to create database on these logical and temp dbspaces. Or is there any way to filter them from the list?