Re: Urgent question about tables!

> 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.