Explain Plan through JDBC

Posted in 2014

Summary

User needed to collect explain plans via JDBC on client with CSDK 3.7 and IDS 11.7. Responses suggested: (1) "SET EXPLAIN ON" creates server-side explain files; (2) EXPLAIN_SQL function exists but lacks good client-side documentation; (3) Fernando Nunes provided a working solution using database-generated CLOB output that works across versions 9.x-12.10; (4) John Miller mentioned newer ifx_explain and bson_explain functions for v12.10+.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Connectivity: ODBC / JDBC / .NET

JACOB SHADIX — Fri, 19 Sep 2014 21:19:47 — source: IIUG Forums & Mailing Lists

I need a means to collect explain plans using JDBC on client. Is EXPLAIN_SQL an option here? Are there any better solutions? I'm having issues finding much documentation on the function. We're using CSDK 3.7 on client and 11.7 IDS.

↪ replying to JACOB SHADIX

Paul Watson — Fri, 19 Sep 2014 21:35:35 — source: IIUG Forums & Mailing Lists

Set explain on Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 > On Sep 19, 2014, at 16:19, "JACOB SHADIX" <jacobshadix@gmail.com> wrote: > > I need a means to collect explain plans using JDBC on client. Is EXPLAIN_SQL > an option here? Are there any better solutions? I'm having issues finding much > documentation on the function. We're using CSDK 3.7 on client and 11.7 IDS. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Paul Watson

Fernando Nunes — Fri, 19 Sep 2014 22:39:59 — source: IIUG Forums & Mailing Lists

?! That will create the explain file in the database server... I suppose that's not what the OP wants. Apologizing for some self promotion, the whole history is on these two articles: For V12.10: http://informix-technology.blogspot.co.uk/2014/03/explain-plans-for-last-time-pl anos-de.html And for any of 9.x, 10, 11.10, 11.50, 11.70 (and works on 12.10 also): http://informix-technology.blogspot.co.uk/2012/12/execution-plans-on-client-plan os-de.html Client technology is irrelevant... as long as it can handle a CLOB. Works in dbaccess, JDBC, etc. To turn a long story short: We finally solved the long standing issue of client side explains in 12.10 (documented in FC2/FC3 I think), but truth is there was an easy solution for years. EXPLAIN_SQL is a good idea, but lacks a good usage on the client side. As I mentioned in the second article I tried to use it by converting the returned XML into text using native database XSLT transformation capabilities. Lack of proper documentation, but mostly my inability to deal with complex XSLT transformations killed the idea (together with a couple of other issues). The latest approach is easy to understand, easy to implement and easy to use. Take into consideration the cleanup of the files... There's a tradeoff... you may use a single file per user which in the worst case scenario would create as many files as the number of your users, vs a file per session... The later allows the same user to get plans in different sessions concurrently... Also consider carefully the files location... I used /tmp for simplicity. Regards. HTH. On Fri, Sep 19, 2014 at 10:35 PM, Paul Watson <paul@oninit.com> wrote: > Set explain on> > Cheers > Paul > > Paul Watson > Oninit www.oninit.com > +1 913 387 7529 > > > On Sep 19, 2014, at 16:19, "JACOB SHADIX" <jacobshadix@gmail.com> wrote: > > > > I need a means to collect explain plans using JDBC on client. Is > EXPLAIN_SQL > > an option here? Are there any better solutions? I'm having issues finding > much > > documentation on the function. We're using CSDK 3.7 on client and 11.7 > IDS. > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c30b3c7be2dd050372ad1f

↪ replying to JACOB SHADIX

John Miller iii — Fri, 19 Sep 2014 22:42:30 — source: IIUG Forums & Mailing Lists

There is a few new ways which return the contains of the explain file to the application. The two new functions are: ifx_explain and bson_explain(). the following article explain how to use them http://www.ibmnosql.com/2014/03/optimizer-explain-file/ John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 09/19/2014 02:19:48 PM: > From: "JACOB SHADIX" <jacobshadix@gmail.com> > To: ids@iiug.org, > Date: 09/19/2014 02:20 PM > Subject: Explain Plan through JDBC [33826] > Sent by: ids-bounces@iiug.org > > I need a means to collect explain plans using JDBC on client. Is EXPLAIN_SQL > an option here? Are there any better solutions? I'm having issues > finding much > documentation on the function. We're using CSDK 3.7 on client and 11.7 IDS. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

↪ replying to John Miller iii

JACOB SHADIX — Fri, 03 Oct 2014 19:19:35 — source: IIUG Forums & Mailing Lists

Any workaround for 11.7?

↪ replying to JACOB SHADIX

Fernando Nunes — Fri, 03 Oct 2014 21:15:42 — source: IIUG Forums & Mailing Lists

Didn't you look into my answer on September 19? Regards On Fri, Oct 3, 2014 at 8:19 PM, JACOB SHADIX <jacobshadix@gmail.com> wrote: > Any workaround for 11.7? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e013c66144651bd05048b3cc8