A good way to start using query plans is to set the QUERY_PLAN_AS_HTML option ON. This option places a graphical version of the query plan in the same directory as the .iqmsg file. You can view this file in most Web browsers.
In the HTML query plan, each node in the tree is a hyperlink to the details. Each box is hyperlinked to the tree above. You can click on any node to navigate quickly through the plan.
Authorized users can display query plans in the Java-based Interactive SQL plan window. Users can also save and print query plans from Interactive SQL instead of accessing the .iqmsg file or query plan files on the server.
SQL functions GRAPHICAL_PLAN and HTML_PLAN return IQ query plans in XML and HTML format, respectively, as a string result set. Database options QUERY_PLAN_TEXT_ACCESS and QUERY_PLAN_TEXT_CACHING control the behavior of the new functions.
View query plans from the Interactive SQL plan window in the following ways:
Execute the query and open the plan window. Depending on the plan type you selected from the Plan option (Tools > Options > Plan), the appropriate plan displays in the plan window.
The IQ query plan displays only if the GRAPHICAL_PLAN option is selected. Other plans return the error message, “Plan type is not supported.”
Enter the query in the SQL statements window and select from the menu SQL > Get Plan. Depending on the plan type you selected from the Plan option (Tools > Options > Plan), the appropriate plan displays in the plan window.
The IQ query plan displays only if the GRAPHICAL_PLAN option is selected. Other plans return the error message, “Plan type is not supported.”
Use the SQL functions, GRAPHICAL_PLAN and HTML_PLAN, to return the query plan as a string result.
To access query plans, use the SQL functions, GRAPHICAL_PLAN and HTML_PLAN, for the following queries: SELECT, UPDATE, DELETE, INSERT SELECT, and SELECT INTO.
To save query plans from Interactive SQL, use GRAPHICAL_PLAN or HTML_PLAN to retrieve the query plan and save the output to a file using the OUTPUT statement.
To view saved plans, select File > Open from the Interactive SQL client menu and navigate to the directory where you saved your plan. You can also print plans displayed on the plan window by selecting File > Print.
See “GRAPHICAL_PLAN function [String]” and “HTML_PLAN function [String]” in Reference: Building Blocks, Tables, and Procedures for details. For the options that support these query plan functions, see “QUERY_PLAN_TEXT_ACCESS option” and “QUERY_PLAN_TEXT_CACHING option” in Reference: Statements and Options.
