#!/bin/sh
# This is ratios.shr, a shell archive (produced by GNU sharutils 4.15.2).
# To extract the files from this archive, save it to some FILE, remove
# everything before the '#!/bin/sh' line above, then type 'sh FILE'.
#
lock_dir=_sh127809
# Made on 2020-11-26 08:05 EST by <art.kagel@gmail.com>.
# Source directory was '/home/art/GoogleDrive/iiug/ratios.shr_ak'.
#
# Existing files WILL be overwritten.
#
# This shar contains:
# length mode       name
# ------ ---------- ------------------------------------------
#   3136 -rw-rw-r-- README.ratios.1st
#   4869 -rw-rw-r-- ratios.9.40.sql
#   6883 -rw-rw-r-- ratios_1110.sql
#   8837 -rw-rw-r-- ratios.1150.sql
#   8625 -rw-rw-r-- ratios.sql
#   1354 -rw-rw-r-- ratios.7.31.ksh
#   2746 -rw-rw-r-- newratios.ksh
#
MD5SUM=${MD5SUM-md5sum}
f=`${MD5SUM} --version | egrep '^md5sum .*(core|text)utils'`
test -n "${f}" && md5check=true || md5check=false
${md5check} || \
  echo 'Note: not verifying md5sums.  Consider installing GNU coreutils.'
if test "X$1" = "X-c"
then keep_file=''
else keep_file=true
fi
echo=echo
save_IFS="${IFS}"
IFS="${IFS}:"
gettext_dir=
locale_dir=
set_echo=false

for dir in $PATH
do
  if test -f $dir/gettext \
     && ($dir/gettext --version >/dev/null 2>&1)
  then
    case `$dir/gettext --version 2>&1 | sed 1q` in
      *GNU*) gettext_dir=$dir
      set_echo=true
      break ;;
    esac
  fi
done

if ${set_echo}
then
  set_echo=false
  for dir in $PATH
  do
    if test -f $dir/shar \
       && ($dir/shar --print-text-domain-dir >/dev/null 2>&1)
    then
      locale_dir=`$dir/shar --print-text-domain-dir`
      set_echo=true
      break
    fi
  done

  if ${set_echo}
  then
    TEXTDOMAINDIR=$locale_dir
    export TEXTDOMAINDIR
    TEXTDOMAIN=sharutils
    export TEXTDOMAIN
    echo="$gettext_dir/gettext -s"
  fi
fi
IFS="$save_IFS"
if (echo "testing\c"; echo 1,2,3) | grep c >/dev/null
then if (echo -n test; echo 1,2,3) | grep n >/dev/null
     then shar_n= shar_c='
'
     else shar_n=-n shar_c= ; fi
else shar_n= shar_c='\c' ; fi
f=shar-touch.$$
st1=200112312359.59
st2=123123592001.59
st2tr=123123592001.5 # old SysV 14-char limit
st3=1231235901

if   touch -am -t ${st1} ${f} >/dev/null 2>&1 && \
     test ! -f ${st1} && test -f ${f}; then
  shar_touch='touch -am -t $1$2$3$4$5$6.$7 "$8"'

elif touch -am ${st2} ${f} >/dev/null 2>&1 && \
     test ! -f ${st2} && test ! -f ${st2tr} && test -f ${f}; then
  shar_touch='touch -am $3$4$5$6$1$2.$7 "$8"'

elif touch -am ${st3} ${f} >/dev/null 2>&1 && \
     test ! -f ${st3} && test -f ${f}; then
  shar_touch='touch -am $3$4$5$6$2 "$8"'

else
  shar_touch=:
  echo
  ${echo} 'WARNING: not restoring timestamps.  Consider getting and
installing GNU '\''touch'\'', distributed in GNU coreutils...'
  echo
fi
rm -f ${st1} ${st2} ${st2tr} ${st3} ${f}
#
if test ! -d ${lock_dir} ; then :
else ${echo} "lock directory ${lock_dir} exists"
     exit 1
fi
if mkdir ${lock_dir}
then ${echo} "x - created lock directory ${lock_dir}."
else ${echo} "x - failed to create lock directory ${lock_dir}."
     exit 1
fi
# ============= README.ratios.1st ==============
${echo} "x - extracting README.ratios.1st (text)"
  sed 's/^X//' << 'SHAR_EOF' > 'README.ratios.1st' &&
XPackage: ratios.shr
XAuthor: Art S. Kagel
XContact: art.kagel@gmail.com
XCreated: December 29, 2009
XLatest release: August 3, 2016
X
XCopyright 2009-2016 by Art S. Kagel. Permission for all reasonable use is
Xgranted without restriction.
X
XThe report calculates the basic server performance metrics which I have
Xdeveloped over the years.  The interpretation of those metrics is documented in
Xmany CDI and IIUG Forum postings as well as in the Informix FAQ.  This package
Xis a rewrite of the package 'ratios' published by David Kleppinger
X<davek@dtsbiz>.  That package interpreted onstat -p and ONCONFIG file data and
Xso did not port well to new releases of IDS.  This version, by embedding the
Xdata gathering in a stored procedure, is forward compatible beginning with IDS
X9.40 and adds the ability to run the procedure manually for a quick report.  It
Xalso adds calculation of the metrics for the individual buffer caches where a
Xserver has more than one page size cache.
X
XThis package includes three SQL scripts (ratios.sql, ratios_1110.sql, &
Xratios_pre10.9.40.sql) to create a stored procedure in the sysmaster (SMI)
Xdatabase appropriate for different versions of Informix and two ksh scripts
X(newratios.ksh & ratios.7.31.ksh). Newratios.ksh can execute the procedure and
Xformat a pretty report for engine versions 9.40 and newer. The ratios.7.31.ksh
Xis a direct update to David Kleppinger's original script that interprets onstat
X-p output because v7.31 didn't report everthing needed for the calculations in
Xsysmaster.
X
XThe latest releases add two new metrics, BTR2 & BTR3, which are an attempt to
Xget a better handle on buffer cache thrashing and sizing.  These are basically
Xattempts to get a more accurate buffer turnover calculation for certain systems
Xwhere I found the BTR was not indicative of performance. BTR2 can be ignored
Xand I may drop it eventually. BTR3 reports nearly identical values to the
Xoriginal BTR except on those systems that are insert intensive. On such systems
XBTR apparently over reports buffer activity and incorrectly predicts poor
Xperformance. On those systems with high insert rates the BTR3 reports values
Xthat more accurately reflect the perceived performance of the server and should
Xbe relied on. Ultimately BTR3 should replace the original BTR calculation.
X
XInstallation is simple:
X
X1) Install the appropriate stored procedure in each server's SMI database
X   (sysmaster):
X   dbaccess sysmaster - <ratios.sql
X
X   If you have an older version of the procedure installed, this will replace
X   the older procedure.
X
X   If you are running IDS version 10.00 or 11.10 you will need to run the
X   ratios_1110.sql script instead to create a compatible stored procedure.
X
X   If you are running an IDS versions rpior to 10.00 you will need to run the
X   ratios_pre10.9.40.sql script instead. This has not been tested in versions
X   older than 9.40.
X
X2) Install the newratios.ksh script in your path: 
X   cp -p newratios.ksh /local/bin/ratios  # use the appropriate script here
X   chmod +x /local/bin/ratios
X
X3) Run the script for each server in your system:
X   INFORMIXSERVER=myserver ratios.ksh
X
XArt S. Kagel
SHAR_EOF
  (set 20 16 08 03 11 52 45 'README.ratios.1st'
   eval "${shar_touch}") && \
  chmod 0664 'README.ratios.1st'
