IDS Forum

Re: Audit IDS 11.50 Sessions & SQL jobs

Posted By: Fernando Nunes
Date: Tuesday, 15 March 2011, at 8:21 p.m.

In Response To: Audit IDS 11.50 Sessions & SQL jobs (Jonathan Smaby)

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

Messages In This Thread

• Audit IDS 11.50 Sessions & SQL jobs
  Jonathan Smaby -- Tuesday, 15 March 2011, at 7:54 p.m.
  • Re: Audit IDS 11.50 Sessions & SQL jobs
    Fernando Nunes -- Tuesday, 15 March 2011, at 8:21 p.m.
    • Re: Audit IDS 11.50 Sessions & SQL jobs
      KARL OLIVER -- Wednesday, 16 March 2011, at 10:09 p.m.
      • Re: Audit IDS 11.50 Sessions & SQL jobs
        Madison Pruet -- Thursday, 17 March 2011, at 1:09 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.