Hi,

Hope some can help with this one. I have a very large database instance with over 500 tables. I would like to re-organise some of the logical volumes as the I/O contention is too high.
The question is does anyone know how to link a table to the actual disk or chunk that it resides on?
I can link the table to the DBSPACES but not to the actual physical location on the disk.
I am using the following SQL statement to map the tables to the dbspace.

database sysmaster;

select dbinfo( "DBSPACE" , pe_partnum ) dbspace,
dbsname[1,10],
tabname,
pe_phys start,
pe_size size
from sysptnext, outer systabnames
where pe_partnum = partnum
order by dbspace, start;


Any help with this matter will be greatly appreciated.

Thanks,

Akeel

Hi,

Please test with:

#!/bin/ksh
[ $# -ne 1 ] && echo "Usage: $0 <tabla>" && exit
arch=/tmp/tabinchk.tmp
arch1=/tmp/infchk.tmp
infdsk >$arch1
tab=$1
raya='--------------------------------------------------------------------------
-----'
buscadsk()
{
chk=$1
dsk=`cat $arch1|grep $chk|awk '{print $4}'`
}

dbaccess sysmaster 2>/dev/null <<EOF
set isolation to dirty read;
select unique dbsname[1,10] base,
fname[1,40] chunk,dbinfo("DBSPACE",b.partnum) dbspace
from systabextents a,systabnames b, syschktab c
where a.te_partnum = b.partnum
and b.tabname = '$tab'
and c.chknum = physchunk(a.te_physaddr)
into temp tabinchktmp with no log;
output to $arch without headings
select * from tabinchktmp
order by 1,2
EOF
if [ -s $arch ] ; then
a=0
echo $raya
printf "%-9s %-39s %-10s\t%10s\n" 'DataBase' 'Chunk(s)' 'Dbspaces' 'Disk'
echo $raya
for lin in `cat $arch|sed '/^$/d`
do
let a=a+1
case $a in
1) printf '%-9s ' $lin ;;
2) buscadsk $lin; printf '%-40s' $lin ;;
3) printf '%-12s\t%10s\n' $lin $dsk ;a=0;;
esac
done
echo $raya
fi
rm -r $arch $arch1

Gustavo.

Thanks for the response Gustavo.
That certainly works on a table by table option. I will edit that script so that it looks up all the tables from a file rather than specifying the file name at the prompt.
Also a quick question with regards to the function "infdsk". Reading through the script it looks like this is meant to output the actual disk information. I cannot seem to find this function and also it does not seem to output any information with regards to the disks.
But overall you have managed to answer my question.

Thanks,

Akeel

Hi Akeelm,

Excuse me, please, i attach infdsk.

#!/usr/bin/ksh
tabs -4
arch=/tmp/tmp$$
echo "## Dbspaces por Discos - `date +%d/%m/%Y` - Server: '`uname -n`' ##"
raya="--------------------------------------------------------------------------
-----"
#raya="-------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------------------------------------"
echo $raya
IFS="
"
total=0
echo "Dbspace \tLink Simbolico\t\t\tDevice\t\t\t\t\tDisk \t\tMb."
echo $raya
for i in `onstat -d|grep informix|grep -vE "[MP]O"|awk '{if($7!="T"&&$7!="B") pr
int $2,$8; else print $2,$9 }'`
do
dbs=`echo $i|awk '{print $1}'`
name=`echo $i|awk '{print $2}'`
[ ! "$nameant" ] && nameant=$name
for j in `onstat -d|grep -E "[PM]O"|awk -v d=$dbs '{if($3==d) print $7,$8}'`
do
mir=`echo $j|awk '{print $1}'`
nom=`echo $j|awk '{print $2}'`
alias=`ls -l $nom|awk -F "->" '{print $2}'|sed -e 's/\/r/\//' -e 's/ //'`
i=0
tot=0
for gg in `/etc/lvdisplay -v $alias|grep -v current|grep -E "dsk|LV Size"|sed
's/\/dev\/dsk\///'|awk '{print $0}'`
do
if [ `echo $gg|grep -c LV` -eq 1 ]; then
tdsk=`echo $gg|awk '{printf "%-d\n",$4}'`
continue
fi
disk=`echo $gg|awk '{print $1}'`
let total=total+tdsk
let i=i+1
[ "$nameant" != $name ] && echo $raya && nameant=$name
if [ $i -eq 1 ] ; then
if [ "$mir" = "MO-" ] ; then
echo $name $nom $alias $disk $tdsk|awk '{printf "%-10s %-23s %-28s %-1c %
-10s %-4d\n",$1,$2,$3,"M",$4,$5}'
else
echo $name $nom $alias $disk $tdsk|awk '{printf "%-10s %-23s %-20s %-10s
%-5d\n",$1,$2,$3,$4,$5}'
fi
else
echo $disk $tdsk|awk '{printf "\t\t\t\t\t\t\t\t\tdisk mirroring\t\t\t% -10s %
-4d\n",$1,$2}'
fi
rm -f $arch
done
[ "$nameant" != $name ] && echo $raya && nameant=$name && tot=0
done
done
echo $raya
echo $total|awk '{printf "Total\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t%7d\n",$ 1/2}'
echo $raya

Gustavo.

Thanks Gustavo,

I forgot to mention that the machine i am working with is an AIX machine 4.3.3. I do get the idea behind the script though and will be useful on my HP machines as well. I will adjust the script accordingly for AIX.

Thanks once again.

Akeel

Some advertising ...
If u want to re-organise the layout of data and indexes in ur IDS, we've developed a mature tool, named as DBOrg, to get the lousy job done easily. Functions are :
1. Online re-org without shutting down IDS
2. Re-org with table unit ( of course, analysis of table layout is shown with easy-to-read map )
3. You can schedule the easy time of server to re-org fragmented tables without working overtime
4. With hash comparison to assure the consistency of table after extents are condensed. Safety with no reisk !
5. If any, Indexes of that table will be balanced, too

To work efficiently is important !!!

Lans

MSN : lansrow_hsieh@hotmail.com