Help on IDS 11.50 ratios script / program

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

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 - <<EOFunload 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%%\\ ", rau; printf "Bufwaits Ratio: %f%%\\ ", br; printf "Buffer Turnover Rate: %6.2/hr\\ ", btr; printf "Used Buffer Turnover Rate: %6.2/hr\\ ", 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

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%%\\ ", rau; > > printf "Bufwaits Ratio: %f%%\\ ", br; > > printf "Buffer Turnover Rate: %6.2/hr\\ ", btr; > > printf "Used Buffer Turnover Rate: %6.2/hr\\ ", 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. > > > >

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%%\\ ", rau; > > printf "Bufwaits Ratio: %f%%\\ ", br; > > printf "Buffer Turnover Rate: %6.2/hr\\ ", btr; > > printf "Used Buffer Turnover Rate: %6.2/hr\\ ", 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

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%%\\ ", rau; >> >> printf "Bufwaits Ratio: %f%%\\ ", br; >> >> printf "Buffer Turnover Rate: %6.2/hr\\ ", btr; >> >> printf "Used Buffer Turnover Rate: %6.2/hr\\ ", 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, f

On Wed, Aug 5, 2009 at 06:16, Alexandre Marini<amarini@fazenda.ms.gov.br> wrote: > Ok, Cesar, but how can I do an "unload" on a procedure?? You cannot do UNOAD in a stored procedure (nor LOAD, nor OUTPUT, nor INFO). They are pseudo-SQL statements, not real SQL statements. They are simulated by the client-side software (primarily DB-Access, ISQL, I4GL). More precisely, to get the effect of UNLOAD, your stored procedure would have to use the SYSTEM statement to execute DB-Access or a workalike and have it run the UNLOAD statement on your behalf. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even. Jonathan Swift - "May you live every day of your life." - http://www.brainyquote.com/quotes/authors/j/jonathan_swift.html

Replace the "execute procedure" for "select * from table(ratios());" --- 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 [16603] Para: ids@iiug.org Data: Quarta-feira, 5 de Agosto de 2009, 10:16 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%%\\ ", rau; >> >> printf "Bufwaits Ratio: %f%%\\ ", br; >> >> printf "Buffer Turnover Rate: %6.2/hr\\ ", btr; >> >> printf "Used Buffer Turnover Rate: %6.2/hr\\ ", 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 org

OOPS. Sorry about that! New version below, and this one works: ### ratios.sql #### drop procedure ratios; create procedure ratios() returning decimal(20,2) as BR, decimal(20,2) as RAU, decimal(20,2) as BTR, decimal(20,2) as UBTR, interval hour(5) to second as zelapse, datetime year to second as ztime; 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; 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'; #################### ### newrations.ksh ### #! /usr/bin/ksh ( dbaccess -e sysmaster - 2>/dev/null <<EOFexecute procedure ratios();EOF ) | awk ' $1 == "btr" {btr=$2;} /br/ {br=$2;} /rau/ {rau=$2;} /ubtr/ {ubtr=$2;} /zelapse/ {zelapse=$2;} /ztime/ {ztime=$2 " " $3;} END { # 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%%\\ ", rau; printf "Bufwaits Ratio: %f%%\\ ", br; printf "Buffer Turnover Rate: %6.2f/hr\\ ", btr; printf "Used Buffer Turnover Rate: %6.2f/hr\\ ", ubtr; printf "\\ Stats last zerod at: %s.\\ ", ztime; }' #################### 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. --001636c5a82f6f4d430470cd0323