Tables in a dbspace : how to find
User on IDS 7.31 (Solaris) asked how to list which tables live in a given dbspace, so full dbspaces could be identified and non-critical tables purged. Joe Plugge posted a community ksh script (tab2dbsp.sh by Prasad Mahale) that queries sysmaster (systabnames/systabinfo joined with sysdbspaces/syschunks) and reports tables with extents, rows, pages and percentage of dbspace used, noting page size (2K vs 4K) may need adjusting. Mike Magie suggested 'oncheck -pe' as an alternative. The poster confirmed the script worked after a small awk tweak.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, How can I find the list of tables that are residing in a particular dbspace so that if the dbspace becomes full I can identify and choose only those tables that can be purged (ie, not any application critical config/master table). Informix V7.31 ud8/ Sun 5.7 Thanks in advance Prabal
This is a community provided program that works well to identify used pages
within a dbspace.
Usage:
tab2dbsp.sh mydbspace > mysbspace.out
Here is the script
#!/bin/ksh
#
# $Id: tab2dbsp.sh,v 1.1 2004/11/30 18:28:30 informix Exp $
# $Author: informix $
# $Date: 2004/11/30 18:28:30 $
# $Revision: 1.1 $
#02. Table Vs Dbspace Information
#This scripts shows list of Dbspaces (with #chunks and size) and list of
#tables
#(with #rows, #extents and size) residing in that dbspace. This also shows
#the
#percentage of space occupied by that table in dbspace. Optional Input
#parameter
#is <dbspace_name>. If <dbspace_name> is not specified, it will list all the
#dbspaces.
#Run as - $ tab2dbsp.sh [<dbspace_name>]
# Utility to Print list of tables in a dbspace
# Written By - Prasad Mahale
# Date - 05/11/1999
# Usage - tab2dbsp.sh [<dbspace name>]
# Friends, This utility may not be 100% correct. Feel free to modify this
according to your requirement.
# If you find anything wrong in the logic of script or you have better version
# of this utility or just even to send comments or suggestions, mail me at
# pmahale@yahoo.com.
# For sample output of this script, visit www.geocities.com/pmahale/inf/tools
#############################################################################
if [ ! "$1" ]
then
dbspace_name="*"
else
dbspace_name=$1
fi
dbaccess sysmaster 2> /dev/null << +
unload to tab2dbsp.out
select trunc(a.partnum/1048576) dbsnum, a.dbsname, a.tabname,
b.ti_nextns, b.ti_npused, b.ti_nrows
from systabnames a, systabinfo b
where a.partnum = b.ti_partnum;
create temp table t_tab2dbsp1 ( dbsnum integer, dbsname char(20),tabname char(35), nextns smallint, npused integer, nrows integer);
load from tab2dbsp.out insert into t_tab2dbsp1;
unload to tab2dbsp.out
select a.dbsnum, a.name, count(*), sum(b.chksize)
from sysdbspaces a, syschunks b
where a.dbsnum = b.dbsnum
and name matches "$dbspace_name"
and name not in ("dbslog","dbsphys")
and is_temp = 0
group by 1,2;
create temp table t_tab2dbsp2 ( dbsnum integer, dbspname char(20),nchunks smallint, dbssiz integer);
load from tab2dbsp.out insert into t_tab2dbsp2;
unload to tab2dbsp.out
select a.dbspname, a.nchunks, a.dbssiz,
b.dbsname, b.tabname, b.nextns, b.nrows, b.npused
from t_tab2dbsp2 a, t_tab2dbsp1 b
where a.dbsnum = b.dbsnum
order by 1,8 desc;
drop table t_tab2dbsp1;+
echo
cat tab2dbsp.out | awk -F "|" '{
if ( t_break != $1 )
{ printf("\\
DBSPACE: %-15s #CHUNKS: %-3d SIZE(PAGES): %-7d\\
",$1,$2,$3)
print("=========================================================================
========================")
print(" TABLE/INDEX DATABASE #EXTNS #ROWS #PAGES % IN DBSPACE")
print("=========================================================================
========================")
}
printf("%-35s %-20s %-3d %-8d %-8d %3.2f%\\
",$5,$4,$6,$7,$8,$8/$3*100)
t_break=$1
}'
rm tab2dbsp.out
#*END OF SCRIPT*
#SAMPLE OUTPUT -
#=============
#DBSPACE: hcdmo7db #CHUNKS: 1 SIZE(PAGES): 80000
#===================================================================
#TABLE/INDEX DATABASE #EXTNS #ROWS #PAGES % IN DBSPACE
#===================================================================
#pspnlfield hcdmo7 1 65011 11114 13.89%
#TBLSpace hcdmo7db 37 0 3250 4.06%
#sysdistrib hcdmo7 3 31116 2504 3.13%
#psprojectitem hcdmo7 4 55712 2424 3.03%
#psrecfield hcdmo7 1 58462 2340 2.93%
#pspcmprog hcdmo7 1 14673 2307 2.88%
#pspcmname hcdmo7 1 84893 1393 1.74%
#psauthitem hcdmo7 1 35756 1234 1.54%
#ps_pay_check hcdmo7 2 7385 1232 1.54%
#sysconstraints hcdmo7 52 31907 1113 1.39%
#sysobjstate hcdmo7 52 36657 1107 1.38%
#ps_local_tax_tb hcdmo7 1 21858 1094 1.37%
#ps_pay_earnings hcdmo7 2 13943 931 1.16%
#psactivitymap hcdmo7 1 713 891 1.11%
#ps_pay_deductio hcdmo7 2 45710 776 0.97%
#syscolumns hcdmo7 43 53583 732 0.92%
#===============================================================================
============
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PRABAL
DAS
Sent: Thursday, December 03, 2009 7:35 AM
To: ids@iiug.org
Subject: Tables in a dbspace : how to find [18255]
Hi,
How can I find the list of tables that are residing in a particular dbspace
so that if the dbspace becomes full I can identify and choose only those
tables
that can be purged (ie, not any application critical config/master table).
Informix V7.31 ud8/ Sun 5.7
Thanks in advance
Prabal
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
The script is giving some minor error but I think I can rectify those and execute it properly. Thanks again
I can't remember if you have to adjust within the script if your page size is 2K versus 4K ... (mine is 4K) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of PRABAL DAS Sent: Thursday, December 03, 2009 8:26 AM To: ids@iiug.org Subject: RE: Tables in a dbspace : how to find [18257] The script is giving some minor error but I think I can rectify those and execute it properly. Thanks again ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
oncheck -pe works pretty well also...
Yes its working fine, just after a little modification in awk. thanks