tables in dbspace
Folks, Might someone suggest a way to derive a list of all tables that exist in each dbspace? Thank you for any comments. David Grove Dept. Health & Social Svcs. State of Alaska
David Grove wrote:
>
> Folks,
>
> Might someone suggest a way to derive a list of all tables that exist in
> each dbspace?
>
> Thank you for any comments.
>
> David Grove
> Dept. Health & Social Svcs.
> State of Alaska
David,
The following works for XPS 8.21, but I think you might be able to modify it for
7.x. You don't have a syscmdbspaces table but I think instead it's just a
sysdbspaces table. This script was written for a UNIX environment, but I'm
sure it could be converted to Perl or something else for NT.
I think with the minor change you'll be on the air. Please let me know if
it doesn't work for 7.x with the change so I can create a separate one. I'm
being lazy and not trying it for 7.x myself. :-)
Tim
-
--
--- Tim Schaefer
---- tschaefe@mindspring.com
--- http://www.inxutil.com
--
-
#!/bin/sh
################################################################################
#
# XDBtree: Produces a report of tables in dbspaces
# For XPS 8.21 or greater
#
# Author: Tim Schaefer, Data Design Technologies, Inc.
# August 1998
# tschaefe@mindspring.com
#
################################################################################
get_db_info()
{
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 ) }
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 ;
lsize=$10;
}
' /tmp/systree.dat
}
################################################################################
>/tmp/systree.dat
get_db_info
produce_rpt
# >/tmp/systree.dat