Obtaining Informix query plans

Abstract

Informix database administrators and developers should know the importance of reviewing query plans for new SQL statements before they are deployed to ensure they are as fast as possible and use minimum resources. “You wouldn’t buy a car without a test drive.”

Unless you are using AGS Server Studio which displays an Execution Plan at the click of a button, or are logged into the database server itself, how to view a plan is not obvious using only the IBM or HCL documentation.

This article describes both standard and little-known methods requiring only a SQL connection.

Content

Examples in this article were produced using the provided “stores_demo” database in a Docker container from informix-dockerhub with Informix Dynamic Server (IDS) updated to 14.10.FC11.

We will use the following example SQL that determines the states and ZIP Codes where most customers are located:

That returns:

state	customers
CA	18
AZ	2
NJ	2

zipcode	customers
94063	3
94022	2
94062	2
94086	2
94025	2

If SET EXPLAIN ON was run beforehand in the same session, the plan is appended by default to:

$HOME/sqexplain.out on a Unix derived operating system;
%INFORMIXDIR%\sqexpln\username.out on Windows.

The documentation describes slight differences for mapped users and how to specify a different file.

Alternatively, you can use an EXPLAIN DIRECTIVE within a SQL statement or use the onmode -Y session-id 1 command.

The Informix message log will have recorded a line like this:

Whichever method was used, content like this would have been appended:

QUERY: (OPTIMIZATION TIMESTAMP: 04-26-2025 12:50:55)
------
SELECT state, COUNT(*) AS customers
FROM customer
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 2 DESC;

Estimated Cost: 41
Estimated # of Rows Returned: 3
Temporary Files Required For: Order By  Group By

1) informix.customer: SEQUENTIAL SCAN

Query statistics:
-----------------

Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                customer

type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     28         28        28         00:00.00   6

type     rows_prod  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  group    3          9         28         00:00.00   36

type     rows_sort  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  sort     3          3         3          00:00.00   0

QUERY: (OPTIMIZATION TIMESTAMP: 04-26-2025 12:50:55)
------
SELECT zipcode, COUNT(*) AS customers
FROM customer
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 2 DESC;

Estimated Cost: 3
Estimated # of Rows Returned: 7
Temporary Files Required For: Order By

1) informix.customer: INDEX PATH

(1) Index Name: informix.zip_ix
        Index Keys: zipcode   (Key-Only)  (Serial, fragments: ALL)

Query statistics:
-----------------

Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                customer

type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     28         28        28         00:00.00   3

type     rows_prod  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  group    5          22        28         00:00.00   36

type     rows_sort  est_rows  rows_cons  time       est_cost
  ------------------------------------------------------------
  sort     5          7         5          00:00.00   1

Once you become familiar with the format, these plans are easy enough to interpret. See also Sample query plan reports. You can see that the first statement must scan the whole table, whereas the second only needs to read the index on “zipcode”. Your goal is to reduce the estimated cost. If you wanted to make the first query more efficient, this would be a solution:

Appending ONLINE normally means the table can be in use while the index is built, but there are caveats listed here.

Sometimes, the right solution is to specify how the query should be run with Optimizer Directives.

If you don’t want the “Query statistics” sections, they can be suppressed via the EXPLAIN_STAT configuration parameter:

Alternatively, if enabling query plans dynamically, use the onmode -Y session-id 2 command.

You can capture query plans without actually executing the SQL with:

If so, “Query statistics” sections are not included anyway as that data is only available afterwards.

If you do not have access to the database server file system, undocumented system function “ifx_explain” exists in every database from IDS version 12.10.XC2 onwards, which is passed and returns an LVARCHAR of indeterminate length:

You can only pass a single statement. Any problem with the SQL returns “Error 0”. Otherwise, you get exactly the same text as with SET EXPLAIN ON. You might get error -282:

That means you have configuration parameter ALLOW_NEWLINE set to 0. Either change it to 1, or do this first in the same session:

A similar system function “bson_explain” also exists in every database should you need the query plan in BSON format:

That returns:

The following produces exactly the same output as “ifx_explain”:

Both of the above functions use SET EXPLAIN ON AVOID_EXECUTE – so do not return “Query statistics” sections – and add a line like this to the Informix message log:

There is an alternative Java function getExplain which is documented. However, there are disadvantages:

You first have to install a compatible Java Run-time Environment (JRE) as current IDS versions no longer provide one, copying into the location specified by default with:

Otherwise you get:

Running the setup steps on the documentation page recorded this in the message log:

Running the example statement shows where the explain file is written, which it removes afterwards:

The disadvantages are that:

You need a compatible JRE and none is supplied from 14.10.FC11 or 15.10.
Registering a datablade requires DBA privileges.
This datablade is not automatically registered on first use.
It actually executes the query. The documentation page says so, and the results include Query Statistics which further proves that. It would be better if it used SET EXPLAIN ON AVOID_EXECUTE.

InformixHQ and AGS Server Studio use an entirely different function in the “sysadmin” database from IDS 11.50 onwards with the following specification:

Results can more easily be parsed for presenting graphically. A smart blob space is required. That function is not suitable for our purposes here and is beyond the scope of this article.

Caveats

System function “ifx_explain” is only available from IDS 12.10.XC2 and does not work if IDS is running on Windows (IBM case raised).

Conclusion

Reviewing query plans is essential. The steps required are well documented if you have access to the database server file system. Otherwise, undocumented system function “ifx_explain” returns exactly the same for any one statement and requires only a SQL connection.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.