Informix

 View Only

  • 1.  SQL command identification/source

    Posted Thu March 28, 2024 08:49 AM
    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
    ------------------------------


  • 2.  RE: SQL command identification/source

    Posted Thu March 28, 2024 10:44 AM
    Edited by Doug Lawry Thu March 28, 2024 10:45 AM

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



  • 3.  RE: SQL command identification/source

    Posted Thu March 28, 2024 11:58 AM
    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 Kbytes
     
    session           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
    ------------------------------

    Original Message


  • 4.  RE: SQL command identification/source

    IBM Champion
    Posted Thu March 28, 2024 03:01 PM

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

    Original Message


  • 5.  RE: SQL command identification/source

    IBM Champion
    Posted Thu March 28, 2024 04:20 PM
    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)
    A standalone Java Decompiler GUI. Contribute to java-decompiler/jd-gui development by creating an account on GitHub.
    github.com
    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,
    MARCUS HAARMANN



    Original Message


  • 6.  RE: SQL command identification/source

    IBM Champion
    Posted Thu March 28, 2024 04:49 PM
    If you have access to the logging of your tomcat engine, maybe the JDBC debugging might help you.

    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.
    MARCUS HAARMANN



    Original Message


  • 7.  RE: SQL command identification/source

    Posted Thu April 04, 2024 11:56 AM

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



    ------------------------------
    Antonin Rozehnal
    ------------------------------

    Original Message