IDS Forum

Re: Help on IDS 11.50 ratios script / program

Posted By: Alexandre Marini
Date: Wednesday, 5 August 2009, at 9:16 a.m.

In Response To: Re: Help on IDS 11.50 ratios script / program (Cesar Inacio Martins)

Ok, Cesar, but how can I do an "unload" on a procedure??
The execution of the

unload to "$tempfile"
execute procedure ratios();

returns sql error 809...

Alexandre Marini

Tecnologia da Informação - DBA

SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix

Cesar Inacio Martins escreveu:
> Hi Alexandre,
>
> Don't redirect the dbaccess output.
> For use the Art script you must use the UNLOAD statement because the
> delimitator "|" is needed by AWK script.
>
> The FS = "|" means FIELD SEPARATOR = "|"
>
> Regards
> Cesar
>
> --- Em qua, 5/8/09, Alexandre Marini <amarini@fazenda.ms.gov.br> escreveu:
>
> De: Alexandre Marini <amarini@fazenda.ms.gov.br>
> Assunto: Re: Help on IDS 11.50 ratios script / program [16601]
> Para: ids@iiug.org
> Data: Quarta-feira, 5 de Agosto de 2009, 9:01
>
> Thanks a lot, mr. Art!
> There´s only one more thing:
> when I run the script, it raises an 809 sql error.
>
> I´ve already tryed to modify your script, instead of doing a "unload to"
> statement,
> I did a simple "dbaccess sysmaster query.sql > $tempfile"
> but then I´d need to modify the other things, the
> awk 'BEGIN{ FS="|"; }
> line I thing won´t work nice.
>
> Could you give me a "last hand" on this?
> Best regards!
>
> Alexandre Marini
>
> Tecnologia da Informação - DBA
>
> SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
>
> Art Kagel escreveu:
>
>> 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
>>
>>
>>
>>
>
> *******************************************************************************
>
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>>
>>
>>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
> ____________________________________________________________________________________
> Veja quais são os assuntos do momento no Yahoo! +Buscados
> http://br.maisbuscados.yahoo.com
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>

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.