Generates a query execution plan for a SQL query.
EXPLAIN PLAN [ INTO [ owner_name. ] table_name ] [ SET STATEMENT_ID = <stmt id> ] FOR <SQL statement>
The table into which the query plan is to be saved. Any valid table name may be specified. If the table does not exist, then it is created, provided the user has resource privilege. If the current owner is not the owner of the table specified he should have adequate privilege to write into it.
Note: The INTO table_name clause is optional. If it is not specified, then the query plan is stored in the default plan table admin.qep_tbl.
The user provided identifier for the query plan. Any character string (less than 32 characters) can be specified. The stmt_id is stored along with the query plan and is used to distinguish between the various Query plans stored in the Query plan table.
The SET STATEMENT_ID = <stmt id> clause is optional. If not specified, a unique ID of the form qep_stmtid_xx is generated internally.
For complex SQL operation, the query plan of the SQL query being executed has a very large impact on its performance. It is useful to have a mechanism wherein the execution plan of a query can be made available before actually executing the query. This aids in designing efficient queries.
FairCom DB SQL has the ability to dynamically generate and store query plans. The query plan can be generated and even viewed graphically with the FairCom DB SQL Explorer utility.
An internal stored procedure is used to retrieve the query plan.
access_get_qep( table_owner, table_name, stmt_id, max_nodeinfo_len )
The output of the procedure is a resultset consisting of a single column. Each record of the resultset corresponds to a node in the query plan. The ordering of the nodes in the resultset is, from bottom to top and left to right of the query execution plan tree.
If the table does not exist, then it is created, provided the user has resource privilege. If the current owner is not the owner of the table specified they should have adequate privilege to write into it.
Embedded SQL, interactive SQL, ODBC applications