Expand all | Collapse all

Spot the difference

  • 1.  Spot the difference

    Posted Tue December 22, 2020 12:13 PM

    > 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 Zokovic

  • 2.  RE: Spot the difference

    Posted Tue December 22, 2020 12:35 PM

    It is feature

    From xC5 onwards, the header also includes a datetime stamp in the format: <year>-<month>-<day> <hour>:<minute>:<second>

    IBM Informix Dynamic Server Version 14.10.FC5 -- On-Line -- Up 00:00:22 -- 54180 Kbytes
    2020-12-17 19:15:15

    Hrvoje Zokovic

  • 3.  RE: Spot the difference

    Posted Wed December 23, 2020 01:38 AM
    Edited by SangGyu Jeong Wed December 23, 2020 01:39 AM

    I also installed 14.10.fc5 to see this change.

    It is nice to be able to leave a timestamp without a command like date in the onstat result. :)

    SangGyu Jeong
    Software Engineer
    Seoul Korea, Republic of

  • 4.  RE: Spot the difference

    Posted Wed December 23, 2020 05:06 AM
    I created a case and IBM engineer told me this is a new feature in xC5.

    I don't understand the reason to do this, but it seams this new timestamps is going to stay even it broke most of our parsing shells :-(

    Vicente Salvador

  • 5.  RE: Spot the difference

    Posted Wed December 23, 2020 08:18 AM

    That was my initial thought. I like the feature, but that's going to break a bunch of scripts out there. [Thankfully I don't think any of mine are impacted; our scripts only ever run onstat - to check the return code, and other onstat scripts are already parsed with grep and similar to look for specific things.]


  • 6.  RE: Spot the difference

    Posted Wed December 23, 2020 08:22 AM
    A bit like  the recent DBINFO change that broke lots of scripts.


    Paul Watson
    Oninit LLC
    Oninit®️ is a registered trademark of Oninit LLC

  • 7.  RE: Spot the difference

    Posted Wed December 23, 2020 02:48 PM

    Hi Paul.

    > A bit like  the recent DBINFO change that broke lots of scripts.

    I must have missed that one.  What change was made to DBINFO? I'm not that heavy a user thereof but I wouldn't mind being forewarned.

    Jacob Salomon
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra

  • 8.  RE: Spot the difference

    Posted Wed December 23, 2020 03:07 PM

    IIRC correctly, it isn't DBINFO itself that changed, but rather the system catalogs. They created some new entries with partnum values below 1048576. So if you don't filter those out, DBINFO('dbspace',partnum) will throw an error, saying that it can't decode the partnum value.


  • 9.  RE: Spot the difference

    Posted Wed December 23, 2020 07:13 PM
    Thanks Tom.

    My old scripts, pre-dating the "dbspace" option for dbinfo (or at least my knowledge thereof) use mod(partnum, 1048576) joined to sysdbspaces.dbsnum so my queries would have skipped such pseudo-partitions.

    But nice to be aware of the issue.

    - Jacob S

  • 10.  RE: Spot the difference

    Posted Tue December 29, 2020 04:45 PM
    Yes Vincente, I agree. IMHO this feature should not be default - it can be turned on e.g. with env variable.
    Informix was known for consistency with older versions and new features (almost all) can be turned on with oncofig settings or env variable.

  • 11.  RE: Spot the difference

    Posted 8 days ago

    Also raised a case with this response:

    This problem is already fixed with below APAR in 14.10.xC6 (tentatively scheduled for 22-April-2021):

    Have requested a special build for Linux x86_64 as we need it sooner.

    Doug Lawry
    Oninit Consulting

  • 12.  RE: Spot the difference

    Posted 7 days ago
    That is cool - only I would prefer env variable which can be used to "enable onstat changes" and default would be without variable - without changes :)

    Hrvoje Zokovic

  • 13.  RE: Spot the difference

    Posted 5 days ago
    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.



    John Lengyel

  • 14.  RE: Spot the difference

    Posted 5 days ago

    Now you've opened a can of worms. Suggested improvements to the SMI tables:

    • This one is not related to onstat being better, but there needs to be more complete reporting of readahead. Not all types of pages are included in either the pages read or pages used by readahead which makes it impossible to tune AUTO_READAHEAD! I used to be able to determine roughly how much to tune readahead up or down depending on the workload and my RA Utilization metric. But that calculation is useless now and I am left with pure trial and error and guesswork.
    • There are values included in the sysactptnhdr that are not included in sysptnhdr and not all of those are valid only for active tables.
    • The live onstat -g cac output is not readily accessible in sysmaster.
    • The output of onstat -g defragment and similar runtime status options.
    • Running archive status.

    Oh, separate issue, some commands (onstat -g ioh for example) are not listed in the online help output nor in the documentatin online.
    That's all I can think of off the top of my head.


    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.

  • 15.  RE: Spot the difference

    Posted 5 days ago
    Hi John.

    Both the headers and "the meat" have impact on scripts, as you have noted.  As to your other question:
    Is there something lacking with the SMI pseudo tables that makes parsing onstat
    > output still so indispensable to your operations?

    Here'e my $0.02(US) on that question:

    I do most of my scripting in Perl, heavily relying on Jonathan Leffler's DBD::Informix.  Last year I discovered, to my dismay, that not everyone has that module installed, or even the more general DBI.  In that situation I was able to nudzh some senior admin into installing it on some of their servers (I'm pretty good at nudzhing :-) but it remains that a large enough pool of clients may not have those modules, making it difficult to nicely present the results of the SMI query.  And not everyone has 4GL (with its report writer) or its replacement (whazzit's name?) either.

    So yes, there should be a well documented mapping between anything you can get directly from each onstat option and its equivalent SMI "table".

    +----- Jacob Salomon --------------------------------------------------+
    | The first lesson of economics is scarcity: there is never enough of  |
    | anything to fully satisfy all those who want it. The first lesson of |
    | politics is to disregard the first lesson of economics.              |
    +------------------------------ Thomas Sowell (Is Reality Optional?) --+

  • 16.  RE: Spot the difference

    Posted 5 days ago
    Three thinks I'm currently monitoring and not a clear and easy way to obtain from SMI tables are:

    onstat -g iof

    To monitor current open files by server

    onstat -g rea
    Easy way to monitor ready process queue

    onstat -g act
    Easy way to monitor active process queue

    I know this two last commands can be obtained from SMI but it's hard and unclear to do all the joins required. Much easier to just execute onstat command. Same for all CDR/ER and HDR stuff


    Vicente Salvador

  • 17.  RE: Spot the difference

    Posted 4 days ago
    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:


    Benjamin Thompson

  • 18.  RE: Spot the difference

    Posted 4 days ago
    @John Lengyel,
    I know I might be asking for much, but how about better documentation on ALL the tables in the sysmaster? Since I'm feeling a bit needy, how about IBM publishing the OFFICIAL SMI queries for each onstat ​command? ^_^


    Ramon Rey

  • 19.  RE: Spot the difference

    Posted 5 days ago
    This is exactly the feedback I'm looking for. It's the kind of thing we ought to be talking about at IIUG, but of course, Armageddon. Keep it up please. I'll process and respond asap.

    John Lengyel

  • 20.  RE: Spot the difference

    Posted 5 days ago

    At last check (admittedly it's been a while) we couldn't get the output of 'onstat –g opn' from the SMI tables. So I had to write a pretty convoluted script to see who's currently got a table open (but isn't holding any record locks). I have to parse onstat –g opn, then load it into a temp table so it can be joined against systabnames, syssessions and sysuserthreads. Would be a lot easier if I could just join sysopentab (or whatever).