Re: List tables per dbspace
Sue Athey wrote: > > I am trying to generate a list of the Informix tables per each dbspace. My > sysfragments table only contains the indexes. In which case a simple example would be: SELECT DBINFO("DBSPACE", partnum) dbspace, tabname FROM systables WHERE tabtype = "T" AND tabid > 99 ORDER BY 1,2 However, if you have fragmented tables, or need to identify indexes to move then you need to look into the sysfragments table. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+
In article <87vf46$54u$1@news.xmission.com>, "Mark D. Stock" <mdstock@mydas.freeserve.co.uk> wrote: > > Sue Athey wrote: > > > > I am trying to generate a list of the Informix tables per each > > dbspace. My sysfragments table only contains the indexes. > > In which case a simple example would be: > > SELECT DBINFO("DBSPACE", partnum) dbspace, tabname > FROM systables > WHERE tabtype = "T" > AND tabid > 99 > ORDER BY 1,2 > > However, if you have fragmented tables, or need to identify indexes to > move then you need to look into the sysfragments table. > > Cheers, And L'Chaim to you too, Mark. Sue, and whoever else cares, just yesterday, I submitted a new script to iiug, so it should show up in a few days. It is called partitions.sh and it replaces a script with the same name that I submitted (together with who-access) a few months ago. It displays some vital information about tables and all tblspaces associated with each table. This includes the partitions number (in hex) and the name of the dbspace. (Looks like you're not interested in the row & page counts). If a tblspace is an index fragment, it will also display the name of the index. The SQL that derives all the data is a UNION that handles sysfragments in the second query. One quirk: If an index that supports a constraint was named by the system (because the DBA didn't name it) the index name begins with a space. partitions.sh does not retain that leading space. When Walter posts it on the IIUG software archive, you can download it and, when you run it, pipe it through awk to get the columns and rows you want. I think this will serve your purpose, Sue. -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.