Monitoring SPL Executions?

Solaris 10 Informix 12.10FC3 Today I was asked if we can monitor the execution of SPLs. Specifically, the requester wants a list of all SPLs executed in the preceding 24 hours, and the number of times each was executed. I didn't see any Server Studio reports that addressed this, nor any "precooked" 'onstat' capability. How might this be accomplished? Perhaps with a script that uses 'onstat -g prc' that runs at the beginning and end of the period, and then parse out the SPL names and the difference in the "hit" column?? Thank you for any thoughts. DG

Add a counter/monitor at the top of each SPL Cheers Paul Paul Watson Oninit www.oninit.com Tel: +1 913 674 0360 Cell: +1 913 387 7529 Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. > On Sep 20, 2017, at 16:47, DAVID GROVE <david.grove@alaska.gov> wrote: > > Solaris 10 > Informix 12.10FC3 > > Today I was asked if we can monitor the execution of SPLs. > > Specifically, the requester wants a list of all SPLs executed in the preceding > 24 hours, and the number of times each was executed. > > I didn't see any Server Studio reports that addressed this, nor any > "precooked" 'onstat' capability. > > How might this be accomplished? > > Perhaps with a script that uses 'onstat -g prc' that runs at the beginning and > end of the period, and then parse out the SPL names and the difference in the > "hit" column?? > > Thank you for any thoughts. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. --Apple-Mail-37D1D027-A6D0-4DE1-A83B-9D0A7BCBDF0B

Thank you for such a quick response. No question that would work. I'm just a lazy cuss, and am a bit reluctant to have to go and edit 1,500 SPL UDRs. Regards, DG

Activate auditing. Set mask _default to EXSP Arrange for some space, and then parse the audit files... Be careful.. I know of a busy system that last time I checked executed ~200 SPLs per second... Regards. On Wed, Sep 20, 2017 at 11:03 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > Thank you for such a quick response. > > No question that would work. > > I'm just a lazy cuss, and am a bit reluctant to have to go and edit 1,500 > SPL > UDRs. > > Regards, > > DG > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Ahhh. Auditing. Of course. About time I dip into that. Thanks for the caution. DG

That is want scripting is for :) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVID GROVE Sent: Wednesday, September 20, 2017 5:03 PM To: ids@iiug.org Subject: Re: Monitoring SPL Executions? [39946] Thank you for such a quick response. No question that would work. I'm just a lazy cuss, and am a bit reluctant to have to go and edit 1,500 SPL UDRs. Regards, DG **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

This is what awk and perl exist for. > On 20 Sep 2017, at 23:03, DAVID GROVE <david.grove@alaska.gov> wrote: > > Thank you for such a quick response. > > No question that would work. > > I'm just a lazy cuss, and am a bit reluctant to have to go and edit 1,500 SPL > UDRs. > > Regards, > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Although that's true, I'd stick to my suggestion. Activating DEBUG in every procedure would be a greater overhead and would require the procedures to be rebuilt (which is not good in a live system). onaudit -l 1 -p /some/path/with/space -s 50000000 -e 0 onaudit -c -u _default -e EXSP and then wait and then look at the file sgenerated in /some/path/with/space (each should have 50MB). There will be a line for each procedure execution... and the procid and database will be there... Regards (test the above in non production first of course... I just wrote it without checking every detail...) On Thu, Sep 21, 2017 at 12:23 PM, Spokey Wheeler <spokey.wheeler@gmail.com> wrote: > This is what awk and perl exist for. > > > On 20 Sep 2017, at 23:03, DAVID GROVE <david.grove@alaska.gov> wrote: > > > > Thank you for such a quick response. > > > > No question that would work. > > > > I'm just a lazy cuss, and am a bit reluctant to have to go and edit 1,500 > SPL > > UDRs. > > > > Regards, > > > > DG > > > > > > > ************************************************************ > ******************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...

Touche

Thank you, Fernando. 'onaudit' seems to be just what we need. The number of SPL calls per second is not a problem. But, there is something I do not understand. We have created only one mask, with only one entry (see below): informix@ifmx-prod-anc>onaudit -o -y Onaudit -- Audit Subsystem Configuration Utility _default - EXSP informix@ifmx-prod-anc> Yet, there are occasional entries in the audit log file from another kind of event. Immediately below are two rows from the audit log file: ONLN|2017-09-22 07:29:29.000|doc-sadc-acoms4.akdoc.local|1922|prodanctli|mlrodger|-1:STSN ONLN|2017-09-22 07:29:29.000|doc-sadc-acoms4.akdoc.local|1922|prodanctli|mlrodger|-1:STSN Why do "STSN" events appear in the audit log, when onaudit is setup to monitor only EXSP events? Thank you. David Grove

