Re: npused in fragmented tables

}>From ilist@rmy.emory.edu Fri Mar 14 00:35:16 1997 }>Received: from rmy.rmy.emory.edu by hobbes.dbintellect.com via ESMTP (940816.SGI.8.6.9/930416.SGI.AUTO) }> for <settler@dbintellect.com> id AAA10903; Fri, 14 Mar 1997 00:35:15 -0700 }>Received: (from ilist@localhost) by rmy.rmy.emory.edu (8.7.1/8.7.1) id BAA19138 for informix-list-out; Fri, 14 Mar 1997 01:00:01 -0500 (EST) }>From: Nils.Myklebust@idg.no (Nils Myklebust) }>Message-Id: <3328d9fc.67278671@gate.idg.no> }>Subject: Re: npused in fragmented tables }>Date: Fri, 14 Mar 1997 04:56:37 GMT }>Reply-To: Nils.Myklebust@idg.no }>Organization: NM Data AS }>Sender: informix-list-owner@rmy.emory.edu }>To: informix-list@rmy.emory.edu }>X-Informix-List-Id: <news.35207> }> }>And what version of Informix databases does this work on? }>It returns no rows found on both OnLine 7.10.UC1 on SCO Unix 3.2.4.2 }>and 7.12.UC1 on SCO Openserver 5.0.0d }>It would be a nice statement if it worked. Any ideas? }> }>settler@hobbes.dbintellect.com (Jeff Craig) wrote: }> }>:You could muck around $INFORMIXDIR/etc/sysmaster.sql to find out more }>:about the system table columns. Here is a sysmaster SQL statement }>:that I use to show allocated versus used pages in tables fragmented }>:across dbspaces. }>: }>:set isolation to dirty read; }>:select a.tabname as Table }>: ,c.name[1,10] as DBSpace }>: ,b.nptotal as PagesAllocated }>: ,b.npused as PagesUsed }>: ,(b.nptotal - b.npused) as PagesRemaining }>: ,trunc(((b.npused / b.nptotal) * 100)) as PctUsed }>: ,b.nrows as NbrRows }>: ,b.nextns as NbrExtents }>: ,b.fextsiz as FirstExtSize }>: ,b.nextsiz as NextExtSize }>: from sysptnhdr b }>: ,systabnames a }>: ,sysdbstab c }>:where c.dbsnum = trunc(b.partnum/1048576) }>: and b.partnum = a.partnum }>: and a.owner='dwadmin' }>:order by a.tabname }>: ,c.name }>:; }>: }>: }>:------------------------------------------------------------------- }>:Jeff Craig | dbINTELLECT Technologies, Inc. }>:Senior Consultant | Golden, CO }>: | (303) 275-6954 ext. 8008 }>: | email: settler@dbintellect.com }>: | fax: (303) 275-2134 }>:------------------------------------------------------------------- }> }>Nils.Myklebust@idg.no }>NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway }>My opinions are those of my company }>The Informix FAQ is at http://www.iiug.org }> Umm, maybe you should get rid of the OWNER='dwadmin' in the WHERE clause, as that is specific to my database. My apologies. This works on release 7.20.UC2 on a Silicon Graphics box. ------------------------------------------------------------------- Jeff Craig | dbINTELLECT Technologies, Inc. Senior Consultant | Golden, CO | (303) 275-6954 ext. 8008 | email: settler@dbintellect.com | fax: (303) 275-2134 -------------------------------------------------------------------