Isolate and Analyze the Problem Query
To determine whether the problem is due to the user interface, the query itself, or a software bug, follow these steps:
1. Isolate a poorly performing query from your user interface using the trace flag set printqry, which prints queries before they are optimized and executed. Identify the query that seems to hang.
For details on setting printqry, see the System Administrator Guide.
Execute the query in a terminal monitor, from a query tab in Director, or from within the VDBA SQL Scratchpad window, and determine if performance is the same. If performance is only a problem when the query is executed from the user interface, you have identified an application problem. If performance is the same, continue.
2. In a terminal monitor, issue the following statements to display the QEP without running the query:
set qep;
set optimizeonly;
Now, execute your query and save the output to a file for examination. After running the query, exit the terminal monitor session or turn query execution back on using:
set nooptimizeonly;
For details on these SET statements, see the System Administrator Guide.
3. Review the Design Issues section and evaluate the QEP for your query. For example, you can look for:
• Large table scans that can be avoided
• An additional index that is needed
• Cartesian products with large tables
• Function joins
• If you are not able to identify your problem and suspect a software bug, submit your query and a test case to customer support.