if test $? -ne 0
then ${echo} "restore of README.ratios.1st failed"
fi
  if ${md5check}
  then (
       ${MD5SUM} -c >/dev/null 2>&1 || ${echo} 'README.ratios.1st': 'MD5 check failed'
       ) << \SHAR_EOF
75a00812266c8c1f739dc4f49abe89cc  README.ratios.1st
SHAR_EOF

else
test `LC_ALL=C wc -c < 'README.ratios.1st'` -ne 3136 && \
  ${echo} "restoration warning:  size of 'README.ratios.1st' is not 3136"
  fi
# ============= ratios.9.40.sql ==============
${echo} "x - extracting ratios.9.40.sql (text)"
  sed 's/^X//' << 'SHAR_EOF' > 'ratios.9.40.sql' &&
XGRANT usage ON LANGUAGE spl TO "public";
X
Xdrop procedure ratios();
X
XCREATE PROCEDURE  "informix".ratios ()
Xreturning char(10) , decimal(32,2) , decimal(32,2) , 
X	decimal(32,2) , decimal(32,2) ,
X	decimal(32,2) , decimal(32,2) ,
X	decimal(32,2) , decimal(32,2) ,
X	interval hour(5) to second , datetime year to second ;
X
Xdefine pagreads, bufwrites, buffwts, buffers, rapgs_used, btradata,
X	btraidx, dpra, stathrs, usedbuffs decimal(32,2);
Xdefine isopens, seqscans, lockreqs, lockwts decimal(32,2);
Xdefine br, ubtr, btr, rau, ssr, lwr decimal(32,2);
Xdefine stattime interval hour(5) to second;
Xdefine stattemp interval day(3) to second;
Xdefine statsecs interval second(9) to second;
Xdefine whenreset datetime year to second;
Xdefine lname char(13);
Xdefine tempstr char(12);
Xdefine lvalue, resettime, tempint decimal(20,0);
Xdefine pagsz int;
Xdefine bufstr char(10);
X
X-- trace on;
XFOREACH
X    select name, value
X    into lname, lvalue
X    from sysprofile
X    where name in ('pagreads','bufwrites', 'buffwts', 'rapgs_used', 'btradata', 
X	'btraidx', 'dpra', 'isopens', 'seqscans', 'lockreqs', 'lockwts' ) 
X
X    if lname == 'pagreads' then
X	let pagreads = lvalue;
X    elif lname == 'bufwrites' then
X	let bufwrites = lvalue;
X    elif lname == 'buffwts' then
X	let buffwts = lvalue;
X    elif lname == 'rapgs_used' then
X	let rapgs_used = lvalue;
X    elif lname == 'btradata' then
X	let btradata = lvalue;
X    elif lname == 'btraidx' then
X	let btraidx = lvalue;
X    elif lname == 'dpra' then
X	let dpra = lvalue;
X    elif lname == 'isopens' then
X	let isopens = lvalue;
X    elif lname == 'seqscans' then
X	let seqscans = lvalue;
X    elif lname == 'lockreqs' then
X	let lockreqs = lvalue;
X    elif lname == 'lockwts' then
X	let lockwts = lvalue;
X    end if
X
XEND FOREACH;
X
Xselect count(*) 
Xinto tempint
Xfrom systables st, syscolumns sc
Xwhere st.tabid = sc.tabid
X and tabname = 'sysbufhdr'
X and colname = 'pagenum';
X
Xif tempint == 0 then
X    -- IDS 9.40+
X    select count(*)
X    into usedbuffs
X    from sysbufhdr
X    where offset >= 0 and chunk > 0;
Xelse
X    -- IDS pre- 9.40
X     select count(*)
X    into usedbuffs
X    from sysbufhdr
X    where pagenum > 0;   
Xend if
X
Xselect current year to second
X	- ( sh_curtime - sh_pfclrtime) units second,
X	( sh_curtime - sh_pfclrtime) units second
Xinto whenreset, statsecs
Xfrom sysshmvals;
X
Xlet stattemp = current year to second - whenreset;
Xlet stattime = stattemp;
X
Xlet tempstr = (statsecs / 3600);
Xlet stathrs = tempstr;
X
Xselect cf_effective
Xinto buffers
Xfrom sysconfig 
Xwhere cf_name = 'BUFFERS';
X
Xif (pagreads + bufwrites) > 0 then
X    let br = ((buffwts * 100.00) / (pagreads + bufwrites));
Xelse
X    let br = 0.00;
Xend if
Xif (btradata + btraidx + dpra) > 0 then
X    let rau = ((rapgs_used * 100) / (btradata + btraidx + dpra));
Xelse
X    let rau = 100.00;
Xend if
Xif buffers > 0 and stathrs > 0 then
X    let btr = (((pagreads + bufwrites) / buffers) / stathrs);
Xelse
X    let btr = 0;
Xend if
Xif usedbuffs > 0 and stathrs > 0 then
X    let ubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
Xelse
X    let ubtr = 0;
Xend if
Xif isopens > 0 then
X    let ssr = (seqscans / isopens) * 100.00;
Xelse
X    let ssr = 0;
Xend if
Xif lockreqs > 0 then
X    let lwr = (lockwts / lockreqs) * 100.00;
Xelse
X    let lwr = 0.00;
Xend if
X
X-- trace off;
Xreturn "Total", br, rau, btr, ubtr, 0.00, 0.00, lwr, ssr, stattime, whenreset with resume;
X
X
X    let pagsz = 2048;
X
X    select a.cf_effective
X    into buffers
X    from sysconfig AS a
X    where cf_name = "BUFFERS";
X
X    select value
X    into buffwts
X    from sysprofile
X    where name = 'buffwts';
X
X    select value
X    into pagreads
X    from sysprofile
X    where name = 'pagreads';
X
X    select value
X    into bufwrites
X    from sysprofile
X    where name = 'bufwrites';
X
X    if (pagreads + bufwrites) > 0 then
X	let br = ((buffwts * 100.00) / (pagreads + bufwrites));
X    else
X	let br = 0.00;
X    end if
X
X    if buffers > 0 and stathrs > 0 then
X	let btr = (((pagreads + bufwrites) / buffers) / stathrs);
X    else
X	let btr = 0;
X    end if
X
X    LET bufstr = (pagsz / 1024)::INT || 'K';
X    LET rau = -1;
X    LET ubtr = -1;
X
X    return bufstr, br, rau, btr, ubtr, 0.00, 0.00, 0.00, 0.00, stattime, whenreset with resume;
X
Xend procedure
Xdocument
X'Procedure ratios.',
X'  Calculate critical ratios BR, BTR, and RAU and return with time of and ',
X'  time since stats were last reset (onstat -z).',
X'Written and copyright by Art S. Kagel, 2004-2011. ',
X'Permission for all reasonable use is granted without restriction. '
Xwith listing in 'ratios.sql';                                                                                                                                                                                                                              
X
XREVOKE EXECUTE ON FUNCTION "informix".ratios () FROM "public" AS "informix";
XGRANT EXECUTE ON FUNCTION "informix".ratios () TO "public" AS "informix";
X
X
X
SHAR_EOF
  (set 20 16 08 01 16 04 02 'ratios.9.40.sql'
   eval "${shar_touch}") && \
  chmod 0664 'ratios.9.40.sql'
