Herding Programmers with DB resources

Good afternoon, Well, I get to enjoy one of the unfortunately aspects of Systems and Database administration. I get to content with programmers that write in-house applications in a way that does not take into system resources and table lock contention. So, after a long time of adding resources to the DB server, I have learned (yet again) that the more you give, the more programmers will use. So, I've had to restart my Informix engine (yet again) in my production environment because of careless and disconcerted programming that gobbles up all my system resources. If you have been down the dark alley and have survived and found tools to help, I would be very interested in your advice with: I am currently on Informix IDS 10.00.FC5 for HP-UX platform. I imagine the answers are probably No, but I still have to ask: Is there a way to restrict certain kinds of SQL operations based on User ID with in Informix? When I have an Informix SQL processes, how can I tell how much CPU/VPU the processes is consuming if I know the process ID? Is there an onstat command for that? I don't seem to get much CPU usage out of onstat -g ses, sql, or ath. How does your organization restrict or regulate programmers, development, or how do you coordinate with them to prevent resource contentions on your DB systems? Thanks for any guidance or input you might have. Sincerely, Jonathan B. Smaby Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu <%3c!--%20Facebook%20Badge%20START%20--%3e%3ca%20href=%22http:/www.facebook.com/ jonathan.smaby%22%20title=%22Jonathan%20B.%20Smaby%22%20target=%22_TOP%22%20styl e=%22font-family:%20&quot;lucida%20grande&quot;,tahoma,verdana,arial,sans-serif; %20font-size:%2011px;%20font-variant:%20normal;%20font-style:%20normal;> ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.

Jonathan Smaby wrote: > Good afternoon, > > Well, I get to enjoy one of the unfortunately aspects of Systems and Database > administration. I get to content with programmers that write in-house > applications in a way that does not take into system resources and table lock > contention. So, after a long time of adding resources to the DB server, I have > learned (yet again) that the more you give, the more programmers will use. So, > I've had to restart my Informix engine (yet again) in my production > environment because of careless and disconcerted programming that gobbles up > all my system resources. If you have been down the dark alley and have > survived and found tools to help, I would be very interested in your advice > with: > > I am currently on Informix IDS 10.00.FC5 for HP-UX platform. > > I imagine the answers are probably No, but I still have to ask: > > Is there a way to restrict certain kinds of SQL operations based on User ID > with in Informix? I-Spy. > When I have an Informix SQL processes, how can I tell how much CPU/VPU the > processes is consuming if I know the process ID? Is there an onstat command > for that? I don't seem to get much CPU usage out of onstat -g ses, sql, or > ath. I think SQLIDEBUG with do this, but that's using Cadillac to swat a fly. > How does your organization restrict or regulate programmers, development, or > how do you coordinate with them to prevent resource contentions on your DB > systems? I have a baseball bat and I'm not afraid to use it. ;o) > Thanks for any guidance or input you might have. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

A hard rubber bat about 15 inches long is the preferred tool for dealing with recalcitrant developers. Hurts like hell but leaves no broken bones or lingering marks! ;-) Serious time: What do you mean by restrict certain kinds of SQL by id? Do you mean no updates or deletes? Or are you looking for something more subtle? The former can be controlled by table and column privileges. Tracking CPU time by session: select s.sid, s.username, sum( t.cpu_time ) from syssessions s, sysrstcb r, systcblst t where s.sid = 1234567 and s.sid = r. sid and r.tid = t.tid group by 1, 2 ; 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 Mon, Jul 27, 2009 at 4:11 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Good afternoon, > > Well, I get to enjoy one of the unfortunately aspects of Systems and > Database > administration. I get to content with programmers that write in-house > applications in a way that does not take into system resources and table > lock > contention. So, after a long time of adding resources to the DB server, I > have > learned (yet again) that the more you give, the more programmers will use. > So, > I've had to restart my Informix engine (yet again) in my production > environment because of careless and disconcerted programming that gobbles > up > all my system resources. If you have been down the dark alley and have > survived and found tools to help, I would be very interested in your advice > with: > > I am currently on Informix IDS 10.00.FC5 for HP-UX platform. > > I imagine the answers are probably No, but I still have to ask: > > Is there a way to restrict certain kinds of SQL operations based on User ID > with in Informix? > > When I have an Informix SQL processes, how can I tell how much CPU/VPU the > processes is consuming if I know the process ID? Is there an onstat command > for that? I don't seem to get much CPU usage out of onstat -g ses, sql, or > ath. > > How does your organization restrict or regulate programmers, development, > or > how do you coordinate with them to prevent resource contentions on your DB > systems? > > Thanks for any guidance or input you might have. > > Sincerely, > > Jonathan B. Smaby > Pomona College > phone: (909) 621-8506 > email: jonathan.smaby@pomona.edu > > <%3c!--%20Facebook%20Badge%20START%20--%3e%3ca%20href=%22http:/ > www.facebook.com/jonathan.smaby%22%20title=%22Jonathan%20B.%20Smaby%22%20target= %22_TOP%22%20style=%22font-family:%20&quot;lucida%20grande&quot > ;,tahoma,verdana,arial,sans-serif;%20font-size:%2011px;%20font-variant:%20normal ;%20font-style:%20normal;> > > ------------------------------------------------------------- > This message has been scanned by Postini anti-virus software. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a72ed5acf5046fb5de3f

