IDS Forum

Re: Help on IDS 11.50 ratios script / program

Posted By: Art Kagel
Date: Tuesday, 4 August 2009, at 2:49 p.m.

In Response To: Help on IDS 11.50 ratios script / program (Alexandre Marini)

Try this. Run the CREATE PROCEDURE statement in sysmaster, then you can run
newratios.ksh anytime you need to. The fourth metric (Used Buffer Turnover
Rate) will differ from the BTR if you have unused buffers - it's a new
metric I was playing with that's of little use in most environments.

ratios.sql:
##################################################################
drop procedure ratios;
create procedure ratios()
returning decimal(20,2), decimal(20,2), decimal(20,2), decimal(20,2),

interval hour(5) to second, datetime year to second;

define pagreads, bufwrites, buffwts, buffers, rapgs_used, btradata,

btraidx, dpra, stathrs, usedbuffs decimal(20,2);
define br, ubtr, btr, rau decimal(20,2);
define stattime interval hour(5) to second;
define stattemp interval day(3) to second;
define statsecs interval second(9) to second;
define whenreset datetime year to second;
define lname char(13);
define tempstr char(12);
define lvalue, resettime, tempint decimal(20,0);

-- trace on;
FOREACH

select name, value

into lname, lvalue

from sysprofile

where name in ('pagreads','bufwrites', 'buffwts', 'rapgs_used',
'btradata',

'btraidx', 'dpra')

if lname == 'pagreads' then

let pagreads = lvalue;

elif lname == 'bufwrites' then

let bufwrites = lvalue;

elif lname == 'buffwts' then

let buffwts = lvalue;

elif lname == 'rapgs_used' then

let rapgs_used = lvalue;

elif lname == 'btradata' then

let btradata = lvalue;

elif lname == 'btraidx' then

let btraidx = lvalue;

elif lname == 'dpra' then

let dpra = lvalue;

end if

END FOREACH;

-- Detact server version
select count(*)
into tempint
from systables st, syscolumns sc
where st.tabid = sc.tabid
and tabname = 'sysbufhdr'
and colname = 'pagenum';

if tempint == 0 then

-- IDS 9.40+

select count(*)

into usedbuffs

from sysbufhdr

where offset >= 0 and chunk > 0;
else

-- IDS pre- 9.40

select count(*)

into usedbuffs

from sysbufhdr

where pagenum > 0;
end if

select current year to second

- ( sh_curtime - sh_pfclrtime) units second,

( sh_curtime - sh_pfclrtime) units second
into whenreset, statsecs
from sysshmvals;

let stattemp = current year to second - whenreset;
let stattime = stattemp;

let tempstr = (statsecs / 3600);
let stathrs = tempstr;

select cf_effective
into buffers
from sysconfig
where cf_name = 'BUFFERS';

if (pagreads + bufwrites) > 0 then

let br = ((buffwts * 100.00) / (pagreads + bufwrites));
else

let br = 0.00;
end if
if (btradata + btraidx + dpra) > 0 then

let rau = ((rapgs_used * 100) / (btradata + btraidx + dpra));
else

let rau = 100.00;
end if
if buffers > 0 and stathrs > 0 then

let btr = (((pagreads + bufwrites) / buffers) / stathrs);
else

let btr = 0;
end if
if usedbuffs > 0 and stathrs > 0 then

let ubtr = (((pagreads + bufwrites) / usedbuffs) / stathrs);
else

let ubtr = 0;
end if

-- trace off;
return br, rau, btr, ubtr, stattime, whenreset;

end procedure
document
'Procedure ratios.',
' Calculate critical ratios BR, BTR, and RAU and return with time of and ',
' time since stats were last reset (onstat -z).',
'Written and copyright by Art S. Kagel, 2004. ',
'Permission for all reasonable use is granted without restriction. '
with listing in 'ratios.sql';

####################################################################
newratios.ksh
####################################################################
#! /usr/bin/ksh

tempfile=/tmp/ratios.$$.tmp

dbaccess -e sysmaster - <<EOF
unload to "$tempfile"
execute procedure ratios();
EOF

awk 'BEGIN{ FS="|"; }
{

br=$1; rau=$2; btr=$3; ubtr=$4; zelapse=$5; ztime=$6;

split( zelapse, units, ":" );

hrs=units[1];

mins=units[2];

secs=units[3];

printf "ReadAhead Utilization: %f%%\n", rau;

printf "Bufwaits Ratio: %f%%\n", br;

printf "Buffer Turnover Rate: %6.2/hr\n", btr;

printf "Used Buffer Turnover Rate: %6.2/hr\n", ubtr;
}' $tempfile

#####################################################################

Art

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.

On Tue, Aug 4, 2009 at 1:30 PM, Alexandre Marini
<amarini@fazenda.ms.gov.br>wrote:

> Hello, friends.
> I´m using a IDS 11.50.FC4 on a linux_64 box.
>
> I´ve actually tryed to adapt the "ratios.sh" script of the download area,
> but it´s not working here.
>
> That´s the one which "Calculates Read-ahead utilization, Bufwaits and
> Buffer Turnover ratios"
> but I´m not familiar developing on perl, I´d try to ask if someone has
> some other kind of
> query, or program, that does that work on 11.50.X versions, that would
> be great....
>
> Thanks a lot for your attention,
> Regards!
>
> --
>
> Alexandre Marini
>
> Tecnologia da Informação - DBA
>
> SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636c5b712549e9b04705558ec

Messages In This Thread

• Help on IDS 11.50 ratios script / program
  Alexandre Marini -- Tuesday, 4 August 2009, at 1:30 p.m.
  • Re: Help on IDS 11.50 ratios script / program
    Art Kagel -- Tuesday, 4 August 2009, at 2:49 p.m.
    • Re: Help on IDS 11.50 ratios script / program
      Alexandre Marini -- Wednesday, 5 August 2009, at 8:01 a.m.
      • Re: Help on IDS 11.50 ratios script / program
        Cesar Inacio Martins -- Wednesday, 5 August 2009, at 9:10 a.m.
        • Re: Help on IDS 11.50 ratios script / program
          Alexandre Marini -- Wednesday, 5 August 2009, at 9:16 a.m.
          • Re: Help on IDS 11.50 ratios script / program
            Jonathan Leffler -- Thursday, 6 August 2009, at 1:09 a.m.
          • Re: Help on IDS 11.50 ratios script / program
            Cesar Inacio Martins -- Thursday, 6 August 2009, at 8:03 a.m.
            • Re: Help on IDS 11.50 ratios script / program
              Art Kagel -- Monday, 10 August 2009, at 1:35 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.