if test $? -ne 0
then ${echo} "restore of ratios.9.40.sql failed"
fi
  if ${md5check}
  then (
       ${MD5SUM} -c >/dev/null 2>&1 || ${echo} 'ratios.9.40.sql': 'MD5 check failed'
       ) << \SHAR_EOF
b9be15c9b8033ea68f3300a6d4d7da7d  ratios.9.40.sql
SHAR_EOF

else
test `LC_ALL=C wc -c < 'ratios.9.40.sql'` -ne 4869 && \
  ${echo} "restoration warning:  size of 'ratios.9.40.sql' is not 4869"
  fi
# ============= ratios_1110.sql ==============
${echo} "x - extracting ratios_1110.sql (text)"
  sed 's/^X//' << 'SHAR_EOF' > 'ratios_1110.sql' &&
X{
X   Version of the ratios procedure that works with IDS 11.10 
X   (may also work with 10.00 and works with 11.50+ but its a bit slower than the one in ratios.sql).
X}
X
Xdrop procedure ratios;
Xcreate procedure ratios()
Xreturning char(10) AS which, decimal(20,2) AS BR, decimal(20,2) AS RAU, decimal(20,2) AS BTR, decimal(20,2) AS UBTR,
X	decimal(20,2) AS BTR2, decimal(20,2) AS BTR3,
X	decimal(20,2) AS LWR, decimal(20,2) AS SSR,
X	interval hour(5) to second AS since_reset, datetime year to second AS reset_time;
X
Xdefine pagreads, bufwrites, buffwts, buffers, rapgs_used, btradata,
X	btraidx, dpra, stathrs, usedbuffs decimal(20,2);
Xdefine br, ubtr, btr, rau decimal(20,2);
Xdefine lruwrites, chunkwrites, fgwrites, iswrites, isrewrites, isdeletes decimal(20,2);
Xdefine stattime interval hour(5) to second;
Xdefine stattemp interval day(3) to second;
Xdefine statsecs interval second(9) to second;
Xdefine whenreset datetime year to second;
Xdefine lname char(13);
Xdefine tempstr char(12);
Xdefine lvalue, resettime, tempint decimal(20,0);
Xdefine pagsz int;
Xdefine bufstr char(10);
Xdefine seqscans, isopens, lockreqs, lockwts decimal(20,0);
X
X-- trace on;
XFOREACH
X    select name, value
X    into lname, lvalue
X    from sysprofile
X    where name in ('pagreads','bufwrites', 'buffwts', 'rapgs_used', 'btradata', 
X	'btraidx', 'dpra', 'lruwrites', 'chunkwrites', 'fgwrites', 'iswrites', 'isrewrites', 'isdeletes',
X       'isopens', 'seqscans', 'lockreqs', 'lockwts'
X ) 
X
X    if lname == 'pagreads' then
X	let pagreads = lvalue;
X    elif lname == 'bufwrites' then
X	let bufwrites = lvalue;
X    elif lname == 'buffwts' then
X	let buffwts = lvalue;
X    elif lname == 'rapgs_used' then
X	let rapgs_used = lvalue;
X    elif lname == 'btradata' then
X	let btradata = lvalue;
X    elif lname == 'btraidx' then
X	let btraidx = lvalue;
X    elif lname == 'dpra' then
X	let dpra = lvalue;
X   elif lname == 'lruwrites' then
X       let lruwrites = lvalue;
X   elif lname == 'chunkwrites' then
X       let chunkwrites = lvalue;
X   elif lname == 'fgwrites' then
X       let fgwrites = lvalue;
X   elif lname == 'iswrites' then
X       let iswrites = lvalue;
X   elif lname == 'isrewrites' then
X       let isrewrites = lvalue;
X   elif lname == 'isdeletes' then
X       let isdeletes = lvalue;
X   elif lname == 'isopens' then
X       let isopens = lvalue;
X   elif lname == 'seqscans' then
X       let seqscans = lvalue;
X   elif lname == 'lockreqs' then
X       let lockreqs = lvalue;
X   elif lname == 'lockwts' then
X       let lockwts = lvalue;
X   end if
X
XEND FOREACH;
X
Xselect count(*) 
Xinto tempint
Xfrom systables st, syscolumns sc
Xwhere st.tabid = sc.tabid
X and tabname = 'sysbufhdr'
X and colname = 'pagenum';
X
Xif tempint == 0 then
X    -- IDS 9.40+
X    select count(*)
X    into usedbuffs
X    from sysbufhdr
X    where offset >= 0 and chunk > 0;
Xelse
X    -- IDS pre- 9.40
X     select count(*)
X    into usedbuffs
X    from sysbufhdr
X    where pagenum > 0;   
Xend if
X
Xselect current year to second
X	- ( sh_curtime - sh_pfclrtime) units second,
X	( sh_curtime - sh_pfclrtime) units second
Xinto whenreset, statsecs
Xfrom sysshmvals;
X
Xlet stattemp = current year to second - whenreset;
Xlet stattime = stattemp;
X
Xlet tempstr = (statsecs / 3600);
Xlet stathrs = tempstr;
X
Xselect cf_effective
Xinto buffers
Xfrom sysconfig 
Xwhere cf_name = 'BUFFERS';
X
Xif (pagreads + bufwrites) > 0 then
X    let br = ((buffwts * 100.00) / (pagreads + bufwrites));
Xelse
X    let br = 0.00;
Xend if
Xif (btradata + btraidx + dpra) > 0 then
X    let rau = ((rapgs_used * 100) / (btradata + btraidx + dpra));
Xelse
X    let rau = 100.00;
Xend if
Xif buffers > 0 and stathrs > 0 then
X    let btr = (((pagreads + bufwrites) / buffers) / stathrs);
Xelse
X    let btr = 0;
Xend if
Xif usedbuffs > 0 and stathrs > 0 then
X    let ubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
Xelse
X    let ubtr = 0;
Xend if
X
X-- trace off;
Xreturn "Total", br, rau, btr, ubtr, 0.00, 0.00, 0.00, 0.00, stattime, whenreset with resume;
X
Xforeach
X	select a.bufsize, a.nbuffs
X	into pagsz, buffers
X	from sysbufpool AS a
X	order by 1
X
X    if (pagsz / 1024) =  2 then
X    	 select value
X	 into buffwts
X	 from sysbufprofile
X	 where name = 'bufwaits_2K';
X    	 select value
X	 into pagreads
X	 from sysbufprofile
X	 where name = 'pagreads_2K';
X    	 select value
X	 into bufwrites
X	 from sysbufprofile
X	 where name = 'bufwrites_2K';
X
X    elif (pagsz / 1024) =  4 then
X    	 select value
X	 into buffwts
X	 from sysbufprofile
X	 where name = 'bufwaits_4K';
X    	 select value
X	 into pagreads
X	 from sysbufprofile
X	 where name = 'pagreads_4K';
X    	 select value
X	 into bufwrites
X	 from sysbufprofile
X	 where name = 'bufwrites_4K';
X
X    elif (pagsz / 1024) =  8 then
X    	 select value
X	 into buffwts
X	 from sysbufprofile
X	 where name = 'bufwaits_8K';
X    	 select value
X	 into pagreads
X	 from sysbufprofile
X	 where name = 'pagreads_8K';
X    	 select value
X	 into bufwrites
X	 from sysbufprofile
X	 where name = 'bufwrites_8K';
X
X    elif (pagsz / 1024) =  10 then
X    	 select value
X	 into buffwts
X	 from sysbufprofile
X	 where name = 'bufwaits_10K';
X    	 select value
X	 into pagreads
X	 from sysbufprofile
X	 where name = 'pagreads_10K';
X    	 select value
X	 into bufwrites
X	 from sysbufprofile
X	 where name = 'bufwrites_10K';
X
X    elif (pagsz / 1024) =  12 then
X    	 select value
X	 into buffwts
X	 from sysbufprofile
X	 where name = 'bufwaits_12K';
X    	 select value
X	 into pagreads
X	 from sysbufprofile
X	 where name = 'pagreads_12K';
X    	 select value
X	 into bufwrites
X	 from sysbufprofile
X	 where name = 'bufwrites_12K';
X
X    elif (pagsz / 1024) =  14 then
X    	 select value
X	 into buffwts
X	 from sysbufprofile
X	 where name = 'bufwaits_14K';
X    	 select value
X	 into pagreads
X	 from sysbufprofile
X	 where name = 'pagreads_14K';
X    	 select value
X	 into bufwrites
X	 from sysbufprofile
X	 where name = 'bufwrites_14K';
X
X    elif (pagsz / 1024) =  16 then
X    	 select value
X	 into buffwts
X	 from sysbufprofile
X	 where name = 'bufwaits_16K';
X    	 select value
X	 into pagreads
X	 from sysbufprofile
X	 where name = 'pagreads_16K';
X    	 select value
X	 into bufwrites
X	 from sysbufprofile
X	 where name = 'bufwrites_16K';
X
X    end if
X
X    if (pagreads + bufwrites) > 0 then
X	let br = ((buffwts * 100.00) / (pagreads + bufwrites));
X    else
X	let br = 0.00;
X    end if
X
X    if buffers > 0 and stathrs > 0 then
X	let btr = (((pagreads + bufwrites) / buffers) / stathrs);
X    else
X	let btr = 0;
X    end if
X
X    LET bufstr = (pagsz / 1024)::INT || 'K';
X    LET rau = -1;
X    LET ubtr = -1;
X
X    return bufstr, br, rau, btr, ubtr, 0.00, 0.00, 0.00, 0.00, stattime, whenreset with resume;
X
Xend foreach;
X
Xend procedure
Xdocument
X'Procedure ratios.',
X'  Calculate critical ratios BR, BTR, and RAU and return with time of and ',
X'  time since stats were last reset (onstat -z).',
X'Written and copyright by Art S. Kagel, 2004-2011. ',
X'Permission for all reasonable use is granted without restriction. '
Xwith listing in 'ratios.sql';
SHAR_EOF
  (set 20 13 03 22 07 56 15 'ratios_1110.sql'
   eval "${shar_touch}") && \
  chmod 0664 'ratios_1110.sql'
