1.0

A CREATE or DROP INDEX statement for the target table of a prepared positioned delete invalidates the
prepared positioned delete statement.
Example
DELETE FROM TRADE.CUSTOMERS WHERE CID = 10
EXPLAIN
Capture or display the query execution plan for a statement.
Syntax
EXPLAIN { sql_statement | statement_id }
Description
When called with a SQL statement, the EXPLAIN command captures the query execution plan for the statement
and stores it in the STATEMENTPLANS system table on page 609. When called with a statement ID from
SYS.STATEMENTPLANS, the EXPLAIN command displays the full query plan for the statement. See SQLFire
Query Execution Plan Codes for a description of the SQLFire query execution plan codes.
Note: You can capture query execution plans using either a thin client or peer client connection. However,
you can display a stored query execution plan only when using a peer client connection.
As an alternative, you can capture query plans for all of the statements that are executed in a specific connection.
See Capture Query Execution Plans for All Statements on page 260.
Example
This statement captures a query plan in SYS.STATEMENTPLANS:
EXPLAIN select * from flights f, flightavailability fa where
f.flight_id = fa.flight_id;
You can see the statement ID for the query execution plan by querying the system table:
sqlf> select STMT_ID, STMT_TEXT from SYS.STATEMENTPLANS;
STMT_ID |STMT_TEXT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
00000001-ffff-ffff-ffff-000000000022| select * from flights
f, flightavailability fa where f.flight_id = fa.flight_id and
f.flight_id = 'AA1116'
1 row selected
To view the actual query execution plan, connect as a peer to the SQLFire system and
execute the EXPLAIN command with the statement ID:
sqlf> connect peer
'host-data=false;locators=mylocator[10334]';
sqlf> explain '00000001-ffff-ffff-ffff-000000000022';
member 10.0.1.26(52578)<v1>:32828/54810 begin_execution
2012-04-09 15:01:29.478 end_execution 2012-04-09 15:01:29.545
QUERY-RECEIVE execute_time 66323000 member_node
10.0.1.26(52590)<v2>:27914/54823
RESULT-SEND execute_time 127000 member_node
10.0.1.26(52590)<v2>:27914/54823
RESULT-HOLDER returned_rows 286 no_opens 1 execute_time
21378000
473
SQL Language Reference