EXPLAIN PLAN FOR CURSOR cursor-name
{  INTO hostvar | USING DESCRIPTOR sqlda-name }

cursor-name : identifier or hostvar

sqlda-name : identifier

The EXPLAIN statement retrieves a text representation of the optimization strategy for the named cursor. The cursor must be previously declared and opened.

The hostvar or sqlda-name variable must be of string type. The optimization string specifies in what order the tables are searched, and also which indexes are being used for the searches if any.

This string may be long, depending on the query, and has the following format:

table (index), table (index), ...

If a table has been given a correlation name, the correlation name will appear instead of the table name. The order that the table names appear in the list is the order in which they are accessed by the database server. After each table is a parenthesized index name. This is the index that is used to access the table. If no index is used (the table is scanned sequentially) the letters "seq" will appear for the index name. If a particular SQL SELECT statement involves subqueries, a colon (:) will separate each subquery's optimization string. These subquery sections will appear in the order that the database server executes the queries.

After successful execution of the EXPLAIN statement, the sqlerrd field of the SQLCA (SQLIOESTIMATE) is filled in with an estimate of the number of input/output operations required to fetch all rows of the query.

A discussion with quite a few examples of the optimization string can be found in Improving database performance.

Must have opened the named cursor.

None.

• DECLARE CURSOR statement [ESQL] [SP]
• PREPARE statement [ESQL]
• FETCH statement [ESQL] [SP]
• CLOSE statement [ESQL] [SP]
• OPEN statement [ESQL] [SP]
• Using cursors in embedded SQL
• The SQL Communication Area (SQLCA)

The following example illustrates the use of EXPLAIN:

EXEC SQL BEGIN DECLARE SECTION;
char plan[300];
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE employee_cursor CURSOR FOR
   SELECT EmployeeID, Surname
   FROM Employees
   WHERE Surname like :pattern;
EXEC SQL OPEN employee_cursor;
EXEC SQL EXPLAIN PLAN FOR CURSOR employee_cursor INTO :plan;
printf( "Optimization Strategy: '%s'.n", plan );

The plan variable contains the following string:

'Employees <seq>'