if test $? -ne 0
then ${echo} "restore of ratios_1110.sql failed"
fi
  if ${md5check}
  then (
       ${MD5SUM} -c >/dev/null 2>&1 || ${echo} 'ratios_1110.sql': 'MD5 check failed'
       ) << \SHAR_EOF
8b04d1acdbef5482ab7f07f6131a0a0a  ratios_1110.sql
SHAR_EOF

else
test `LC_ALL=C wc -c < 'ratios_1110.sql'` -ne 6883 && \
  ${echo} "restoration warning:  size of 'ratios_1110.sql' is not 6883"
  fi
# ============= ratios.1150.sql ==============
${echo} "x - extracting ratios.1150.sql (text)"
  sed 's/^X//' << 'SHAR_EOF' > 'ratios.1150.sql' &&
XGRANT usage ON LANGUAGE spl TO "public";
X
Xdrop procedure if exists ratios();
X
Xdrop function if exists rightshift( int, smallint );
X
XCREATE FUNCTION  "art".rightshift ( value int, bits smallint ) returns int;
Xdefine ix smallint;
Xdefine res int;
X
Xlet res = value;
X
Xfor ix = 1 to bits step 1
X    let res = res / 2;
Xend for;
X
Xreturn res;
Xend function;
X
X
XCREATE PROCEDURE  ratios ()
Xreturning char(10) AS which, decimal(32,2) AS BR, decimal(32,2) AS RAU, 
X       decimal(32,2) AS BTR, decimal(32,2) AS UBTR,
X       decimal(32,2) AS BTR2, decimal(32,2) AS BTR3,
X       decimal(32,2) AS LWR, decimal(32,2) AS SSR,
X       interval hour(5) to second AS since_reset, datetime year to second AS reset_time;
X
Xdefine pagreads, bufwrites, buffwts, buffers, rapgs_used, btradata decimal(32,2);
Xdefine tpagreads, tbufwrites, tbuffwts, tbuffers decimal(32,2);
Xdefine btraidx, dpra, stathrs decimal(32,2);
Xdefine usedbuffs, lruwrites, chunkwrites, fgwrites, iswrites, isrewrites, isdeletes decimal(32,2);
Xdefine tusedbuffs, tlruwrites, tchunkwrites, tfgwrites, tiswrites, tisrewrites, tisdeletes decimal(32,2);
Xdefine br, ubtr, btr, btr2, btr3, rau decimal(32,2);
Xdefine tbr, tubtr, tbtr, tbtr2, tbtr3, trau, tlwr, tssr decimal(32,2);
Xdefine stattime interval hour(5) to second;
Xdefine stattemp interval day(3) to second;
Xdefine statsecs interval second(9) to second;
Xdefine whenreset datetime year to second;
Xdefine lname char(13);
Xdefine tempstr char(12);
Xdefine lvalue decimal(32,0);
Xdefine pagsz, tempint, mvers int;
Xdefine bufstr char(10);
Xdefine seqscans, isopens, lockreqs, lockwts decimal(32,0);
X
X-- trace on;
X
Xselect dbinfo( 'version', 'major' )
Xinto mvers
Xfrom systables where tabid = 1;
X
Xselect sum(pp.pagreads) as pagreads, sum(pp.bufwrites) as bufwrites, sum(pp.iswrites) as iswrites, 
X	sum(pp.isrewrites) as isrewrites, sum(pp.isdeletes) as isdeletes, sum(pp.seqscans) as seqscans,
X	sum(pp.lockreqs) as lockreqs, sum(pp.lockwts) as lockwts
Xinto pagreads, bufwrites, iswrites, isrewrites, isdeletes, seqscans, lockreqs, lockwts
Xfrom sysptprof pp;
X
XFOREACH
X   select name, value
X   into lname, lvalue
X   from sysprofile
X   where name in ('buffwts', 'rapgs_used', 'btradata', 'btraidx', 'dpra', 'lruwrites', 'chunkwrites',
X		  'fgwrites', 'isopens')
X
X   if lname == 'buffwts' then
X       let buffwts = lvalue;
X   elif lname == 'rapgs_used' then
X       let rapgs_used = lvalue;
X   elif lname == 'btradata' then
X       let btradata = lvalue;
X   elif lname == 'btraidx' then
X       let btraidx = lvalue;
X   elif lname == 'dpra' then
X       let dpra = lvalue;
X   elif lname == 'lruwrites' then
X       let lruwrites = lvalue;
X   elif lname == 'chunkwrites' then
X       let chunkwrites = lvalue;
X   elif lname == 'fgwrites' then
X       let fgwrites = lvalue;
X   elif lname == 'isopens' then
X       let isopens = lvalue;
X   end if
X
XEND FOREACH;
X
Xselect count(*)
Xinto tempint
Xfrom systables st, syscolumns sc
Xwhere st.tabid = sc.tabid
X and tabname = 'sysbufhdr'
X and colname = 'pagenum';
X
Xif mvers >= 10 then
X   select sum(nbuffs)
X   into buffers
X   from sysbufpool;
Xelse
X   select cf_effective
X   into buffers
X   from sysconfig
X   where cf_name = 'BUFFERS';
Xend if
X
Xif mvers >= 12 then
X   -- IDS v12.10+ does not have usedbuffs in sysbufhdr!
X   -- Also, any query against sysbufhdr in 12.10.xC1 or .xC2 will crash the instance.
X   let usedbuffs = buffers;
Xelif tempint == 0 then
X   -- IDS 9.40+
X   select count(*)
X   into usedbuffs
X   from sysbufhdr
X   where offset >= 0 and chunk > 0;
Xelse
X   -- IDS pre- 9.40  --  ASK -- Crashes v12.10.xC2!
X   select count(*)
X   into usedbuffs
X   from sysbufhdr
X   where pagenum > 0;
Xend if
X
Xselect current year to second
X       - ( sh_curtime - sh_pfclrtime) units second,
X       ( sh_curtime - sh_pfclrtime) units second
Xinto whenreset, statsecs
Xfrom sysshmvals;
X
Xlet stattemp = current year to second - whenreset;
Xlet stattime = stattemp;
X
Xlet tempstr = (statsecs / 3600);
Xlet stathrs = tempstr;
X
Xif (pagreads + bufwrites) > 0 then
X   let tbr = ((buffwts * (100.00::decimal(32,2))) / (pagreads + bufwrites));
Xelse
X   let tbr = 0.00;
Xend if
Xif (btradata + btraidx + dpra) > 0 then
X   let trau = ((rapgs_used * 100) / (btradata + btraidx + dpra));
Xelse
X   let trau = (100.00::decimal(32,2));
Xend if
Xif buffers > 0 and stathrs > 0 then
X   let tbtr = (((pagreads + bufwrites) / buffers) / stathrs);
Xelse
X   let tbtr = 0;
Xend if
Xif usedbuffs > 0 and stathrs > 0 then
X   let tubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
X   let tbtr2 = (((pagreads + fgwrites + lruwrites + chunkwrites) / usedbuffs) / stathrs);
X   let tbtr3 = (((pagreads + iswrites + isrewrites + isdeletes) / usedbuffs) / stathrs);
Xelse
X   let tubtr = 0;
X   let tbtr2 = 0;
X   let tbtr3 = 0;
Xend if
X
Xlet tlwr = (lockwts / lockreqs) * (100.00::decimal(32,2));
Xlet tssr = (seqscans / isopens) * (100.00::decimal(32,2));
X
X -- trace off;
Xif tempint == 0 then   -- vers 9.40 or later
X
Xlet tusedbuffs = 0.0;
Xlet tbuffers = 0.0;
Xlet tpagreads = 0.0;
Xlet tbufwrites = 0.0;
Xlet tiswrites = 0.0;
Xlet tisrewrites = 0.0;
Xlet tisdeletes = 0.0;
Xlet tfgwrites = 0.0;
Xlet tlruwrites = 0.0;
Xlet tchunkwrites = 0.0;
X
X-- trace on;
Xselect dbsnum, pagesize from sysdbspaces into temp dbsinfo;
Xcreate unique index dbsinfo_ix on dbsinfo( dbsnum, pagesize );
X
Xforeach
X       select a.bufsize, a.nbuffs, a.bufwaits, a.pagreads, a.bufwrites, a.fgwrites, a.lruwrites, a.chunkwrites
X       into pagsz, buffers, buffwts, pagreads, bufwrites, fgwrites, lruwrites, chunkwrites
X       from sysbufpool AS a
X       order by 1
X
X   if mvers >= 12 then
X       let usedbuffs = buffers;
X   elif tempint == 0 then
X       -- IDS 9.40+
X       select count(*)
X       into usedbuffs
X       from sysbufhdr
X       where offset >= 0 and chunk > 0 and bufsize = pagsz;
X   end if
X
X   if (mvers >= 11) then
X-- trace pagsz;
X       select sum(pp.pagreads) pagreads, sum(pp.iswrites) iswrites, sum(pp.isrewrites) isrewrites, sum(pp.isdeletes) isdeletes
X       into pagreads, iswrites, isrewrites, isdeletes
X       from sysptprof pp, dbsinfo db
X       where pagesize = pagsz
X	 and dbsnum = rightshift( partnum, 20 )
X	; 
X	if (SQLCODE = 100) then 
X	    let pagreads = 0.0;
X	    let iswrites = 0.0;
X	    let isrewrites = 0.0;
X	    let isdeletes = 0.0;
X	end if;
X	if pagreads is null then
X	    let pagreads = 0.0;
X	end if;
X	if iswrites is null then
X	    let iswrites = 0.0;
X	end if;
X	if isrewrites is null then
X	    let isrewrites = 0.0;
X	end if;
X	if isdeletes is null then
X	    let isdeletes = 0.0;
X	end if;
X   else
X       let iswrites = 0.0;
X       let isrewrites = 0.0;
X       let isdeletes = 0.0;
X   end if
X
X   if (pagreads + bufwrites) > 0 then
X       let br = ((buffwts * (100.00::decimal(32,2))) / (pagreads + bufwrites));
X   else
X       let br = 0.00;
X   end if
X
X   if buffers > 0 and stathrs > 0 then
X       let btr = (((pagreads + bufwrites) / buffers) / stathrs);
X   else
X       let btr = 0.00;
X   end if
X
X-- trace pagsz;
X-- trace pagreads;
X-- trace iswrites;
X-- trace isrewrites;
X-- trace isdeletes;
X-- trace usedbuffs;
X-- trace stathrs;
X
X   if usedbuffs > 0 and stathrs > 0 then
X       let ubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
X       let btr2 = (((pagreads + fgwrites + lruwrites + chunkwrites) / usedbuffs) / stathrs);
X       let btr3 = (((pagreads + iswrites + isrewrites + isdeletes) / usedbuffs) / stathrs);
X   else
X       let ubtr = 0.00;
X       let btr2 = 0.00;
X       let btr3 = 0.00;
X   end if
X
X    let tusedbuffs = tusedbuffs + usedbuffs;
X    let tbuffers = tbuffers + buffers;
X    let tpagreads = tpagreads + pagreads;
X    let tbufwrites = tbufwrites + bufwrites;
X    let tiswrites = tiswrites + iswrites;
X    let tisrewrites = tisrewrites + isrewrites;
X    let tisdeletes = tisdeletes + isdeletes;
X    let tfgwrites = tfgwrites + fgwrites;
X    let tlruwrites = tlruwrites + lruwrites;
X    let tchunkwrites = tchunkwrites + chunkwrites;
X
X   LET bufstr = (pagsz / 1024)::INT || 'K';
X   LET rau = -1;
X
X   return bufstr, br, rau, btr, ubtr, btr2, btr3, 0, 0, stattime, whenreset with resume;
X
Xend foreach;
X
Xend if;
X
Xif mvers >= 10 then
X   let tbtr = (((tpagreads + tbufwrites) / tbuffers) / stathrs);
X   if usedbuffs > 0 and stathrs > 0 then
X       let tubtr = (((tpagreads + tbufwrites) / tusedbuffs) / stathrs);
X       let tbtr2 = (((tpagreads + tfgwrites + tlruwrites + tchunkwrites) / tusedbuffs) / stathrs);
X       let tbtr3 = (((tpagreads + tiswrites + tisrewrites + tisdeletes) / tusedbuffs) / stathrs);
X   end if
Xend if
X--  trace off;
X
Xreturn "Total", tbr, trau, tbtr, tubtr, tbtr2, tbtr3, tlwr, tssr, stattime, whenreset;
X
Xend procedure
Xdocument
X'Procedure ratios.',
X'  Calculate critical ratios BR, BTR, and RAU and return with time of and ',
X'  time since stats were last reset (onstat -z).',
X'Written and copyright by Art S. Kagel, 2004-2011. ',
X'Permission for all reasonable use is granted without restriction. '
Xwith listing in 'ratios.sql';
X
SHAR_EOF
  (set 20 17 05 03 15 15 33 'ratios.1150.sql'
   eval "${shar_touch}") && \
  chmod 0664 'ratios.1150.sql'
