Setting up the Execution Environment with Query options

From RapidSQL
Jump to: navigation, search

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:

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:

  1. 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.
  2. 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.

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).

Personal tools