IDS Forum

RE: Tables in a dbspace : how to find

Posted By: Plugge, Joe R.
Date: Thursday, 3 December 2009, at 8:42 a.m.

In Response To: Tables in a dbspace : how to find (PRABAL DAS)

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("\nDBSPACE: %-15s #CHUNKS: %-3d SIZE(PAGES): %-7d\n",$1,$2,$3)

print("=================================================================================================")
print(" TABLE/INDEX DATABASE #EXTNS #ROWS #PAGES % IN DBSPACE")

print("=================================================================================================")
}

printf("%-35s %-20s %-3d %-8d %-8d %3.2f%\n",$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.

Messages In This Thread

• Tables in a dbspace : how to find
  PRABAL DAS -- Thursday, 3 December 2009, at 8:35 a.m.
  • RE: Tables in a dbspace : how to find
    Plugge, Joe R. -- Thursday, 3 December 2009, at 8:42 a.m.
    • RE: Tables in a dbspace : how to find
      PRABAL DAS -- Thursday, 3 December 2009, at 9:26 a.m.
      • RE: Tables in a dbspace : how to find
        Plugge, Joe R. -- Thursday, 3 December 2009, at 9:33 a.m.
        • Re:Tables in a dbspace : how to find
          PRABAL DAS -- Friday, 4 December 2009, at 9:30 a.m.
  • Re: Tables in a dbspace : how to find
    MIKE MAGIE -- Thursday, 3 December 2009, at 1:14 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.