Re: Where are tables/indexes created ??

Pramod Dhumal wrote: > > Hi List users, > > Thank U for your previous responses to my queries on moving from DEC > 4000 to Alpha 4100. I have 2 queries : > > 1. In our database some tables are specifically defined as "create table > ....in dbspace...." whereas some there is no dbspace mentioned. Since I > have 4 dbspaces, I want to know which table is in which dbspace exactly > ?? If you don't specify a dbspace, the table will be created in the dbspace where the database resides. You can list the current locations with: SELECT tabname, DBINFO("DBSPACE", partnum) dbspace FROM systables WHERE tabtype = "T" AND tabid > 99 AND partnum > 0 This does not cater for fragmented tables. You would have to use the sysfragments table and partn column to find the location of each fragment. > 2. How do I get the size (KB) of all indexes used in my database? Not sure off the top of my head. > Is it possible to create indexes also in specific dbspaces ?? Yes, also using the 'IN dbspace' syntax. You could also fragment them by expression. Hope that helps, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 838250 2325 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+