Re: Dbspaces and indexes

Mark D. Stock wrote: > > jlsmith269@my-dejanews.com wrote: > > > > Hello, > > > > Is it possible to determine what dbspace an index resides in? I know > > generally the index resides in the same dbspace as the table, but there are > > exceptions. There dont seem to be any utilities to tell you where the index > > resides. I am sure there is a query from the SMI that would give me the > > answer. Can anyone help me out. > > Not even from SMI, but your own database. Try something like: > > SELECT tabname, indexname, DBINFO("DBSPACE", partn) dbspace > FROM sysfragments f, systables t > WHERE f.tabid = t.tabid > AND fragtype = "I" > AND indexname = "<my index name>"> I'm wondering why in this particular case you are not using sysfragments.dbspace column. I suppose DBINFO("DBSPACE", partn) originated from some other query. By the way, on IDS 7.30.TC3 on NT sysfragments.partn is 0 for disabled indexes and therefore dbinfo() is not working. HTH Vardan > You don't need the join to systables of course, but it is useful if you > want to see which table the index was created on. > > 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"!|/ ////////| > +----------------------+-----------------------------------+-----------+ > -- Vardan Aroustamian vaar@geocities.com