On Mon, Jul 27, 2009 at 13:16, Obnoxio The Clown<obnoxio@serendipita.com> wrote: > Jonathan Smaby wrote: >> Well, I get to enjoy one of the unfortunately aspects of Systems and Database >> administration. I get to content with programmers that write in-house >> applications in a way that does not take into system resources and table lock >> contention. So, after a long time of adding resources to the DB server, I have >> learned (yet again) that the more you give, the more programmers will use. So, >> I've had to restart my Informix engine (yet again) in my production >> environment because of careless and disconcerted programming that gobbles up >> all my system resources. If you have been down the dark alley and have >> survived and found tools to help, I would be very interested in your advice >> with: >> >> I am currently on Informix IDS 10.00.FC5 for HP-UX platform. >> >> I imagine the answers are probably No, but I still have to ask: >> >> Is there a way to restrict certain kinds of SQL operations based on User ID >> with in Informix? > > I-Spy. > >> When I have an Informix SQL processes, how can I tell how much CPU/VPU the >> processes is consuming if I know the process ID? Is there an onstat command >> for that? I don't seem to get much CPU usage out of onstat -g ses, sql, or >> ath. > > I think SQLIDEBUG with do this, but that's using Cadillac to swat a fly. I don't think SQLIDEBUG is the way to do that. It turns on SQLI protocol logging which does not really include much in the way of CPU usage. Since it does log everything, if there's information passed back in things like the SQLCA.SQLERRD array, then that could be tracked, but it is somewhat indirect at best. I'm not convinced there is a good way to get per-session CPU consumption information out of IDS. >> How does your organization restrict or regulate programmers, development, or >> how do you coordinate with them to prevent resource contentions on your DB >> systems? > > I have a baseball bat and I'm not afraid to use it. ;o) One of them works, especially when there is a shortage of jobs and an abundance of staff. -- 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. Charles de Gaulle - "The better I get to know men, the more I find myself loving dogs." - http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html

>> When I have an Informix SQL processes, how can I tell how much CPU/VPU the >> processes is consuming if I know the process ID? Is there an onstat command >> for that? I don't seem to get much CPU usage out of onstat -g ses, sql, or >> ath. In version 11 you can use onstat -g cpu (or look in sysmaster:systcblst) to see how much virtual cpu time a threads has consumed. You can sum all threads for a give session and have a total amount of cpu time a session has consumed. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 07/28/2009 05:06:51 PM: > [image removed] > > Re: Herding Programmers with DB resources [16526] > > Jonathan Leffler > > to: > > ids > > 07/28/2009 05:08 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > On Mon, Jul 27, 2009 at 13:16, Obnoxio The Clown<obnoxio@serendipita.com> > wrote: > > Jonathan Smaby wrote: > >> Well, I get to enjoy one of the unfortunately aspects of Systems and > Database > >> administration. I get to content with programmers that write in-house > >> applications in a way that does not take into system resources and table > lock > >> contention. So, after a long time of adding resources to the DB server, I > have > >> learned (yet again) that the more you give, the more programmers will use. > So, > >> I've had to restart my Informix engine (yet again) in my production > >> environment because of careless and disconcerted programming that gobbles > up > >> all my system resources. If you have been down the dark alley and have > >> survived and found tools to help, I would be very interested in > your advice > >> with: > >> > >> I am currently on Informix IDS 10.00.FC5 for HP-UX platform. > >> > >> I imagine the answers are probably No, but I still have to ask: > >> > >> Is there a way to restrict certain kinds of SQL operations based > on User ID > >> with in Informix? > > > > I-Spy. > > > >> When I have an Informix SQL processes, how can I tell how much CPU/VPU the > >> processes is consuming if I know the process ID? Is there an > onstat command > >> for that? I don't seem to get much CPU usage out of onstat -g ses, sql, or > >> ath. > > > > I think SQLIDEBUG with do this, but that's using Cadillac to swat a fly. > > I don't think SQLIDEBUG is the way to do that. It turns on SQLI > protocol logging which does not really include much in the way of CPU > usage. Since it does log everything, if there's information passed > back in things like the SQLCA.SQLERRD array, then that could be > tracked, but it is somewhat indirect at best. > > I'm not convinced there is a good way to get per-session CPU > consumption information out of IDS. > > >> How does your organization restrict or regulate programmers, development, > or > >> how do you coordinate with them to prevent resource contentions on your DB > >> systems? > > > > I have a baseball bat and I'm not afraid to use it. ;o) > > One of them works, especially when there is a shortage of jobs and an > abundance of staff. > > -- > 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. > Charles de Gaulle - "The better I get to know men, the more I find > myself loving dogs." - > http://www.brainyquote.com/quotes/authors/c/charles_de_gaulle.html > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >