Get Explain over JDBC-Connection
Stefan Wilfling wanted to read SET EXPLAIN output over JDBC but the writable path differs between Linux and Windows servers. Art Kagel pointed to a blog with SPL procedures and suggested an NFS share. Doug Lawry said the OS can be found from sysmaster:sysmachineinfo and that GUI tools use the built-in EXPLAIN_SQL function, giving its signature.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 ------------------------------ #Informix
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 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.
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 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.
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
------------------------------