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.
select dbinfo( "DBSPACE" , pe_partnum ) dbspace,
from sysptnext, outer systabnames
where pe_partnum = partnum
order by dbspace, start;
Any help with this matter will be greatly appreciated.
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
if [ -s $arch ] ; then
printf "%-9s %-39s %-10s\t%10s\n" 'DataBase' 'Chunk(s)' 'Dbspaces' 'Disk'
for lin in `cat $arch|sed '/^$/d`
case $a in
1) printf '%-9s ' $lin ;;
2) buscadsk $lin; printf '%-40s' $lin ;;
3) printf '%-12s\t%10s\n' $lin $dsk ;a=0;;
rm -r $arch $arch1
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.
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.
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