Kennedy, Randy wrote:
> I am trying to run a SQL against system tables that will show me which
> tables are in each dbspace and then correlate their indexes to see if
> the indexes are in the same dbspace. I know have some indexes in
> dbspaces that have tables in other dbspaces and want a query that will
> show me all of them at once to determine if they are where they should
> be or I need to move them.
>
> sysfragments shows me a indexname and dbspace, but I can't seem to see
> where the dbspace for the tables are listed (I can't find it in systables).
If you work with sysmaster:systabnames and sysmaster:sysptnhdr you can
compile information on tables, indexes and fragments of same from one
source. Meanwhile, the dbspace # is compiled into the partnum of each
object and can be extracted, there is a system function to provide the
dbspace name for a given partnum:
select dbinfo('dbspace', partnum)
from sysmaster:systabnames
where dbsname = ' mydatabase'
and tabname NOT MATCHES 'sys*';
Art S. Kagel