if test $? -ne 0
then ${echo} "restore of ratios.1150.sql failed"
fi
  if ${md5check}
  then (
       ${MD5SUM} -c >/dev/null 2>&1 || ${echo} 'ratios.1150.sql': 'MD5 check failed'
       ) << \SHAR_EOF
cc19c07bcae6137666098eafa3cc0b32  ratios.1150.sql
SHAR_EOF

else
test `LC_ALL=C wc -c < 'ratios.1150.sql'` -ne 8837 && \
  ${echo} "restoration warning:  size of 'ratios.1150.sql' is not 8837"
  fi
# ============= ratios.sql ==============
${echo} "x - extracting ratios.sql (text)"
  sed 's/^X//' << 'SHAR_EOF' > 'ratios.sql' &&
XGRANT usage ON LANGUAGE spl TO "public";
X
Xdrop procedure if exists ratios();
X
XCREATE PROCEDURE  ratios ()
Xreturning char(10) AS which, decimal(32,2) AS BR, decimal(32,2) AS RAU, 
X       decimal(32,2) AS BTR, decimal(32,2) AS UBTR,
X       decimal(32,2) AS BTR2, decimal(32,2) AS BTR3,
X       decimal(32,2) AS LWR, decimal(32,2) AS SSR,
X       interval hour(5) to second AS since_reset, datetime year to second AS reset_time;
X
Xdefine pagreads, bufwrites, buffwts, buffers, rapgs_used, btradata decimal(32,2);
Xdefine tpagreads, tbufwrites, tbuffwts, tbuffers decimal(32,2);
Xdefine btraidx, dpra, stathrs decimal(32,2);
Xdefine usedbuffs, lruwrites, chunkwrites, fgwrites, iswrites, isrewrites, isdeletes decimal(32,2);
Xdefine tusedbuffs, tlruwrites, tchunkwrites, tfgwrites, tiswrites, tisrewrites, tisdeletes decimal(32,2);
Xdefine br, ubtr, btr, btr2, btr3, rau decimal(32,2);
Xdefine tbr, tubtr, tbtr, tbtr2, tbtr3, trau, tlwr, tssr decimal(32,2);
Xdefine stattime interval hour(5) to second;
Xdefine stattemp interval day(3) to second;
Xdefine statsecs interval second(9) to second;
Xdefine whenreset datetime year to second;
Xdefine lname char(13);
Xdefine tempstr char(12);
Xdefine lvalue decimal(32,0);
Xdefine pagsz, tempint, mvers int;
Xdefine bufstr char(10);
Xdefine seqscans, isopens, lockreqs, lockwts decimal(32,0);
X
X-- trace on;
X
Xselect dbinfo( 'version', 'major' )
Xinto mvers
Xfrom systables where tabid = 1;
X
Xselect sum(pp.pagreads) as pagreads, sum(pp.bufwrites) as bufwrites, sum(pp.iswrites) as iswrites, 
X	sum(pp.isrewrites) as isrewrites, sum(pp.isdeletes) as isdeletes, sum(pp.seqscans) as seqscans,
X	sum(pp.lockreqs) as lockreqs, sum(pp.lockwts) as lockwts
Xinto pagreads, bufwrites, iswrites, isrewrites, isdeletes, seqscans, lockreqs, lockwts
Xfrom sysptprof pp;
X
XFOREACH
X   select name, value
X   into lname, lvalue
X   from sysprofile
X   where name in ('buffwts', 'rapgs_used', 'btradata', 'btraidx', 'dpra', 'lruwrites', 'chunkwrites',
X		  'fgwrites', 'isopens')
X
X   if lname == 'buffwts' then
X       let buffwts = lvalue;
X   elif lname == 'rapgs_used' then
X       let rapgs_used = lvalue;
X   elif lname == 'btradata' then
X       let btradata = lvalue;
X   elif lname == 'btraidx' then
X       let btraidx = lvalue;
X   elif lname == 'dpra' then
X       let dpra = lvalue;
X   elif lname == 'lruwrites' then
X       let lruwrites = lvalue;
X   elif lname == 'chunkwrites' then
X       let chunkwrites = lvalue;
X   elif lname == 'fgwrites' then
X       let fgwrites = lvalue;
X   elif lname == 'isopens' then
X       let isopens = lvalue;
X   end if
X
XEND FOREACH;
X
Xselect count(*)
Xinto tempint
Xfrom systables st, syscolumns sc
Xwhere st.tabid = sc.tabid
X and tabname = 'sysbufhdr'
X and colname = 'pagenum';
X
Xif mvers >= 10 then
X   select sum(nbuffs)
X   into buffers
X   from sysbufpool;
Xelse
X   select cf_effective
X   into buffers
X   from sysconfig
X   where cf_name = 'BUFFERS';
Xend if
X
Xif mvers >= 12 then
X   -- IDS v12.10+ does not have usedbuffs in sysbufhdr!
X   -- Also, any query against sysbufhdr in 12.10.xC1 or .xC2 will crash the instance.
X   let usedbuffs = buffers;
Xelif tempint == 0 then
X   -- IDS 9.40+
X   select count(*)
X   into usedbuffs
X   from sysbufhdr
X   where offset >= 0 and chunk > 0;
Xelse
X   -- IDS pre- 9.40  --  ASK -- Crashes v12.10.xC2!
X   select count(*)
X   into usedbuffs
X   from sysbufhdr
X   where pagenum > 0;
Xend if
X
Xselect current year to second
X       - ( sh_curtime - sh_pfclrtime) units second,
X       ( sh_curtime - sh_pfclrtime) units second
Xinto whenreset, statsecs
Xfrom sysshmvals;
X
Xlet stattemp = current year to second - whenreset;
Xlet stattime = stattemp;
X
Xlet tempstr = (statsecs / 3600);
Xlet stathrs = tempstr;
Xif (stathrs = 0.00) then let stathrs = 0.01; end if
X
Xif (pagreads + bufwrites) > 0 then
X   let tbr = ((buffwts * (100.00::decimal(32,2))) / (pagreads + bufwrites));
Xelse
X   let tbr = 0.00;
Xend if
Xif (btradata + btraidx + dpra) > 0 then
X   let trau = ((rapgs_used * 100) / (btradata + btraidx + dpra));
Xelse
X   let trau = (100.00::decimal(32,2));
Xend if
Xif buffers > 0 and stathrs > 0 then
X   let tbtr = (((pagreads + bufwrites) / buffers) / stathrs);
Xelse
X   let tbtr = 0;
Xend if
Xif usedbuffs > 0 and stathrs > 0 then
X   let tubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
X   let tbtr2 = (((pagreads + fgwrites + lruwrites + chunkwrites) / usedbuffs) / stathrs);
X   let tbtr3 = (((pagreads + iswrites + isrewrites + isdeletes) / usedbuffs) / stathrs);
Xelse
X   let tubtr = 0;
X   let tbtr2 = 0;
X   let tbtr3 = 0;
Xend if
X
Xlet tlwr = (lockwts / lockreqs) * (100.00::decimal(32,2));
Xlet tssr = (seqscans / isopens) * (100.00::decimal(32,2));
X
X -- trace off;
Xif tempint == 0 then   -- vers 9.40 or later
X
Xlet tusedbuffs = 0.0;
Xlet tbuffers = 0.0;
Xlet tpagreads = 0.0;
Xlet tbufwrites = 0.0;
Xlet tiswrites = 0.0;
Xlet tisrewrites = 0.0;
Xlet tisdeletes = 0.0;
Xlet tfgwrites = 0.0;
Xlet tlruwrites = 0.0;
Xlet tchunkwrites = 0.0;
X
X-- trace on;
Xselect dbsnum, pagesize from sysdbspaces into temp dbsinfo;
Xcreate unique index dbsinfo_ix on dbsinfo( dbsnum, pagesize );
X
Xforeach
X       select a.bufsize, a.nbuffs, a.bufwaits, a.pagreads, a.bufwrites, a.fgwrites, a.lruwrites, a.chunkwrites
X       into pagsz, buffers, buffwts, pagreads, bufwrites, fgwrites, lruwrites, chunkwrites
X       from sysbufpool AS a
X       order by 1
X
X   if mvers >= 12 then
X       let usedbuffs = buffers;
X   elif tempint == 0 then
X       -- IDS 9.40+
X       select count(*)
X       into usedbuffs
X       from sysbufhdr
X       where offset >= 0 and chunk > 0 and bufsize = pagsz;
X   end if
X
X   if (mvers >= 11) then
X-- trace pagsz;
X       select sum(pp.pagreads) pagreads, sum(pp.iswrites) iswrites, sum(pp.isrewrites) isrewrites, sum(pp.isdeletes) isdeletes
X       into pagreads, iswrites, isrewrites, isdeletes
X       from sysptprof pp, dbsinfo db
X       where pagesize = pagsz
X	 and dbsnum = ifx_bit_rightshift( partnum, 20 )
X	; 
X	if (SQLCODE = 100) then 
X	    let pagreads = 0.0;
X	    let iswrites = 0.0;
X	    let isrewrites = 0.0;
X	    let isdeletes = 0.0;
X	end if;
X	if pagreads is null then
X	    let pagreads = 0.0;
X	end if;
X	if iswrites is null then
X	    let iswrites = 0.0;
X	end if;
X	if isrewrites is null then
X	    let isrewrites = 0.0;
X	end if;
X	if isdeletes is null then
X	    let isdeletes = 0.0;
X	end if;
X   else
X       let iswrites = 0.0;
X       let isrewrites = 0.0;
X       let isdeletes = 0.0;
X   end if
X
X   if (pagreads + bufwrites) > 0 then
X       let br = ((buffwts * (100.00::decimal(32,2))) / (pagreads + bufwrites));
X   else
X       let br = 0.00;
X   end if
X
X   if buffers > 0 and stathrs > 0 then
X       let btr = (((pagreads + bufwrites) / buffers) / stathrs);
X   else
X       let btr = 0.00;
X   end if
X
X-- trace pagsz;
X-- trace pagreads;
X-- trace iswrites;
X-- trace isrewrites;
X-- trace isdeletes;
X-- trace usedbuffs;
X-- trace stathrs;
X
X   if usedbuffs > 0 and stathrs > 0 then
X       let ubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
X       let btr2 = (((pagreads + fgwrites + lruwrites + chunkwrites) / usedbuffs) / stathrs);
X       let btr3 = (((pagreads + iswrites + isrewrites + isdeletes) / usedbuffs) / stathrs);
X   else
X       let ubtr = 0.00;
X       let btr2 = 0.00;
X       let btr3 = 0.00;
X   end if
X
X    let tusedbuffs = tusedbuffs + usedbuffs;
X    let tbuffers = tbuffers + buffers;
X    let tpagreads = tpagreads + pagreads;
X    let tbufwrites = tbufwrites + bufwrites;
X    let tiswrites = tiswrites + iswrites;
X    let tisrewrites = tisrewrites + isrewrites;
X    let tisdeletes = tisdeletes + isdeletes;
X    let tfgwrites = tfgwrites + fgwrites;
X    let tlruwrites = tlruwrites + lruwrites;
X    let tchunkwrites = tchunkwrites + chunkwrites;
X
X   LET bufstr = (pagsz / 1024)::INT || 'K';
X   LET rau = -1;
X
X   return bufstr, br, rau, btr, ubtr, btr2, btr3, 0, 0, stattime, whenreset with resume;
X
Xend foreach;
X
Xend if;
X
Xif mvers >= 10 then
X   let tbtr = (((tpagreads + tbufwrites) / tbuffers) / stathrs);
X   if usedbuffs > 0 and stathrs > 0 then
X       let tubtr = (((tpagreads + tbufwrites) / tusedbuffs) / stathrs);
X       let tbtr2 = (((tpagreads + tfgwrites + tlruwrites + tchunkwrites) / tusedbuffs) / stathrs);
X       let tbtr3 = (((tpagreads + tiswrites + tisrewrites + tisdeletes) / tusedbuffs) / stathrs);
X   end if
Xend if
X--  trace off;
X
Xreturn "Total", tbr, trau, tbtr, tubtr, tbtr2, tbtr3, tlwr, tssr, stattime, whenreset;
X
Xend procedure
Xdocument
X'Procedure ratios.',
X'  Calculate critical ratios BR, BTR, and RAU and return with time of and ',
X'  time since stats were last reset (onstat -z).',
X'Written and copyright by Art S. Kagel, 2004-2011. ',
X'Permission for all reasonable use is granted without restriction. '
Xwith listing in 'ratios.sql';
X
SHAR_EOF
  (set 20 19 10 07 06 24 21 'ratios.sql'
   eval "${shar_touch}") && \
  chmod 0664 'ratios.sql'
