anyone using SolarWinds for monitoring?
Mark Collins found SolarWinds APM procedures in sysmaster using SYSTEM sleep failed over ODBC with a system command error, though they worked in dbaccess. David Link suspected the environment; Art Kagel and Paul Watson advised avoiding SYSTEM sleep, giving an SQL spin loop and a C UDR. Cause: the service account had no home directory and /bin/false shell. Mark switched to the loop.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Informix 14.10.FC7W1 on RHEL 8.6, SolarWinds APM
2020.2.6.50231
So the company is using SolarWinds to monitor several Windows servers and SQL Server databases, using the APM (Application Performance Monitoring) and DPA (Database Performance Analyzer) tools, and there is an effort to expand that monitoring to include our Informix/Red Hat servers. The stated goal is to have a uniform monitoring solution across the enterprise.
DPA works with several databases, but not with Informix. That leaves APM, which
does
claim to support Informix.
The manner in which APM does its monitoring is to use an ODBC connection to gather statistics. The statistics are gathered via stored procedures that SW requires to be installed in sysmaster. An example of one such procedure is:
CREATE PROCEDURE APM_L_REQ ()
RETURNING DECIMAL(8,2);DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'lockreqs';
SYSTEM "sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'lockreqs';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;
Yes, I am aware that the 'SYSTEM "sleep 10"' will lock the CPU VP the entire time that the 'sleep 10' is running. Not happy about that, but it is what it is.
The odd thing is, it fails when invoked from SW. I can run this procedure via ServerStudio, which uses a JDBC connection, and I can run it in dbaccess on the server running the instance. But when the SW process attempts to run it, they receive an error message stating "DbException caught". The full log message is:
2022-09-07 14:06:19,131 [69] [C6818] [WU:(null)] DEBUG SolarWinds.APM.Probes.OdbcQAProbe - ODBC connection string used: 'Driver={IBM INFORMIX ODBC DRIVER};Host=192.168.26.200;Server=test_srvr;Service=test_inst;Protocol=onsoctcp;Database=sysmaster;Uid=;Pwd=;'
2022-09-07 14:06:19,131 [69] [C6818] [WU:(null)] DEBUG SolarWinds.APM.Probes.OdbcQAProbe - SQL query to execute: 'EXECUTE PROCEDURE APM_L_REQ ();'
2022-09-07 14:06:19,131 [69] [C6818] [WU:(null)] DEBUG SolarWinds.APM.Probes.OdbcQAProbe - Start SQL query execution
2022-09-07 14:06:19,162 [69] [C6818] [WU:(null)] ERROR SolarWinds.APM.Probes.OdbcQAProbe - DbException caught.
System.Data.Odbc.OdbcException (0x80131937): ERROR [HY000] [Informix][Informix ODBC Driver][Informix]The system command cannot be executed or it exited with a non-zero status.
I modified their procedure to add a 'set debug file' and an 'on exception' block, writing the details of the error to a file so we could try isolating the problem. The SQLCODE = -668, and ISAM code = -21565.
I am unable to explain why the procedure works in dbaccess and ServerStudio, but not in SolarWinds.
Our SolarWinds admin has opened a ticket with the vendor, but from some of the previous interactions when trying to set this up, I'm not expecting much assistance from them.
Any thoughts? Suggestions on where to go next? Yes, I know - Informix HQ will provide much better monitoring, without holding CPU VPs, etc., but for now, I have to get this working.
------------------------------
mark collins
------------------------------
#Informix
Mark,
Would it be that when you run it from command line, dbaccess//ServerStudio you are getting the environment setup? When they execute the procedure you maybe getting the default profile. PATH settings, maybe?
David,
I did think about that after I posted, so I re-created the procedures specifying the full path to the sleep command. So it now reads:
CREATE PROCEDURE APM_L_REQ ()
RETURNING DECIMAL(8,2);DEFINE x,y,res DECIMAL(8,2);
SELECT value INTO x FROM sysprofile WHERE name = 'lockreqs';
SYSTEM "/usr/bin/sleep 10";
SELECT value INTO y FROM sysprofile WHERE name = 'lockreqs';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;
We're still getting same error.
------------------------------
mark collins
------------------------------
I would just not use "sleep" but rather spin on sysshmvals.sh_curtime, shelling out is more expensive than just locking up the CPU VP. This should be able to release the VP. To simplify the recoding of the many sensors, just write a sleep function the same way and call it inside each:
CREATE PROCEDURE APM_L_REQ ()
RETURNING DECIMAL(18,2);DEFINE x,y,res DECIMAL(18,2);
DEFINE syst1, syst2 bigint;
SELECT value INTO x FROM sysprofile WHERE name = 'lockreqs';
LET syst2 = 0;
select sh_curtime into syst1 from sysshmvals;
while (syst2 < 5)
select (sh_curtime - syst1)::bigint into syst2 from sysshmvals;
end while;
SELECT value INTO y FROM sysprofile WHERE name = 'lockreqs';
LET res = (y - x) / 10;
RETURN res;
END PROCEDURE;
create procedure sleep( inp int );DEFINE syst1, syst2 bigint;
LET syst2 = 0;
select sh_curtime into syst1 from sysshmvals;
while (syst2 < inp)
select (sh_curtime - syst1)::bigint into syst2 from sysshmvals;
end while;
return;
end procedure;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog:
http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Do not use sleep - I posted a UDR sleep the other month that will not hurt your system
It probably works in dbaccess etc cos of the env, change the sleep to a bash script and that runs env > file and exit 0
Cheers
Paul
Paul Watson
Oninit LLC
+1-913-387-7529
www.oninit.com
Oninit® is a registered trademark of Oninit LLC
Found the issue. This is running under a "service account". Because we didn't expect this service account to log in to the server, we had set it up to have no home directory and also set the login shell to /bin/false. The SYSTEM call requires that the user be able to log on. ------------------------------ mark collins ------------------------------
Paul - can you tell me where that UDR is located? I'd like to try that as an alternative. I cringed when I saw that they had used "SYSTEM 'sleep'" in their procedures. ------------------------------ mark collins ------------------------------
Mark, I'm not an ODBC expert but when I set up a quick connection to Informix just to see if it worked, I noticed there is a trace file option in the odbc.ini file. You could try adding something like the following to the odbc.ini file on the system trying to make the ODBC connection to Informix. Then check the trace file to see if it gives any more info. If you have access to syslog, I also check it on both the monitoring box and the one running Informix. ....................cut here.................... [ODBC] ; ; Trace file Section ; Trace=0 TraceFile=/tmp/odbc-trace.out InstallDir=/opt/informix ; If TRACEDLL does not work, try full path /opt/informix/lib/cli/idmrs09a.so TRACEDLL=idmrs09a.so ....................cut here.................... scot
Art, Great! I like it. I suspect that this is very similar to the procedure that Paul was referring to? I've created the sleep() procedure, and changed all of the "SYSTEM 'sleep 10'" calls to "CALL sleep(10);". Doing this also allows me to reset the login shell to /bin/false as a means of preventing that account from being used to get access at the OS level. Thanks. ------------------------------ mark collins ------------------------------
Similar - I just do it in C, and avoid any sql overhead not that the sql is that expensive Cheers Paul Paul Watson Oninit LLC +1-913-387-7529 www.oninit.com Oninit® is a registered trademark of Oninit LLC
Mark
Note it takes ms not seconds
void oni_sleep(mi_integer ms)
{
struct timespec ts;
int i;
long wait;
clock_getres(CLOCK_MONOTONIC, &ts);
clock_gettime(CLOCK_MONOTONIC, &ts);
for(i=ms;i--;)
{
wait = (ts.tv_sec * 1000000000L + ts.tv_nsec) + 1000000;
ts.tv_sec = wait / 1000000000L;
ts.tv_nsec = wait % 1000000000L;
clock_nanosleep(CLOCK_MONOTONIC, TIMER_ABSTIME, &ts, NULL);
mi_yield();
}
}
begin work;
drop procedure if exists oni_sleep(int);
create procedure oni_sleep (int)external name "$INFORMIXDIR/extend/Oninit.1.0/oninit.bld(oni_sleep)" language c;
grant execute on procedure oni_sleep (int) to public;commit work;
What about:
execute function sysmaster:yieldn(10)
------------------------------Øyvind Gjerstad
Developer/Architect
PostNord AS
------------------------------
Nothing if it is available and you are happy with second granularity
I am not familiar with yieldn(). Is it documented anywhere? What controls whether it is available or not? Version? Specific datablade present? For this application, granularity down to the second is sufficient. But I will save your C procedure for cases that require millisecond granularity. ------------------------------ mark collins ------------------------------