Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Location
    pretoria, south africa
    Posts
    7

    Unanswered: table greater than 2 gb

    I want to identify tables that are more than 2 gb in size to be excluded by the unload scripts.Please advise on what statement to execute

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,
    Please test with:

    [ $# -ne 2 ] && echo "Modo de uso: `basename $0` <databasename> <tablename>" &&
    exit
    [ `uname -s` = "HP-UX" ] && bk=512 || bk=256
    raya="\n------------------------------------------------------------------------
    ------\n"
    raya2="----------------------------------------------------------------------"
    raya1="\n========================================= ==============================
    =======\n"
    raya3="=========================================== ==========================="
    raya4="=========================================== ===========================\n"
    arch=frgtmp$$

    ## Cheque existencia de base y tabla ingresadas por parametro ##
    base=$1
    tab=$2
    nn=`{ echo "select count(*) xx from sysdatabases \
    where name = '$base';"|dbaccess sysmaster 2>/dev/null; }|sed 's/xx//g'`
    [ $nn -eq 0 ] && echo "La Base '$base' no existe en '$INFORMIXSERVER'." && exit
    nn=`{ echo "select count(*) xx from systables \
    where tabname = '$tab';"|dbaccess $base 2>/dev/null; }|sed 's/xx//g'`
    [ $nn -eq 0 ] && echo "La tabla '$tab' en base '$base' no existe" && exit
    zz=`{ echo "select tabid,flags xx from systables \
    where tabname = '$tab';"|dbaccess $base 2>/dev/null; }|sed -e 's/xx//g' -e 's/ta
    bid//'`
    nn=`echo $zz|awk '{print $1}'`
    tip=`echo $zz|awk '{print $2}'`
    [ "$tip" = "8" ] && tipo="RAW" || tipo="STD"
    frg=`{ echo "select count(*) xx from sysfragments \
    where tabid = $nn \
    and fragtype = 'T';"|dbaccess $base 2>/dev/null; }|sed '/xx/d'`
    [ $frg -ne 0 ] && frag="Fragmentada" && tabid=$nn || frag="" || tabid=0

    ### MAIN ###
    if [ $frg -gt 0 ] ; then ## Tablas fragmentadas ##
    dbaccess sysmaster 1>$arch 2>/dev/null <<EOF
    select substr(dbinfo("DBSPACE",ti_partnum),1,10) dbspace,
    round(ti_npused/$bk,0) datos,0 indices,
    ti_nrows rows, ti_nextns extents
    from systabinfo
    where ti_partnum in (select partn from $base:sysfragments
    where tabid = $tabid
    and fragtype = 'T')
    into temp tabtemp with no log;
    select substr(dbinfo("DBSPACE",ti_partnum),1,10) dbspace,0 datos,
    round(ti_npused/$bk,0) indices,
    ti_nrows rows, ti_nextns extents
    from systabinfo
    where ti_partnum in (select partn from $base:sysfragments
    where tabid = $tabid
    and fragtype = 'I')
    into temp idxtemp with no log;
    select *
    from tabtemp
    order by 1;
    select * from idxtemp
    order by 1
    EOF
    echo $raya3
    echo "Base: $base - Tabla: $tab - $frag -\t- Datos en Mbytes - Tipo: $tipo"
    echo $raya2
    echo "Dbspace\t\tDatos\tIndices\tTotal\tFilas\t Extents"
    echo $raya2
    cat $arch|sed -e '/dbs/d' -e '/^$/d' | \
    awk -v r=$raya4 ' BEGIN { {D=0} {I=0} {T=0} {R=0} {E=0} }
    {D+=$2} {I+=$3} {R+=$4} {T+=($2+$3)} {if($2>0) {E+=$5}}
    {printf "%-12s\t%0d\t%0d\t%0d\t%0d\t\t%d\n",$1,$2,$3,$2+$3,$4 ,$5}
    END {printf "%sTotal\t\t%d\t%d\t%d\t%d\t%d\n%s",r,D,I,T,R,E,r} '
    rm -f $arch
    else ## Tablas NO fragmentadas ##
    {
    dbaccess sysmaster 2>/dev/null <<EOF
    select substr(s.name,1,10) expres,-- trim(n.dbsname) expres,
    round(sum(ti_nptotal)/$bk,0) expres,
    round(sum(ti_npdata)/$bk,0) expres,
    round((sum(ti_npused)/$bk) - (sum(ti_npdata)/$bk),0) expres,
    i.ti_nrows expres,
    i.ti_nextns expres
    from systabinfo i, sysdbspaces s, $base:systables x
    where x.tabname = '$tab'
    and x.partnum = i.ti_partnum
    and partdbsnum(i.ti_partnum) = s.dbsnum
    group by 1,5,6
    EOF
    } |sed -e 's/expres//' -e '/^$/d'| tr -d "\n" | \
    awk -v m=$tipo -v r1=$raya1 -v r=$raya -v t=$tab -v b=$base \
    '{printf "%sBase: %s - Tabla: %s \t- Datos en Mbytes - Tipo: %s %sDbspace\t\tDat
    os\tIndices\tTotal\tFilas\t\tExtents%s%-12s\t%d\t%d\t%d\t%d\t\t%d%s",r1,b,t,m,r,
    r,$1,$3,$4,$2,$5,$6,r1}'
    fi
    ### END MAIN ###

Posting Permissions

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