Sets database options in an Adaptive Server Enterprise-compatible manner.
SET option-name option-value
Database options in Sybase IQ are set using the SET OPTION statement. However, Sybase IQ also provides support for the Adaptive Server Enterprise SET statement for a set of options particularly useful for compatibility.
Option name |
Option value |
|---|---|
ANSINULL |
ON | OFF |
ANSI_PERMISSIONS |
ON | OFF |
CLOSE_ON_ENDTRANS |
ON |
QUOTED_IDENTIFIER |
ON | OFF |
ROWCOUNT |
integer |
STRING_RTRUNCATION |
ON | OFF |
TRANSACTION ISOLATION LEVEL |
0 | 1 | 2 | 3 |
You can set these options using the Transact-SQL SET statement in Sybase IQ, as well as in Adaptive Server Enterprise:
SET ANSINULL { ON | OFF }—The default behavior for comparing values to NULL in Sybase IQ and Adaptive Server Enterprise is different. Setting ANSINULL to OFF provides Transact-SQL compatible comparisons with NULL.
SET ANSI_PERMISSIONS { ON | OFF }—The default behavior in Sybase IQ and Adaptive Server Enterprise regarding permissions required to carry out a DELETE containing a column reference is different. Setting ANSI_PERMISSIONS to OFF provides Transact-SQL-compatible permissions on DELETE.
SET CLOSE_ON_ENDTRANS { ON }—When CLOSE_ON_ENDTRANS is set to ON (the default and only allowable value), cursors are closed at the end of a transaction. With the option set ON, CLOSE_ON_ENDTRANS provides Transact-SQL-compatible behavior.
SET QUOTED_IDENTIFIER { ON | OFF }—Controls whether strings enclosed in double quotes are interpreted as identifiers (ON) or as literal strings (OFF).
SET ROWCOUNT integer—The Transact-SQL ROWCOUNT option limits to the specified integer the number of rows fetched for any cursor. This includes rows fetched by repositioning the cursor. Any fetches beyond this maximum return a warning. The option setting is considered when returning the estimate of the number of rows for a cursor on an OPEN request.
In Sybase IQ, if ROWCOUNT is greater than the number of rows that dbisql can display, dbisql may do extra fetches to reposition the cursor. The number of rows actually displayed may be less than the number requested. Also, if any rows are refetched due to truncation warnings, the count might be inaccurate.
A value of zero resets the option to get all rows.
SET STRING_RTRUNCATION { ON | OFF }—The default behavior in Sybase IQ and Adaptive Server Enterprise when nonspace characters are truncated on assigning SQL string data is different. Setting STRING_RTRUNCATION to ON provides Transact-SQL-compatible string comparisons, including hexadecimal string (binary data type) comparisons.
SET TRANSACTION ISOLATION LEVEL { 0 | 1 | 2 | 3 }—Sets the locking isolation level for the current connection, as described in System Administration Guide: Volume 1 > Transactions and Versioning. For Adaptive Server Enterprise, only 1 and 3 are valid options. For Sybase IQ, only 3 is a valid option.
In addition, this SET statement is allowed by Sybase IQ for compatibility, but has no effect:
SET PREFETCH { ON | OFF }
SQL—Transact-SQL extension to ISO/ANSI SQL grammar.
Sybase—Sybase IQ supports a subset of the Adaptive Server Enterprise database options.
None