SQL command identification/source

I am involved in the operation of a large multi-user system with hundreds of users. The application code is written in Java and Infomix 4gl. I am able to monitor the current traffic and identify possibly suboptimal SQL statements (e.g. using onstat -g ...). However, I often have trouble tracking down the appropriate source file for a particular SQL statement. Respectively, I don't have this problem with 4GL, because "onstat -g ses XZX" prints the name of the 4ge binary. But all Java code is executed on the server under one virtual user. The Java code includes about several thousands Java files. How to identify the appropriate file for a particular SQL,or user session in such a large number? ------------------------------ Antonin Rozehnal ------------------------------

Hi Antonin. On a system we manage that uses Java heavily, we do get meaningful content in the "Program" field of "onstat -g ses session-id " or "onstat -g ses 0" for all sessions. Actual examples (deduplicated) are: /C:/Program%20Files%20(x86)/AGS/sentinel/lib/ifxjdbc.jarcom.agsltd.sentinel/lg /C:/Program%20Files/squirrel-sql-4.4.0/jdbc/ifxjdbc.jarjava.lang/Thread /opt/tomcat/webapps/MemberPortal/WEB-INF/lib/jdbc-4.10.6.20151104.jarjava.lang/Thread Thread[id:19, name:Catalina-startStop-1, path:/opt/tomcat/lib/ifxjdbc.jar] Thread[id:26, name:pool-2-thread-1, path:/C:/Sql/ifxjdbc.jar] Thread[id:416, name:HikariPool-5 connection adder, path:/C:/Program%20Files/Apache%20Software%20Foundation/Tomcat%208.5/webapps/FileMonitor/WEB-INF/lib/jdbc-4.50.7.1.jar] Thread[id:25405, name:HikariPool-5 connection adder, path:/opt/tomcat/webapps/FileMonitor/WEB-INF/lib/jdbc-4.50.7.1.jar] ar] Thread[id:25445, name:HikariPool-4 connection adder, path:/opt/tomcat/webapps/ClientPortal/WEB-INF/lib/jdbc-4.10.14.jar] Thread[id:104213, name:InformixHQ-Agent-pool-repository connection adder, path:/opt/IBM/InformixHQ/informixhq-agent.jar] That's enough to identify each application. This is on IDS 12.10.FC14. Try updating to the latest JDBC driver 4.50.JC10W1. You should do so anyway: Security Bulletin: IBM Informix JDBC Driver is susceptible to remote code execution https://www.ibm.com/support/pages/node/7099762 This might also help: https://www.ibm.com/docs/en/informix-servers/14.10?topic=products-client-label-environment-variable ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Hi Doug, thanks for your advice and experience. I didn't describe my situation accurately enough... All the Java code is packaged in a single Jar file and accesses the database through the Tomcat application server. Listing onstat -g ses .... looks like this: IBM Informix Dynamic Server Version 12.10.FC13XAQAEE -- On-Line -- Up 19 days 19:52:23 -- 10653984 Kbytessession effective #RSAM total used dynamic id user user tty pid hostname threads memory memory explain 24536 tomuser - - 11141482 myhost 1 94208 73000 off Program : Thread[id:3189, name:HikariPool-1 connection adder, path:file:/path/some_app.jar!/BOOT-INF/lib/jdbc-4.50.2.jar!/] tid name rstcb flags curstk status 25288 sqlexec 7000000238a ee28 Y--P--- 6224 cond wait netnorm - Memory pools count 2 name class addr totalsize freesize #allocfrag #freefrag 24536 V 70000002618f040 90112 20440 91 12 24536*O0 V 700000036588040 4096 768 1 1 name free used name free used overhead 0 6656 scb 0 144 opentable 0 5992 filetable 0 728 log 0 16536 temprec 0 2208 keys 0 176 gentcb 0 1592 ostcb 0 3472 sqscb 0 22576 hashfiletab 0 552 osenv 0 2368 sqtcb 0 9192 fragman 0 664 So all I find is that the problem is somewhere inside some_app.jar :-( ------------------------------ Antonin Rozehnal ------------------------------

My response would be that the only thing you can do would be to modify every one of those java source modules to insert a row into a table at startup identifying the module name and session id then have the sysdbclose() function for username tomuser delete the row when the session ends. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi, that is really not quite easy to solve. The JDBC driver does not pass any class information or stack info as far as I know. There could be object relational mappers involved, like hibernate , which do create the SQL internally and you would not find the SQL even in the source code directly, because it is constructed internally. If you assume that plain JDBC is being used, you might be able to identify the classes or at least the candidates by searching the .class files for string fragments. Since the jar is only a zip, you can unpack everything in the filesystem and either search the files for constants which contain parts of your sql or use a decompiler to reverse engineer the code. ( https://github.com/java-decompiler/jd-gui/releases could be a product to try) jd-gui is able to decompile java11 code at least, for higher versions, there are other tools available. If you are getting really unreadable code, the code is probably obfuscated. Then it is really complex to find the source class, because all classes would have very cryptic names. The Informix server side will not know which class is issuing the query, it only knows the class which started the connection, which would usually be some kind of datasource pooling software, the version of your driver, the user which was used for authentication and maybe the tty it is running in (for a Linux/Unix application server). I am not aware of any property you might pass to the driver in order to get this information to the server side. A java deleoper would maybe create some code with aspectj and intercept the method calls of IfmxStatement and IfmxPreparedStatement and print out the sql and stack trace dynamically to a file so you would get the exact call stack for each statement. (if the code is compiled with debug info also the line of code). This would require an experienced java developer and aspectj needs to be added as a startup javaagent to your tomcat container. Easiest solution is of course to have access to the source code which seems not to be the case for you. Good luck,

If you have access to the logging of your tomcat engine, maybe the JDBC debugging might help you. https://www.ibm.com/docs/en/informix-servers/14.10?topic=troubleshooting-debug-your-jdbc-api-program That article mentions that you would be able to get a trace of your jdbc calls using logging.properties, enabling the log output of the internal Informix JDBC classes. Setting the SQLIDEBUG property in the connection string to a trace file might also help you. I have not used that one, and documentation says the feature should only be used together with technical support, but in a sandbox environment I would give it a try what would be the result. Both should be used with care and only for a short time frame in production systems, because the files created can grow very fast depending on the system activity (for us, enabling an SQL output for all sessions even on one host only would kill the system, since we have > 25000 Sessions) so we would try to limit the output by other markers (only a specific user e.g. if your logging mechanism allows to filter that). Or try to use them only on a test system or staging, where user activity is not that high. If you see a problematic query running, which for example takes a lot of time, you could also try to dump stack traces of the running tomcat engine in order to identify which classes are waiting for query results. You could set up a job which dumps stacks each 30 seconds for a specific period of time (use timestamps as filename) and note when the problematic queries were executed. Then search the stacks for threads which have been waiting for query results in that specific time frame. The class that issued the statement, will be part of the stack (including line info if available). This can be done with the jstack tool, which is part of the jdk and has solved many issues (especially locking issues) on our side.

Thank you all for the recommendations, especially for the comprehensive suggestions from Marcus. ------------------------------ Antonin Rozehnal ------------------------------