Hi John,
My thoughts on this. As Art says this opens a can of worms.
I like the SMI interface and it's great for programming. Jacob has already mentioned Perl DBI: we have this installed everywhere and make extensive use of it. In programmes is much easier to use SMI than parse the output of onstat, which as you point out cannot be changed without breaking stuff. SMI is great for pulling metrics into monitoring tools.
On the command line, 'onstat' is quick and easy to use. If SMI were the only option I would need a script library to quickly access information to save typing long SQL queries, something every decent Oracle DBA I know has to hand.
I think 'dbaccess' lets things down a bit. In my opinion it could or should be improved and give more control over formatting to the user. Often I find myself casting output to shorter varchars to keep the output under 80 characters wide and avoid dbaccess pivoting the output. It should also be able to run scripts without having to exit the programme and pass the script name as an argument.
We have found a couple of specific examples where an SMI implementation is not a good choice:
We have a monitor which checks for a high number of threads in a ready state. This used to use onstat and worked brilliantly. A colleague re-implemented it using SMI for good reasons (which I forget) and it also works fine except exactly when we need it, at which point the SMI SQL is queued for too long and the check exceeds a timeout and returns no data.
Queries on sysscblst/syssessions can be expensive. There is an APAR about this:
https://www.ibm.com/support/pages/apar/IT34183Ben.
------------------------------
Benjamin Thompson
------------------------------
Original Message:
Sent: Mon February 22, 2021 08:38 AM
From: John Lengyel
Subject: Spot the difference
Hi all. As has been reported here, in xC6 and up you'll need to set the ONSTAT_LEGACY environment variable to get back the original header. (Any value will do at the moment.) My apologies for the problem. I would anticipate that changing the meat of an onstat output could break some scripts, but I confess I did not expect a header change to throw so many people off. Lessons have been learned...
I have a question for the group. Is there something lacking with the SMI pseudo tables that makes parsing onstat output still so indispensable to your operations? There are pros and cons to onstat and SMI obviously. onstat runs faster when you need to gather info very quickly and it has essentially zero impact on the server. For temporary needs it's easier to dash off a script that parses onstat output than to write an esql/c program or the like. But with SMI you can gather only the info you really need, slice it, dice it, and aggregate it with SQL, stored procedures, or in the client... For long-term day-to-day operations I would have thought that by now shell scripts were perceived as not powerful enough or solid enough.
I'm clearly wrong about that, at least in some cases, so I'm wondering if there's anything we can do on our end to make SMI a more attractive alternative.
Thanks.
-jc
------------------------------
John Lengyel
Original Message:
Sent: Tue December 22, 2020 12:12 PM
From: Hrvoje Zokovic
Subject: Spot the difference
> onstat -
IBM Informix Dynamic Server Version 14.10.FC4W1DE -- On-Line -- Up 10 days 04:18:20 -- 334828 Kbytes
>
> onstat -
IBM Informix Dynamic Server Version 14.10.FC5DE -- On-Line -- Up 00:01:54 -- 185996 Kbytes
2020-12-22 18:08:01
>
Is this feature or bug?
Hrvoje
------------------------------
Hrvoje Zokovic
------------------------------
#Informix