if test $? -ne 0
then ${echo} "restore of ratios.sql failed"
fi
  if ${md5check}
  then (
       ${MD5SUM} -c >/dev/null 2>&1 || ${echo} 'ratios.sql': 'MD5 check failed'
       ) << \SHAR_EOF
2eac47e0cd90649b7a08c9e3bf671bba  ratios.sql
SHAR_EOF

else
test `LC_ALL=C wc -c < 'ratios.sql'` -ne 8625 && \
  ${echo} "restoration warning:  size of 'ratios.sql' is not 8625"
  fi
# ============= ratios.7.31.ksh ==============
${echo} "x - extracting ratios.7.31.ksh (text)"
  sed 's/^X//' << 'SHAR_EOF' > 'ratios.7.31.ksh' &&
X#!/usr/bin/ksh
X#
X# From the original by Marat Kotik, modified to include BTR by Art S. Kagel, 3/21/2011
X#
X#
Xbuffers=$( nawk '/^BUFFERS /{print $2;}' $INFORMIXDIR/etc/$ONCONFIG )
Xonstat -p | nawk -v buffers=$buffers '
XBEGIN { print ""; }
X/On-Line/{ split($12,a,":"); time=($10 * 24) + a[1] + a[2]/60.00;next;}
X/[a-zA-Z]/ {
X         for(i=1; i<=NF; i++){
X            name[i] = $i;
X         }
X      }
X/[0-9]/ {
X         for(i=1; i<=NF; i++){
X            content[name[i]] = $i;
X         }
X      }
XEND {
X      ixdaRA = content["ixda-RA"];
X      idxRA = content["idx-RA"];
X      daRA = content["da-RA"];
X      RApgsused = content["RA-pgsused"];
X      print "Read Utilization (UR): ", (RApgsused / (ixdaRA + idxRA + daRA)) * 100, "%";
X
X      bufwaits = content["bufwaits"];
X      bufwrits = content["bufwrits"];
X      pagreads = content["pagreads"];
X      print "Bufwaits Ratio(BR): ", ((bufwaits/(pagreads + bufwrits)) * 100), "%";
X
X      print "Buffer Turnover Rate(BTR): ", ((pagreads + bufwrits)/buffers)/time, " per hour";
X
X      print "\n";
X      print "\n----------------------------------------------------------\n";
X      print "The UR should ideally be VERY near 100% - the higher the better.";
X      print "The BR should be below 7% - the lower the better.";
X      print "Check CDI archives for more info.\n";
X}'
SHAR_EOF
  (set 20 11 03 21 11 42 16 'ratios.7.31.ksh'
   eval "${shar_touch}") && \
  chmod 0664 'ratios.7.31.ksh'
