EXPLAIN PLAN CLPPlus command

The EXPLAIN PLAN CLPPlus command retrieves explain plan information for any single SQL statement.

The EXPLAIN PLAN CLPPlus command is supported on Db2 for z/OS and IBM Informix.

Invocation

You must run this command from the CLPPlus interface.

Authorization

None

Required connection

You must be connected to a database.

Restrictions

Support on IBM Informix has these restrictions:
  • Only SELECT statements are supported.
  • You must create and specify a default sbspace name for the SBSPACENAME configuration parameter in the ONCONFIG file. This sbspace is used for creating BLOB objects when an explain plan is created.
  • To retrieve statistics data from an Informix® server, your user ID must have the DBA privilege on the Informix database. Only user IDs with this privilege have access to statistics data.

Syntax diagram

Read syntax diagramSkip visual syntax diagramEXPLAINPLANFORSQL-statement

Command parameters

SQL-statement
The SQL statement on which explain information is retrieved. For IBM Informix only SELECT statements are supported.

Examples

SQL> explain plan for select * from emp where bonus > 1000 and salary>10000;

    ID TYPE            OBJECT_SCHEMA        OBJECT_NAME         PREDICATE_TEXT         
------ --------------- -------------------- ------------------- -----------------------
     1 RETURN                                                                          
     2 TBSCAN          MANSHANB             EMPLOYEE            (10000 < Q1.SALARY)    
     2 TBSCAN          MANSHANB             EMPLOYEE            (1000 < Q1.BONUS)