SET Options for Tracing Queries
SET options of special interest in query troubleshooting include:
• [NO]PRINTQRY—Set query display on or off.
• [NO]QEP—Set query execution plan display on or off.
• JOINOP [NO]TIMEOUT—Set optimizer timeout on or off.
The SET PRINTQRY Option
The command SET PRINTQRY prints out the query before it is optimized and executed. This is especially useful when evaluating queries for performance tuning or troubleshooting.
Example: Turn Query Display On Using ING_SET
The following command turns query display on:
Windows:
SET ING_SET=set printqry
UNIX:
C Shell:
setenv ING_SET "set printqry"
Bourne Shell:
ING_SET = "set printqry"
export ING_SET
VMS:
DEFINE ING_SET "SET PRINTQRY"
As an example, assume that QBF is started up. The following is displayed:
Query Buffer:
set Autocommit on
Query Parameters:
Query Buffer:
select cap_value, cap_capability from iidbcapabilities
Query Parameters:
Query Buffer:
select user_name, dba_name from iidbconstants
Query Parameters:
Example: Turn Query Display On Using II_EMBED_SET
The following command turns query display on through II_EMBED_SET. This is similar to the ING_SET environment variable/logical. All information is gathered in the client application and printed to the log by the library routines linked to the client application:
Windows:
SET II_EMBED_SET=printqry
UNIX:
C Shell:
setenv II_EMBED_SET "printqry"
Bourne Shell:
II_EMBED_SET = "printqry"
export II_EMBED_SET
VMS:
DEFINE II_EMBED_SET "PRINTQRY"
The query output with query timings is placed into a file called iiprtqry.log. Here is a sample output:
Query text:
select * from iirelation
Query Send Time: Thu Mar 26 17:20:43 2001
Query Response Time: Thu Mar 26 17:20:44 2001
Response CPU Time: 2630
Qry End CPU Time: 3370
This type of query monitoring can be useful for spotting slow-running queries in applications.
The SET QEP Option
The SET QEP option provides a display of the optimizer’s query execution plan (QEP) for the query after it has been optimized but before it is executed.
The following command turns QEP display on as the default ING_SET level:
Windows:
SET ING_SET=set qep
UNIX:
C Shell:
setenv ING_SET "set qep"
Bourne Shell:
ING_SET = "set qep"
export ING_SET
VMS:
DEFINE ING_SET "SET QEP"
The Query Optimizer and QEPs are described in detail in the Database Administrator Guide.
The SET JOINOP NOTIMEOUT Option
The SET JOINOP NOTIMEOUT option can be used in tracing query performance. This statement turns the optimizer timeout feature off. With timeout on, the optimizer stops checking for further query execution plans when it believes that the best plan it has found takes less time to execute than the amount of time already spent searching for a plan. If you issue a SET JOINOP NOTIMEOUT statement, the optimizer continues searching query plans. This option is often used with the SET QEP option to ensure that the optimizer is picking the best possible query plan.
Last modified date: 01/30/2023