What value do you have for ADTMODE? Values greater than 1 audit events other than what's listed in your audit masks for DBSSO/AAO/DBSA, including "Start New Session" (STSN). -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVID GROVE Sent: Friday, September 22, 2017 11:57 AM To: ids@iiug.org Subject: Re: Monitoring SPL Executions? [39971] Thank you, Fernando. 'onaudit' seems to be just what we need. The number of SPL calls per second is not a problem. But, there is something I do not understand. We have created only one mask, with only one entry (see below): informix@ifmx-prod-anc>onaudit -o -y Onaudit -- Audit Subsystem Configuration Utility _default - EXSP informix@ifmx-prod-anc> Yet, there are occasional entries in the audit log file from another kind of event. Immediately below are two rows from the audit log file: ONLN|2017-09-22 07:29:29.000|doc-sadc-acoms4.akdoc.local|1922|prodanctli|mlrodger|-1:STSN ONLN|2017-09-22 07:29:29.000|doc-sadc-acoms4.akdoc.local|1922|prodanctli|mlrodger|-1:STSN Why do "STSN" events appear in the audit log, when onaudit is setup to monitor only EXSP events? Thank you. David Grove **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you for the idea... But, ADTMODE = 1 informix@ifmx-prod-anc>onaudit -c Onaudit -- Audit Subsystem Configuration Utility Current audit system configuration: ADTMODE = 1 ADTERR = 0 ADTPATH = /opt/informix/aaodir ADTSIZE = 50000000 Audit file = 0 ADTROWS = 0 informix@ifmx-prod-anc>

Your suggestion to use onaudit seems to be working well. Hoever, here is another thing (other than the STSN events) about it that I don't understand. Currently, onaudit has been running for about3 1/2 hours, monitoring only EXSP events. The last item in each row of the audit log file for EXSP events is the procid. Now, we currently have about 150,000 rows in the audit log. But, about half of them have a procid = 0. Since there are no values of procid < 1, what does this mean? ("SELECT MIN(procid) FROM sysprocedures;" returns the value "1") I don't understand what it means for an EXSP event to have been triggered by an SPL with procid = 0. Thank you. DG

I see from the audit file entries that the STSN that are shown have a non-zero return code (the -1 right before the STSN) - so they failed. I still don't know why it's showing them, but it may be a clue. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVID GROVE Sent: Friday, September 22, 2017 12:47 PM To: ids@iiug.org Subject: Re: RE: Monitoring SPL Executions? [39973] Thank you for the idea... But, ADTMODE = 1 informix@ifmx-prod-anc>onaudit -c Onaudit -- Audit Subsystem Configuration Utility Current audit system configuration: ADTMODE = 1 ADTERR = 0 ADTPATH = /opt/informix/aaodir ADTSIZE = 50000000 Audit file = 0 ADTROWS = 0 informix@ifmx-prod-anc> **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Good point. A little 'greping' reveals that 100% of the time an STSN event is logged, it is with error code = -1. Comparing to the online.log, it seems that every one of the STSN = -1 events in the audit log corresponds to an online log entry of a failed password. I guess it's a nice touch. I just don't know why it happens. From my (new and limited) understanding of onaudit, it would seem to be an error in the way onaudit is functioning. But, from experience, I know that it is dangerous to make a conclusion too quickly. Most likely is that I simply do not understand onaudit thoroughly enough. DG

If it is any consolation, I get the same thing. When there's a failed connection (wrong password) the STSN event is recorded with a "-1", even though I have NO audit masks configured. e.g: ONLN|2017-09-22 13:22:41.000|pigriffin|16834|griffin|jill|-1:STSN Even if I set an _exclude mask for STSN, it still occurs. So this seems to be the standard behavior. Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAVID GROVE Sent: Friday, September 22, 2017 1:16 PM To: ids@iiug.org Subject: Re: RE: RE: Monitoring SPL Executions? [39976] Good point. A little 'greping' reveals that 100% of the time an STSN event is logged, it is with error code = -1. Comparing to the online.log, it seems that every one of the STSN = -1 events in the audit log corresponds to an online log entry of a failed password. I guess it's a nice touch. I just don't know why it happens. From my (new and limited) understanding of onaudit, it would seem to be an error in the way onaudit is functioning. But, from experience, I know that it is dangerous to make a conclusion too quickly. Most likely is that I simply do not understand onaudit thoroughly enough. DG **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

It is a bit of a consolation. Lets me believe I haven't done something incorrectly in setting up and using onaudit. This really is a useful utility. I don't know why I never paid any attention to it before. I know that auditing concerns are building up, so this will be used more and more. It appears to me that, if I am careful to not go overboard (particularly with respect to row level events), it is useful and not very much of a performance hit. (I say this qualitatively only, however.) The only thing that still has me scratching my head is why approximately half the events (remember only auditing EXSP events) in the audit log show the procid = 0. No SPL has a procid = 0, so what do those rows mean, and why are they there? DG