Re: Tables in dbspaces
SteveS wrote:
>
> Is there a way to list all of the tables that are in a dbspace?
>
> Thanks in advance.
>
> Steve
Steve,
This is one I use for XPS. Your challenge should you decide to accept
is to remove the dbslice layer of the problem, or simply use it with XPS
as it is. I know your intent is probably for 7.x, but I present this
not just for you, but for others out there who may be using XPS. To be
sure, this solution serves only a minority of you out there. But the
future is coming, especially now with our new friends from RedBrick.
XPS works at one extra layer beyond that which exists for the 7.x engine.
DBslices are logical groupings of dbspaces across nodes.
Dbslice
+
+-dbspace
+-dbspace
+-table
+-table
+-dbspace
Dbslice
+
+-dbspace
+-table
+-table
+-dbspace
+-table
+-table
+-dbspace
+-dbspace
I would challenge Informix to show table level information like this in the
IECC for XPS. Many of you out there don't realize this, but there are no
less than 3 IECC programs, probably more. One for 7.x, one for XPS that
points to UNIX, and one for XPS that works only with NT. :-)
The code presented would allow a DBA the total picture, not stopping like it
currently does at the dbspace. Some of the most important priorities a DBA has
are in understanding where things are, how much space is available, and how much
space is used. Currently only slices and spaces are shown in the IECC, but
table information is also necessary, as this post originally pointed out.
Thanks,
Tim
# BEGIN
#!/bin/sh
################################################################################
# begin doc
#
# Program: XDBtree
#
# Author: Tim Schaefer
# Data Design Technologies, Inc.
# www.datad.com
#
# Login: tschaefe@mindspring.com
#
# Created: May 1998
#
# Description: XDBtree reports on tables in dbspaces.
# The report is based on your ONCONFIG setting.
#
# Usage: XDBtree
#
# end doc
################################################################################
# sysdbslices
#
# Column name Type Nulls
# dbslice_num smallint yes
# name char(18) yes
# ndbspaces smallint yes
# is_rootslice integer yes
# is_mirrored integer yes
# is_blobslice integer yes
# is_temp integer yes
#
################################################################################
# syscmdbspaces
#
# Column name Type Nulls
#
# dbsnum smallint yes
# name char(18) yes
# fchunk smallint yes
# nchunks smallint yes
# home_cosvr smallint yes
# current_cosvr smallint yes
# dbslice_num smallint yes
# dbslice_ordinal smallint yes
# is_root integer yes
# is_mirrored integer yes
# is_blobspace integer yes
# is_temp integer yes
#
################################################################################
get_db_info()
{
date
dbaccess sysmaster 2>/dev/null <<+
set isolation to dirty read;
unload to /tmp/systree.datselect
sysdbslices.name,
syscmdbspaces.name,
syscmdbspaces.dbslice_num,
syscmdbspaces.dbsnum,
syscmdbspaces.fchunk,
sysextents.dbsname ,
sysextents.tabname ,
sysextents.start_chunk ,
sysextents.start_offset ,
sysextents.size
from syscmdbspaces, sysdbslices, sysextents
where sysdbslices.dbslice_num = syscmdbspaces.dbslice_num
and sysextents.start_chunk = syscmdbspaces.fchunk
order by
sysdbslices.name,
syscmdbspaces.name,
sysextents.start_chunk,
sysextents.start_offset,
sysextents.tabname
+
}
################################################################################
produce_rpt()
{
awk -F"|" ' BEGIN {
dbslice_name="" ;
dbspace_name="" ;
dbslice_num="" ;
dbspace_num="" ;
fchunk="" ;
dbsname="" ;
tabname="" ;
start_chunk="" ;
start_offset="" ;
size="" ;
ldbslice_name="" ;
ldbspace_name="" ;
ldbslice_num="" ;
ldbspace_num="" ;
lfchunk="" ;
ldbsname="" ;
ltabname="" ;
lstart_chunk="" ;
lstart_offset="" ;
lsize="" ;
size_cntr=0 ;
}
{
dbslice_name=$1 ;
dbspace_name=$2 ;
dbslice_num=$3 ;
dbspace_num=$4 ;
fchunk=$5 ;
dbsname=$6 ;
tabname=$7 ;
start_chunk=$8 ;
start_offset=$9 ;
size=$10 ;
{ if ( tabname == "TBLSpace" ) { { tabname = "" } } }
{ if ( ldbslice_num == dbslice_num ) { { dbslice_num = "" } } }
{ if ( ldbslice_name == dbslice_name ) { { dbslice_name = "" } } }
{ if ( ldbspace_num == dbspace_num ) { { dbspace_num = "" } } }
{ if ( lstart_chunk == start_chunk ) { { start_chunk = "" } } }
{ if ( ldbspace_name == dbspace_name ) { { dbspace_name = "" } } }
{ if ( dbspace_name == dbsname ) { { dbsname = "" } } }
{ if ( ldbsname == dbsname ) { { dbname = "" } } }
{ if ( ltabname == tabname ) { { tabame = "" } } }
{ printf( "%3s %-18s %3s %3s %-18s %-18s %-18s %10s %10s\\n", dbslice_num, dbslice_name, dbspace_num, start_chunk, dbspace_name, dbsname, tabname, start_offset, size ) }
# { printf( "%3s %-18s %3s %3s %s %s %-20s %10s %10s\\n", dbslice_num, dbslice_name, dbspace_num, start_chunk, dbspace_name, dbsname, tabname, start_offset, size ) }
last_chk=$1 ;
ldbslice_name=$1 ;
ldbspace_name=$2 ;
ldbslice_num=$3 ;
ldbspace_num=$4 ;
lfchunk=$5 ;
ldbsname=$6 ;
ltabname=$7 ;
lstart_chunk=$8 ;
lstart_offset=$9 ;