Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Get Explain over JDBC-Connection

  • 1.  Get Explain over JDBC-Connection

    Posted Tue August 18, 2020 08:23 AM

    Hi!

    I try to get the explain-file over JDBC (because not all developers have access to the db-servers).

     

    Following commands:

    "SET EXPLAIN FILE TO '" + fileName + "'   "

    <Execute the explain-statements>

    "SET EXPLAIN OFF"
    "SELECT FILETOBLOB('" + fileName + "', 'server')" --Read the file as ResultSet

     

    So far this work, but I´ve a problem with the Path of the filename.

    We have windows & linux – servers and so I´ve not a fixed path and I don´t know, what I should use as path.

     

    • At linux "/tmp" works.
    • At windows it´s the always the sqexpln-Folder in the installation-folder (%TEMP% doesn´t work).  

     

    Any ideas or is there an easier way to get the explain for the statements? 

    As workaround, do I get the OS from the server over JDBC (Then I can try a list of possible folders)?



    ------------------------------
    Kind Regards
    Stefan
    ------------------------------


  • 2.  RE: Get Explain over JDBC-Connection

    Posted Tue August 18, 2020 09:47 AM
    Stefan:

    Check out Fernando's Blog on this subject. He presents SPL procs to implement this, though I'm not sure that he deals with the differences between a Linux/Unix server and a Windows one.

    http://informix-technology.blogspot.com/2012/12/execution-plans-on-client-planos-de.html

    Art

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


    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.








  • 3.  RE: Get Explain over JDBC-Connection

    Posted Tue August 18, 2020 09:49 AM
    Oh, forgot, another option, one that we used at Bloomberg for many years, is to have an NFS drive that is mounted on the servers and also on the development systems (or developers' workstations) to which you can direct the SET EXPLAIN files.

    Art

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


    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.








  • 4.  RE: Get Explain over JDBC-Connection

    Posted Tue August 18, 2020 09:51 AM

    Hi Stefan.

    You can determine the OS with:

    SELECT os_name FROM sysmaster:sysmachineinfo;

    AGS Server Studio and other GUI tools use the built-in EXPLAIN_SQL function. The specification is:

    CREATE DBA FUNCTION explain_sql
    (
        INOUT major_version INT,
        INOUT minor_version INT,
        requested_locale VARCHAR(33),
        xml_input BLOB,
        xml_filter BLOB,
        OUT xml_output BLOB,
        OUT xml_message BLOB
    )
        RETURNS BLOB AS xml_plan

    The documentation is pretty minimal :-)



    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------