Informix query plan

tags:

informix

views:

385

answers:

+1 Q:

Informix query plan

How can I get the execution plan for a query in Informix?

I need something similar to what I get in Oracle with explain plan or in SQL Server with set showplan_all on.

I'm connecting from a .NET application (using IBM.Data.Informix), if that's important.

Thanks!

Which version of Informix?

There is some information related to v10, for [SET EXPLAIN].

lavinio 2009-07-21 02:56:37

+1 A:

In your SQL, you can either:

execute SET EXPLAIN ON; This will remain in effect until you disconnect or execute the equivalent OFF statement. Works at least as far back as v7, may even work in v5.
add an optimiser directive (v9 onwards), ie SELECT {+EXPLAIN} foo, bar FROM .... You can also use SELECT --+EXPLAIN \n foo bar ... depending on the exact syntax of your SQL (you can't close a -- comment, it runs to the end of the line). This variant is only in effect for that SELECT statement.

Where the explain file is written depends largely on the OS of the database server. It could be your user's home directory, or a directory within $INFORMIXDIR.

RET 2009-07-21 04:20:05

So, I'd need filesystem access to the DB server, right?

Diego Mijelshon 2009-07-21 14:29:28

@RET: It goes back further than version 5; I believe SET EXPLAIN was present in Informix-SQL 1.10 (1985).

Jonathan Leffler 2009-07-21 22:19:15

@Diego: always quote the version of IDS; it matters. For example, IDS 11.50 provides an option `SET EXPLAIN FILE TO 'filename' [WITH APPEND]`; you can specify where (on the server) the file is created. However, the output does still go to a file, so you will need to access the file on the server. If you can wangle it, the FILETOCLOB function can be used to pull a server-side file into a CLOB value: `INSERT INTO SomeTable VALUES(FILETOCLOB('/file/name','server'))` and you can then pull the CLOB to the client with a regular SELECT (and parse the text of the file - have fun!). I didn't say easy!

Jonathan Leffler 2009-07-21 22:24:26

@Diego: Yes, you will need access to the database server, unless you can get Jonathan's FILETOCLOB() method working for you. If adding SET EXPLAIN to your application is an issue, you can also use onmode -Y 1 <session-id> but again, this relies on access to the server.There is also a sysmaster table called syssqexplain that contains some encoded information that you might be able to make use of, although it's no substitute for the actual query-plan text.

RET 2009-07-22 06:03:04

Sorry, that should have been onmode -Y <session-id> 1

RET 2009-07-22 06:03:54

Can you expand on the syssqlexplain usage?I'm writing an open source multi-db query tool that should work as transparently as possible for regular users, so filesystem access is not an option.The resulting output of this should be ideally a query plan tree showing partial costs and potential bottlenecks.

Diego Mijelshon 2009-07-23 15:55:10

There are a couple of examples of usage here: http://www.informixfaq.com/wiki/doku.php/wiki:sysmasterI can't help thinking that if you're writing something open-source, maybe you should build the support for the DBs you're familiar with, and focus on the output. After all, if the tool is good enough, someone with strong Informix skills will pick up the challenge to build the plug-in. Every RDBMS's optimiser is a very specialised thing. Identifying "potential bottlenecks"? OLTP or DSS? It's almost reverse-engineering the optimiser when you think about it.

RET 2009-07-25 08:09:49

ansaurus

tags:

views:

answers:

Informix query plan

related questions