Audit IDS 11.50 Sessions & SQL jobs
Good afternoon fellow DBA's. I have a question and looking for suggestions now how to audit all sessions & SQL jobs submitted to an Informix IDS 11.50 engine running under Linux. I have a user that swears up and down they "didn't do anything" in the system, however, IDS 11.50.FC6 frequently experiences unexpected shutdowns due to bad queries and Cartesian products. The "af" crash files aren't providing enough information, I'm curious if you are auditing your Db sessions and SQL jobs submitting using a 3rd party tool, or OAT. I'm new to IDS 11.50, just upgraded from 10.00 last week. I'm not looking to beat the guy with a stick, but rather give the user feedback on specific SQL jobs that I can prove to him that are broken / malformed. Thanks for any suggestions. Jonathan B. Smaby<http://www.facebook.com/photo.php?pid=392289&l=a2b304ccd3&id=1000008613412 48> Pomona College phone: (909) 621-8506 email: jonathan.smaby@pomona.edu http://wwww.facebook.com/JonathanSmaby ------------------------------------------------------------- This message has been scanned by Postini anti-virus software.
On Tue, Mar 15, 2011 at 11:54 PM, Jonathan Smaby <Jonathan.Smaby@pomona.edu>wrote: > Good afternoon fellow DBA's. > > I have a question and looking for suggestions now how to audit all sessions > & > SQL jobs submitted to an Informix IDS 11.50 engine running under Linux. I > have > a user that swears up and down they "didn't do anything" in the system, > however, IDS 11.50.FC6 frequently experiences unexpected shutdowns due to > bad > queries and Cartesian products. The "af" crash files aren't providing > enough > information, I'm curious if you are auditing your Db sessions and SQL jobs > submitting using a 3rd party tool, or OAT. I'm new to IDS 11.50, just > upgraded > from 10.00 last week. I'm not looking to beat the guy with a stick, but > rather > give the user feedback on specific SQL jobs that I can prove to him that > are > broken / malformed. > > Thanks for any suggestions. > This is a complex question, or at least from my point of view requires a complex answer. In short: I don't have a "perfect" way to do that. But before considering how to do it, I think you should settle on WHAT to do... First, if the system "shuts down", meaning it crashes, you are hitting a bug. Should consider contacting IBM technical support. I'm not saying a user can't stress a system. But it should never crash. It can become slow, maybe hang due to some long transaction, exhaust some resources, but it should NEVER crash. Then you say the AFs are not providing enough information. Which is weird, since the session causing the crash should be captured, including the session statement. You could even generate a shared memory dump that can allow tech support to due more deep investigations (if needed) As for ways to capture the SQL, I can remember 3. Each may have some advantages and disadvantages: 1- Use a "user_crash".sysdbopen() stored procedure to activate SET EXPLAIN/SET EXPLAIN FILE TO... This will capture queries for the user session into the specified file. Check the manuals for SET EXPLAIN, SET EXPLAIN FILE TO and SYSDBOPEN 2- Activate SQLTRACE facility, specified just the user. This can be done in OAT This will store the user queries into the specified memory buffer. But if your instance is crashing the buffer will be lost. 3- Use SQLIDEBUG. This is a low level tracing facility normally used by technical support. I'm not sure if it's documented in the manuals, but I believe so. Try searching for it in the information center. You will require a CSDK component called sqliprint to process the generated information. But I would start by technical support. The instance cannot crash, and it doesn't matter what the users are doing (I'm not saying it never happens, just saying it cannot happen, so if it does you're dealing with a bug or some kind of corruption....) Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001636765b9923c72f049e8e8712
I am running create rep from here output SERVER ID STATE STATUS QUEUE CONNECTION CHANGED ----------------------------------------------------------------------- mafdata_uat_akl 11 Active Connected 0 Mar 16 13:18:41 mafdata_uat_wlg 12 Active Local 0 hoping to replicate to here SERVER ID STATE STATUS QUEUE CONNECTION CHANGED ----------------------------------------------------------------------- res_uat_rep_akl 19 Active Connected 0 Mar 16 11:43:45 res_uat_rep_wlg 18 Active Local 0
These look like two distinct domains Is that the case? From: "KARL OLIVER" <karl.oliver@maf.govt.nz> To: ids@iiug.org Date: 03/16/2011 08:10 PM Subject: Re: Audit IDS 11.50 Sessions & SQL jobs [23134] Sent by: ids-bounces@iiug.org I am running create rep from here output SERVER ID STATE STATUS QUEUE CONNECTION CHANGED ----------------------------------------------------------------------- mafdata_uat_akl 11 Active Connected 0 Mar 16 13:18:41 mafdata_uat_wlg 12 Active Local 0 hoping to replicate to here SERVER ID STATE STATUS QUEUE CONNECTION CHANGED ----------------------------------------------------------------------- res_uat_rep_akl 19 Active Connected 0 Mar 16 11:43:45 res_uat_rep_wlg 18 Active Local 0 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.