if test $? -ne 0
then ${echo} "restore of ratios.7.31.ksh failed"
fi
  if ${md5check}
  then (
       ${MD5SUM} -c >/dev/null 2>&1 || ${echo} 'ratios.7.31.ksh': 'MD5 check failed'
       ) << \SHAR_EOF
9d29c83e3ab3ade559c6de4885f1c156  ratios.7.31.ksh
SHAR_EOF

else
test `LC_ALL=C wc -c < 'ratios.7.31.ksh'` -ne 1354 && \
  ${echo} "restoration warning:  size of 'ratios.7.31.ksh' is not 1354"
  fi
# ============= newratios.ksh ==============
${echo} "x - extracting newratios.ksh (text)"
  sed 's/^X//' << 'SHAR_EOF' > 'newratios.ksh' &&
X#! /usr/bin/ksh
X#
X# Author: Art S. Kagel
X# Copyright: 2009-2010 by Art S. Kagel.
X#           Permission for all reasonable use is granted without restriction.
X#
X# A new implementation for IDS 10.00 and later.
X#
X
Xtempfile=/tmp/ratios.$$.tmp
X
Xdbaccess sysmaster - <<EOF 2>/dev/null >$tempfile
Xexecute procedure ratios();
XEOF
X
Xawk '
XBEGIN{
X   last = "      ";
X   printf "\n";
X}
X$1 == "which" {
X   whichbuf = $2;
X   next;
X}
X$1 == "br" {
X   br = $2
X   next;
X}
X$1 == "rau" {
X   rau = $2;
X   next;
X}
X$1 == "btr" {
X   btr = $2;
X   next;
X}
X$1 == "btr2" {
X   btr2 = $2;
X}
X$1 == "btr3" {
X   btr3 = $2;
X}
X$1 == "ubtr" {
X   ubtr = $2;
X   next;
X}
X$1 == "lwr" {
X   lwr = $2;
X   next;
X}
X$1 == "ssr" {
X   ssr = $2;
X   next;
X}
X$1 == "since_reset" {
X   zelapse = $2;
X   next;
X}
X$1 == "reset_time" {
X   ztime = $2 " " $3;
X
X   split( zelapse, units, ":" );
X   hrs=units[1];
X   mins=units[2];
X   secs=units[3];
X
X   if (whichbuf == "Total") {
X       printf "\n\nMetric Ratio Report Summary For All Caches\n\n";
X       printf "\tReadAhead Utilization:      %f%%\n", rau;
X   } else {
X       printf "\n\nMetric Ratio Report For %s Cache\n\n", whichbuf;
X   }
X   printf "\tBufwaits Ratio:             %f%%\n", br;
X   printf "\tBuffer Turnover Rate:       %6.2f/hr\n", btr;
X   printf "\tUsed Buffer Turnover Rate:  %6.2f/hr\n", ubtr;
X
X   printf "\tExperimental BTR #2:        %6.2f/hr\n", btr2;
X   printf "\tExperimental BTR #3:        %6.2f/hr\n", btr3;
X
X   last = whichbuf;
X   if (whichbuf == "Total") {
X       printf "\tLock Wait Ratio:          %9.5f%%\n", lwr;
X       printf "\tSequential Scan Ratio:    %9.5f%%\n", ssr;
X   }
X
X}
XEND {
X   printf "\n";
X   printf "Statistics reset at: %s\n", ztime;
X   printf "Elapsed time since reset:       %8s\n", zelapse;
X   printf "\n";
X   print "----------------------------------------------------------\n";
X   print "The RAU should ideally be VERY near 100% - the higher the better.";
X   print "The BR should be below 7% - the lower the better.";
X   print "The BTR and UBTR should ideally be less than 10.  UBTR is the same";
X   print "calculation as BTR but removes any unused buffers from the calculation";
X   print "If BTR and UBTR differ, your buffer cache may be too large.  UBTR was";
X   print "a unsuccessful attempt a more accurate picture the nature of the ";
X   print "cache churning which the BTR reports.\n";
X   print "Check CDI archives or the Informix FAQ for more info.\n";
X   print "\nBaseline values for Experimental BTR metrics #2 & #3 have not yet ";
X   print "been determined.  Any data you can provide back to me that will help";
X   print "to determine the acceptable and troublesome value ranges will be greatly";
X   print "appreciated.  Please send data to: art.kagel@gmail.com.";
X}
X' $tempfile
Xrm $tempfile
SHAR_EOF
  (set 20 13 03 21 14 46 30 'newratios.ksh'
   eval "${shar_touch}") && \
  chmod 0664 'newratios.ksh'
if test $? -ne 0
then ${echo} "restore of newratios.ksh failed"
fi
  if ${md5check}
  then (
       ${MD5SUM} -c >/dev/null 2>&1 || ${echo} 'newratios.ksh': 'MD5 check failed'
       ) << \SHAR_EOF
288749e011b976afba86df5d8645d840  newratios.ksh
SHAR_EOF

else
test `LC_ALL=C wc -c < 'newratios.ksh'` -ne 2746 && \
  ${echo} "restoration warning:  size of 'newratios.ksh' is not 2746"
  fi
if rm -fr ${lock_dir}
then ${echo} "x - removed lock directory ${lock_dir}."
else ${echo} "x - failed to remove lock directory ${lock_dir}."
     exit 1
fi
exit 0
