Re: Determining what dbspaces tables are in.
Russell:
Try this:
#!/bin/ksh
#---------------------------------------------------------------------------
# where_are_tables
# written by CARLTON DOE for OnLine DSA
#
# the sql syntax for this germinated from some ideas LESTER KNUTSEN
# sent me.
#
# this will either print out the dbspace(s) a single table is in or a list
# of all dbspaces and the tables in them
#
# NOTE: if your system is using a page size other than 2 KB, change the
# first line of this script
#
# @(#)where_are_tables 1.2 15:37:18 08/06/96
#
# original modified by Steve Romankiw
#
#---------------------------------------------------------------------------
pagesize=2 # page size for your system
bitbucket="/tmp/where_are_tables.unl" # trash can for unload file
function list_tables
{
dbaccess sysmaster - > /dev/null 2>&1 <<!EOF
select 'Non-Fragmented' table_type, systabnames.tabname table_name,
dbinfo( "DBSPACE" , systabnames.partnum ) dbspace,
(nptotal * $pagesize) allocated_space,
((nptotal - sysptnhdr.npused) * $pagesize) free_space
from systabnames, "$db_name":systables, sysptnhdr
where "$db_name":systables.partnum = sysptnhdr.partnum and
"$db_name":systables.partnum = systabnames.partnum and
"$db_name":systables.tabname like "$tab_name" and
tabtype = "T"
group by 1,2,3,4,5
into temp x with no log;
{now get fragmentation info if the table is fragmented}
insert into x
select 'Fragmented' table_type, systabnames.tabname fragmented_table,
dbinfo( "DBSPACE" , systabnames.partnum ) dbspace,
sum(nptotal * $pagesize) allocated_space,
sum((nptotal - sysptnhdr.npused) * $pagesize) free_space
from systabnames, "$db_name":systables, sysptnhdr
where systabnames.tabname = "$db_name":systables.tabname and
systabnames.partnum = sysptnhdr.partnum and
"$db_name":systables.partnum = 0 and
"$db_name":systables.tabname like "$tab_name" and
tabtype = "T"
group by 1,2,3;
unload to $bitbucket
select table_name, dbspace, allocated_space, free_space, table_type
from x
order by 1, 2;
!EOF
}
## the main processing function
echo ""
echo "Finding tables. NOTE--I'm using a $pagesize KB page size"
echo ""
echo "Please enter the database name: \\c"
read db_name
db_name=${db_name:?"Missing database name"}
echo ""
echo "Enter the table name (w/ wildcards) or all [all]: \\c"
read tab_name
tab_name=${tab_name:=all}
if [[ $tab_name = "all" ]]; then
tab_name="%"
fi
list_tables
#--------------------------------------------------------------
# Record layout
#
# table_name|dbspace|allocated_space|free_space|table_type
#--------------------------------------------------------------
cat $bitbucket | awk 'BEGIN { FS = "|"
printf
"\\n-----------------------------------
-------------------------------------------"
printf
"\\ntable\\t\\t\\t\\t\\tallocated\\tfree\\ttable\\n"
printf
"name\\t\\t\\tdbspace\\t\\tspace\\t\\tspace\\ttype\\n"
printf
"-------------------------------------
-----------------------------------------\\n"
}
{
printf "%-18s\\t%-8s\\t%d\\t\\t%d\\t%s\\n",
$1,$2,$3,$4,$5
}
'
rm $bitbucket
[snip]
Russell Bierschbach wrote:
> Can anyone show me a command or query that will show me what tables are in
> what dbspaces.
>
> Along the same lines, what dbspace does Informix create tables in by
> default? The same as the database?
>
> I'm using Workgroup Server 7.2x, and Dynamic Server 7.2x