Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    8

    Unanswered: link tables to actual chunks

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    8
    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

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    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.

  5. #5
    Join Date
    Nov 2004
    Posts
    8
    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

  6. #6
    Join Date
    Nov 2004
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •