Setting up the Execution Environment with Query options
Go Up to Execution and Execution-related SQL Editor options
A set of options corresponding to DBMS-specific query options are available. They let you customize the execution environment for an ISQL session with query-handling directives in areas such as performance, logging/reporting, and error handling.
See the following topics for descriptions of the options available for the DBMS you are working against:
- DB2 LUW Query options
- DB2 z/OS Query options
- MySQL Query options
- Oracle Query options
- SQL Server Query options
- Sybase ASE Query options
- Sybase IQ Query options
- Teradata Query options
If you do not change the default settings, a set of default query option settings are sent to the server, one set per DBMS platform. You can, however save a set of query option settings for each DBMS, and have that set of settings used as the default for ISQL sessions against that platform. As well, during any ISQL session, you can modify the query option settings for the current session.
The query options you select apply only to the current ISQL window instance. In addition, non-default query option settings you specify are not saved. You can however, save a set of query option settings to a file and subsequently open that file in other ISQL editor sections to have those settings applied to that session.
To specify query options:
- With the SQL Editor open, select Query Options from the Query menu.
- Once open, you have the following options:
- Set each option manually using the associated check box.
- Use the Save button to open a dialog that lets you save the current option query settings to an XML file. The dialog also has a Load these query options as default option that lets you use the settings in the saved file as the default query option settings for the current platform. Exercising this option sets the Load Query Options feature on the DBMS-specific ISQL tab of the Options editor to specify the file you saved. For more information, see ISQL Options.
- Use the Load button to open a dialog that lets you locate and open a previously-saved XML file containing query option settings.
- Use the Reset button to restore the query option defaults.
- Click OK to set query options for the current ISQL window session.
- Note: For information on conditions for which query options are sent to the server, see Setting up the Execution Environment with Query options.
When query options are sent to the server
Locked connections optimize sending query options to the server. Since an unlocked connection results in a new connection for each execution, query options must be sent with each execution. The following table outlines the specific cases:
Session unlocked |
When you open an ISQL window, if you not lock the connection, all query options are sent to the server each time you execute a script, immediately before executing the actual script. As long as the connection remains unlocked, all query options are sent to the server at each execution. Similarly if you unlock a currently locked session, all query options are sent to the server at each subsequent execution. For information on locking sessions, see SQL Editor Toolbar Options. |
Session locked |
When you open an ISQL and lock the connection, all query options are sent to the server on the first execution. On subsequent executions, if you do not modify query option settings, no query options are sent to the server on subsequent executions. If you open Query Options dialog and modify options, on the next execution only the modified settings are sent to the server. These rules apply as long as the connection remains locked. |
Contents
DB2 LUW Query options
The following settings are available when setting query options against a DB2 LUW datasource:
Query Option | Description |
---|---|
Batch Delimiter |
The batch separator must be a viewable character and not a space, new line, or tab. The default (semi-colon) has been selected to ensure compatibility with the features of Rapid SQL and the respective platform, but can be customized. Note: A custom delimiter works only from within an ISQL window and can’t be used for extraction operations. |
Check syntax when executing |
TRUE/FALSE |
Create Explain plan tables if required |
If set to TRUE, Explain Plan tables are created, as necessary. If set to FALSE and you don’t manually create tables, Explain Plan operations will fail. |
Create explain plan tables on the SYSTOOLS schema |
If set to TRUE, Explain Plan tables are created on the SYSTOOLS schema. If the tables already exist in the user’s default schema, those tables will continue to be used. Refer to DB2 documentation for a listing of Explain Plan tables that must be deleted in order to use the SYSTOOLS option. If set to FALSE, Explain Plan tables are created under the user’s default schema. |
Max Errors Before Aborting |
Select the maximum number of errors encountered before a script is aborted. Setting this value to zero disables the feature. |
Row Count |
When set to TRUE, a query is tereminated after returning the specified number of rows. |
Run Script with batch execution |
TRUE/FALSE |
Isolation Level |
Lets you set DB2 Isolation Levels of UNCOMMITED READ, RESET, CURSOR STABILITY, REPEATABLE READ, or READ STABILITY. |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |
DB2 z/OS Query options
The following settings are available when setting query options against a DB2 z/OS datasource:
Query Option | Description |
---|---|
Batch Delimiter |
The batch separator must be a viewable character and not a space, new line, or tab. The default (semi-colon) has been selected to ensure compatibility with the features of Rapid SQL and the respective platform, but can be customized. Note: A custom delimiter works only from within an ISQL window and can’t be used for extraction operations. |
Check syntax when executing |
TRUE/FALSE |
Max Errors Before Aborting |
Select the maximum number of errors encountered before a script is aborted. Setting this value to zero disables the feature. |
Row Count |
When set to TRUE, a query is terminated after returning the specified number of rows. |
Run Script with batch execution |
TRUE/FALSE |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |
MySQL Query options
The following settings are available when setting query options against a MySQL datasource:
Query Option | Description |
---|---|
Batch Delimiter |
The batch separator must be a viewable character and not a space, new line, or tab. The default (semi-colon) has been selected to ensure compatibility with the features of Rapid SQL and the respective platform, but can be customized. Note: A custom delimiter works only from within an ISQL window and can’t be used for extraction operations. |
Big Tables |
When set to TRUE, allows big result sets by saving all temporary sets to file. This can slow queries. |
Client Character |
Default character set. |
Insert ID |
Choose a value to be used a following INSERT or ALTER TABLE statement when you supply an AUTO_INCREMENT value. |
Interactive Timeout |
28800 default |
Last Insert ID |
Set the value to be stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. |
Max Errors Before Aborting |
Select the maximum number of errors encountered before a script is aborted. Setting this value to zero disables the feature. |
Query Cache Type |
The query is cached for ON or DEMAND. |
Row Count |
When set to TRUE, a query terminates after returning the specified number of rows. |
SET Other Variables |
Variables to be set at runtime. |
SQL Auto IS NULL |
When set to TRUE, enables you to find the last inserted row for a table. |
SQL Big Selects |
When set to TRUE, SELECT statements likely to take a very long time to execute will be aborted (i.e., where the number of rows examined exceeds the max join size) |
SQL Big Tables |
TRUE/FALSE |
SQL Buffer Result |
When set to TRUE, forces results from SELECT statements into temporary tables. |
SQL Log Bin |
When set to TRUE, allows logging to the binary log. |
SQL Log Off |
When set to TRUE, no logging is done to the general query log. |
SQL Log Update |
When set to TRUE, allows logging to the binary log. |
SQL Low Priority Updates |
When set to TRUE, gives table modifying operations lower priority than SELECT operations. |
SQL Max Join Size |
When set to TRUE, you can catch SELECT statements where keys are not used properly and that would probably take a long time. Set it if your users tend to perform joins that lack a WHERE clause, that take a long time, or that return millions of rows. |
SQL Quote Show Create |
When set to TRUE, table and column names will be quoted. |
SQL Safe Updates |
When set to TRUE, the query aborts UPDATE or DELETE statements that do not use a key in the WHERE clause or a LIMIT clause. This makes it possible to catch UPDATE or DELETE statements where keys are not used properly and that would probably change or delete a large number of rows |
SQL Select |
The maximum number of records that should be returned from SELECT statements. |
SQL Warnings |
Defines whether or not single row insert statements generate an information string in the event of a warning. |
Transaction Isolation |
Repeatable Read is the default. Read Committed, Read Uncommitted, and Serializable are the other options. Refer to MS SQL Query Options dialog box for an explanation. |
Unique Checks |
Performs uniqueness checks for secondary indexes of MyISAM tables. |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |
Oracle Query options
The following settings are available when setting query options against an Oracle datasource:
Category | Query Option | Description |
---|---|---|
Query Analysis |
Network Activity |
True/False |
Execution Information |
True/False | |
I/O Activity |
True/False | |
Sort Activity |
TRUE/FALSE | |
Index Activity |
True/False | |
Parse Activity |
TRUE/FALSE | |
Table Activity |
True/False | |
OS Activity |
TRUE/FALSE | |
Wait Activity |
True/False | |
Miscellaneous |
Batch Delimiter |
The batch separator must be a viewable character and not a space, new line, or tab. The default (“/”) has been selected to ensure compatibility with the features of Rapid SQL and the respective platform, but can be customized. Note: A custom delimiter works only from within an ISQL window and can’t be used for extraction operations. |
Check syntax when executing |
TRUE/FALSE | |
LONG Size Bytes |
8,192 is the default | |
Max Errors Before Aborting |
Select the maximum number of errors encountered before a script is aborted. Setting this value to zero disables the feature. | |
Row Count |
When set to TRUE, a query terminates after returning the specified number of rows. | |
Run Script with batch execution |
TRUE/FALSE | |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |
SQL Server Query options
The following settings are available when setting query options against a SQL Server datasource:
Category | Query Option | Description |
---|---|---|
Query Analysis |
Show Plan |
When set to TRUE, reports data retrieval methods chosen by the Microsoft SQL Server query optimizer. |
No Count |
Terminates the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. | |
No Exec |
When set to TRUE, compiles each query without executing it. | |
Force Plan |
When set to TRUE, processes a join in the same order as tables appear in the FROM clause of a SELECT statement only. | |
Statistics I/O |
Lets you display information regarding the amount of disk activity generated by Transact-SQL statements. | |
Statistics Time |
Displays the number of milliseconds required to parse, compile, and execute each statement. | |
Parse Only |
When set to TRUE, checks the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement. When TRUE, makes Microsoft SQL Server only parse the statement. When FALSE, makes Microsoft SQL Server compile and execute the statement. Do not use Parse Only in a stored procedure or a trigger. | |
Arithmetic |
Ignore Overflow |
When set to TRUE, any overflow caused by a resulting value that is larger than a column’s specified size is ignored. |
Abort On Overflow |
If set to TRUE, queries will be aborted on encountering a value greater than the specified size. | |
SET Options |
The Send Set Options setting dictates whether the remaining ANSI SQL Options in this category are sent to the server. The default for this option is set using the Enable SET query options setting on the ISQL tab of the Options editor. For details, see ISQL Options. If the Send Set Options setting is enabled, the remaining settings in this category let you specify the specific ANSI SQL options that are sent to the server: ansi_defaults, ansi_nulls, ansi_null_dflt_on, QUOTED IDENTIFIER, cursor_close_on_commit, ANSI_PADDING, ANSI WARNINGS, IMPLICIT_TRANSACTIONS, and CONCAT_NULL_YIELDS_NULL. The initial default values are hard-coded, not obtained from server settings. | |
Transactions |
Isolation Level |
Read Committed: Microsoft SQL Server default transaction isolation level. Specifies that shared locks are held while data is read to avoid dirty reads. You can change the data before the end of the transaction, resulting in nonrepeatable reads or phantom data. Read Uncommitted: The lowest level of transaction isolation. Transactions are isolated to ensure that physically corrupt data is not read. Applies dirty read, or isolation level 0 locking, which ensures that no shared locks are issued and no exclusive locks are honored. If set, it is possible to read uncommitted or dirty data values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. Repeatable Read: Places locks on all data used in a query, preventing other users from updating the data. Other users can insert new phantom rows into the data and are included in later reads in the current transaction. Concurrency is lower than Read Committed. Use this option only when necessary. Serializable: The highest level of transaction isolation. Transactions are completely isolated from one another. Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. Concurrency is lower than Repeatable Read. Use this option only when necessary. |
Miscellaneous |
Batch Delimiter |
The batch separator must be a viewable character and not a space, new line, or tab. The default (“go”) has been selected to ensure compatibility with the features of Rapid SQL and the respective platform, but can be customized. Note: A custom delimiter works only from within an ISQL window and can’t be used for extraction operations. |
Max Errors Before Aborting |
Select the maximum number of errors encountered before a script is aborted. Setting this value to zero disables the feature. | |
Row Count |
When set to TRUE, a query terminates after returning the specified number of rows. | |
Run Script with batch execution |
TRUE/FALSE | |
Text Size |
8,192 is the default | |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |
Sybase ASE Query options
The following settings are available when setting query options against a Sybase ASE datasource:
Category | Query Option | Description |
---|---|---|
Query Analysis |
Show Plan |
When set to TRUE, reports data retrieval methods chosen by the Microsoft SQL Server query optimizer. |
No Count |
Terminates the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results. | |
No Exec |
When set to TRUE, compiles each query without executing it. | |
Statistics I/O |
Lets you display information regarding the amount of disk activity generated by Transact-SQL statements. | |
Statistics Time |
Displays the number of milliseconds required to parse, compile, and execute each statement. | |
Statistics Subquery Cache |
Displays the number of cache hits, misses, and the number of rows in the subquery cache for each subquery. | |
Force Plan |
When set to TRUE, processes a join in the same order as tables appear in the FROM clause of a SELECT statement only. | |
Prefetch |
When set to TRUE, enables large I/Os to the data cache. When set to FALSE, disables large I/Os to the data cache. | |
Parse Only |
When set to TRUE, checks the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement. When TRUE, makes Microsoft SQL Server only parse the statement. When FALSE, makes Microsoft SQL Server compile and execute the statement. Do not use Parse Only in a stored procedure or a trigger. | |
Transactions |
Chained |
Invokes a begin transaction before the following statements: delete, insert, open, fetch, select, and update. You must still explicitly close the transaction with a commit. |
Isolation Level |
0 1: Sybase default isolation level. Prevents dirty reads. 2: Prevents dirty and non-repeatable reads. 3: Prevents dirty and non-repeatable reads and phantoms. This level is equivalent to performing all selects with holdlock. | |
Arithmetic |
Ignore Overflow |
When set to TRUE, any overflow caused by a resulting value that is larger than a column’s specified size is ignored. |
Abort On Overflow |
If set to TRUE, queries will be aborted on encountering a value greater than the specified size. | |
Abort On Truncation |
Specifies behavior following a loss of scale by an exact numeric datatype during an implicit datatype conversion. When set to TRUE, a statement that causes the error is aborted but other statements in the transaction or batch continue to be processed. When set to FALSE, query results are truncated processing continues. | |
DBCC Traceflags |
Index Selection |
Valuable when tuning query performance. |
Join Selection |
Valuable when tuning query performance. | |
Output to Execution Window |
TRUE/FALSE | |
Output to Server Error Log |
TRUE/FALSE | |
Miscellaneous |
ANSI NULL |
When set to TRUE, controls results of logical operations with NULL values. |
Set Quoted Identifier |
TRUE/FALSE | |
Batch Delimiter |
The batch separator must be a viewable character and not a space, new line, or tab. The default (“go”) has been selected to ensure compatibility with the features of Rapid SQL and the respective platform, but can be customized. Note: A custom delimiter works only from within an ISQL window and can’t be used for extraction operations. | |
Row Count |
When set to TRUE, a query terminates after returning the specified number of rows. | |
Table Count |
Sets the number of tables that Sybase ASE considers at one time while optimizing a join. | |
Text Size |
8,192 is the default | |
Max Errors Before Aborting |
Select the maximum number of errors encountered before a script is aborted. Setting this value to zero disables the feature. | |
Run Script with batch execution |
TRUE/FALSE | |
Text Formatting (Sybase ASE 15) |
These options let you generate diagnostic output in text format, using the Sybase ASE set option command. To use these options, you must be using the sa_role or have the set tracing permission. You can select ON, OFF BRIEF, or LONG standard Sybase ASE settings. If you are not using the sa_role or do not have the set tracing permission, use only the DEFAULT selection for each Text Formatting Set option is available. That setting has no effect. Using other selections without the required role or permission will result in an error on the next execution. For detailed information on these options and required permissions or roles, see the Sybase documentation. For access, see Accessing Third Party Documentation. Short descriptions message content displayed for Text Formatting set option command options are: | |
Show |
A collection of details, where the collection depends on the choice of BRIEF, LONG, ON, or OFF. | |
Show Lop |
Logical operators used. | |
Show Managers |
Data structure managers used during optimization. | |
Show Props |
Logical properties evaluated. | |
Show Parallel |
Details of parallel query optimization. | |
Show Histograms |
Processing of histograms associated with SARG/join columns. | |
Show Abstract Plan |
Details of an abstract plan. | |
Show Search Engine |
Details of the join-ordering algorithm. | |
Show Counters |
Optimization counters. | |
Show Best Plan |
Details of the best query plan selected by the optimizer. | |
Show Code Gen |
Details of code generation. | |
Show Pio Costing |
Estimates ofreads/writes from/to disk. | |
Show Lio Costing |
Estimates of reads/writes from/to memory. | |
Show Pll Costing |
Estimates relating to costing for parallel execution. | |
Show Elimination |
Partition elimination. | |
Show Missing Stats |
Details of useful statistics missing from SARG/join columns. | |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |
Sybase IQ Query options
The following settings are available when setting query options against a Sybase IQ datasource:
Query Option | Description |
---|---|
Send SET Options |
The Send Set Options setting applies to all settings except those in the bottom-most Miscellaneous category. It dictates whether the child options are sent to the server. The default for this option is set using the Enable SET query option setting on the ISQL tab of the Options editor. For details, see ISQL Options. |
Isolation Level |
Specifies the locking isolation level for Catalog Store tables. |
Allow Nulls By Default |
Sets the ALLOW_NULLS_BY_DEFAULT option, controling whether new columns created without specifying either NULL/NOT NULL are allowed to contain NULL values. |
ANSI Null |
Sets the ANSINULL option, controlling the interpretation of using = and != with NULL. |
Conversion Error |
Sets the CONVERSION_ERROR option, controlling reporting of data type conversion failures on fetching information. |
Divide-by-zero Error |
Sets the DIVIDE_BY_ZERO_ERROR option, controlling reporting of division by zero. |
SET quoted_identifier |
Sets the QUOTED_IDENTIFIER option, controlling the interpretation of strings enclosed in double quotes. |
SQL Flagger Error Level |
Sets the SQL_FLAGGER_ERROR_LEVEL option, controlling the behavior in response to any SQL code that is not part of the specified standard. |
SQL Flagger Warning Level |
Sets the SQL_FLAGGER_WARNING_LEVEL option, controlling the response to any SQL that is not part of the specified standard. |
String R-Truncation |
Sets the STRING_RTRUNCATION option, determining whether an error is raised when an INSERT or UPDATE truncates a CHAR or VARCHAR string. |
T-SQL Variables |
Sets the TSQL_VARIABLES option, controlling whether the @ sign can be used as a prefix for Embedded SQL host variable names. |
Disable RI Check |
Sets the DISABLE_RI_CHECK option, allowing LOAD, INSERT, UPDATE, or DELETE operations to bypass the referential integrity check to improve performance. |
Early Predicate Execution |
Sets the EARLY_PREDICATE_EXECUTION option, controlling whether simple local predicates are executed before query optimization. |
Extended Join Syntax |
Sets the EXTENDED_JOIN_SYNTAX option, controlling whether queries with an ambiguous syntax for multi-table joins are allowed or reported as an error. |
Index Advisor |
Sets the INDEX_ADVISOR option, generateing messages suggesting additional column indexes that may improve performance of queries. |
Index Advisor Max Rows |
Sets the INDEX_ADVISOR_MAX_ROWS option, setting the maximum number of unique advice messages stored by the index advisor. |
Infer Subquery Predicates |
Sets the INFER_SUBQUERY_PREDICATES option, controlling the optimizer’s inference of additional subquery predicates. |
Join Optimization |
Sets the the JOIN_OPTIMIZATION option, enabling or disabling the optimization of the join order. |
Large Doubles Accumulator |
Sets the LARGE_DOUBLES_ACCUMULATOR option, controlling which accumulator to use for SUM or AVG of floating-point numbers. |
Minimize Storage |
Sets the MINIMIZE_STORAGE option, minimizing use of disk space for newly created columns. |
No Exec |
Sets the NOEXEC option, generating the optimizer query plans instead of executing the plan. |
Non ANSI NULL Varchar |
Sets the NON_ANSI_NULL_VARCHAR option, controlling whether zero-length VARCHAR data is treated as NULLs for INSERT, lLOAD, and UPDATE operations. |
On Charset Conversion Error |
Sets the ON_CHARSET_CONVERSION_FAILURE option, controlling the action taken on a character conversion error. |
Query Name |
Sets the QUERY_NAME option, giving a name to an executed query in its query plan. |
Query Detail |
Sets the QUERY_DETAIL option, specifying whether additional query information is included in the Query Detail section of the query plan. |
Query Plan |
Sets the QUERY_PLAN option, specifying whether or not additional query plans are printed to the message file. |
Query Plan After Run |
Sets the QUERY_PLAN_AFTER_RUN option, printing the entire query plan after query execution is complete. |
Query Plan as HTML |
Sets the QUERY_PLAN_AS_HTML option, generating graphical query plans in HTML format. |
Query Plan as HTML Directory |
Sets the QUERY_PLAN_AS_HTML_DIRECTORY option, specifying the directory into which Sybase IQ writes HTML query plans. |
Query Rows Returned Limit |
Sets the QUERY_ROWS_RETURNED_LIMIT option, setting the row threshold for rejecting queries based on an estimated size of the result set. |
Query TempSpace Limit |
Sets the QUERY_TEMP_SPACE_LIMIT option, specifying the maximum estimated amount of temp space before rejecting a query. |
Query Timing |
Sets the QUERY_TIMING option, determining whether specific timing statistics are collected and displayed. |
Batch Delimiter |
A delimiting character used to separate multiple SQL statements within a statement batch. |
Row Count |
Lets you limit the number of rows returned by a query. |
Max Errors Before Aborting |
Lets you limit the number of errors returned before aborting a query. |
Run Script With Batch Execution |
When selected, scripts are executed statement by statement (batch delimited). |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |
Teradata Query options
The following settings are available when setting query options against a Teradata datasource:
Query Option | Description |
---|---|
Batch Delimiter |
A delimiting character used to separate multiple SQL statements within a statement batch. |
Row Count |
Lets you limit the number of rows returned by a query. |
Max Errors Before Aborting |
Lets you limit the number of errors returned before aborting a query. |
Run Script With Batch Execution |
When selected, scripts are executed statement by statement (batch delimited). |
Prepare Batch |
For the current SQL Editor window, setting this check box enables bind variable parameterization. This lets you provide named and unnamed variable values as a script executes. For details, see Bind Variable Parameterization in Prepared Statements (Dynamic SQL). |