Re: Urgent question about tables!

Oops, a couple of slight corrections. The "= 0" should read "= 1", as rootdbs is dbspace number one. The "partn > 0" is correct, though. Additionally, the comments about multiple chunks in the rootdbs do not apply. I was operating under the hallucination that chunk number was part of the tablespace identifier (partnum), but checking the documentation reveals it is dbspace number. Thus, regardless of how many chunks there may be in rootdbs, any table or fragment created within rootdbs will have a tablespace identifier with '1' as the first 12 bits. Given this, the WHERE clauses should include "where trunc(partn / 1048576) = 1" or "where trunc(partnum / 1048576) = 1". Sorry for the confusion. Mark Collins mcollins@us.dhl.com The problem lies in how easily and dangerously we forget that manipulating things is not the same as understanding them. ------------------------ From: Mark Collins <mcollins@us.dhl.com> Subject: Re: Urgent question about tables! Date: Tue, 17 Mar 1998 12:53:12 +0000 To: informix-list@iiug.org, Nils van der Laan <nilsl@simac.nl> > > I have an urgent question about the location of tables. > > > > The application we use (BaanIV) was incorectly installed. The person who did > > this, created a database called baanivc. He placed this database in de the > > dbspace rootdbs and then extended is with chunks in other dbspaces. Now our > > rootdbs (500Mb!) is full!. I would like to run a query that gives me the > > following information: > > > > Which table from the database baanivc is stored in the dbspace rootdbs. > > > > Is this possible. If yes: how? > > Very possible; actually fairly easy. Try the following query: > select partn, owner, tabname > from systables > where tabid > 99 > and trunc(partn / 1048576) = 0;> > The "tabid > 99" ignores any system tables, and the "trunc (partn / 1048576) = 0" finds only > those tables created in chunk 0 - which is where your rootdbs is. If there are multiple > chunks in your rootdbs, you will need to change the "= 0" to be "in (0, x, y, z)". > > I've not worked with Baan, so I don't know if they take advantage of Informix's > fragmentation feature. If they do, any fragmented tables will have a partnum = 0 in > systables, and trunc(0 / 1048576) = 0. To get around this you have to query the > sysfragments table, which contains an entry for each table fragment, each index fragment, > and each non-fragmented detached index. An all-inclusive query might end up like: > > select partnum, owner, tabname, "table" > from systables > where tabid > 99 > and trunc(partnum / 1048576) = 0 > and partnum > 0 > union > select partn, owner, tabname, "table" > from sysfragments f, > systables t > where f.tabid = t.tabid > and f.fragtype = "T" > and partn > 0 > and trunc(partn / 1048576) = 0 > union > select partn, owner, indexname, "index" > from sysfragments f, > sysindexes i > where f.fragtype = "I" > and i.idxname = f.indexname > and partn > 0 > and trunc(partn / 1048576) = 0 > order by 4, 3, 1;> > > > > > Mark Collins > mcollins@us.dhl.com > > Dilbert is a documentary. > ---------------End of Original Message-----------------