Reference Manual
CHAPTER 5. Database Options
This section lists options alphabetically
AUDITING optionEnables and disables auditing in the database.
ON, OFF
Can be set for the PUBLIC group only. Takes effect immediately. DBA permissions are required to set this option.
OFF
This option switches auditing on and off.
Auditing is the recording of detailed information about many events in the database in the transaction log. Auditing provides some security features, at the cost of some performance.
Turn on auditing
SET OPTION PUBLIC.AUDITING = 'ON'
ALLOW_NULLS_BY_DEFAULT optionControls whether new columns that are created without specifying either NULL or NOT NULL are allowed contain NULL values.
ON, OFF
ON
OFF for Open Client and JDBC connections
The ALLOW_NULLS_BY_DEFAULT option is included for Transact-SQL compatibility.
For more information, see Setting options for Transact-SQL compatibility.
ANSI_BLANKS optionControls behavior when character data is truncated at the client side.
ON, OFF
OFF
ON for Open Client and JDBC connections
The ANSI_BLANKS option has no effect unless the database was created with the -b command-line option. It forces a truncation error whenever a value of data type CHAR(N) is read into a C char(M) variable for values of N greater than or equal to M. With ANSI_BLANKS set to OFF, a truncation error occurs only when at least one non-blank character is truncated.
If ANSI_BLANKS is ON, when you supply a value of data type DT_STRING, the sqllen field must be set to the length of the value, including space for the terminating null character. With ANSI_BLANKS off, the length is determined solely by the position of the null character.
The ANSI_BLANKS setting persists for the life of a connection. Changing it after a connection has been established does not affect that connection.
ANSI_CLOSE_CURSORS_ON_ROLLBACK optionControls whether cursors that were opened WITH HOLD are closed when a ROLLBACK is performed.
ON, OFF
OFF
The draft SQL/3 standard requires all cursors be closed when a transaction is rolled back. By default, on a rollback Adaptive Server Anywhere closes only those cursors that were opened without a WITH HOLD clause. This option allows you to force closure of all cursors.
The CLOSE_ON_ENDTRANS option overrides the ANSI_CLOSE_CURSORS_ON_ROLLBACK option.
ANSI_INTEGER_OVERFLOW optionControls what happens when an arithmetic expression causes an integer overflow error.
ON, OFF
OFF
The ISO SQL/92 standard requires integer overflow should result in an SQLSTATE = 22003 - overflow error. Adaptive Server Anywhere behavior was previously different from this. The ANSI_INTEGER_OVERFLOW option can be set to OFF to maintain compatibility with previous releases of the software.
ANSINULL optionControls the interpretation of = and != with NULL values.
ON, OFF
ON
With ANSINULL ON, any comparisons with NULL using = or != are unknown.
Also, aggregate functions on columns that icontain NULL values cause the warning 'null value eliminated in aggregate function' (SQLSTATE=01003).
Setting ANSINULL to OFF allows comparisons with NULL to yield results that are not unknown, for compatibility with Adaptive Server Enterprise, and turns off the warning.
ANSI_PERMISSIONS optionControls permissions checking for DELETE and UPDATE statements.
ON, OFF
ON
With ANSI_PERMISSIONS ON, the SQL/92 permissions requirements for DELETE and UPDATE statements are checked. The default value is OFF in Adaptive Server Enterprise. The following table outlines the differences.
SQL Statement |
Permissions Required with ansi_permissions off |
Permissions Required with ansi_permissions on |
|---|---|---|
UPDATE |
UPDATE permission on the columns where values are being set |
UPDATE permission on the columns where values are being set SELECT permission on all columns appearing in the WHERE clause. SELECT permission on all columns on the right side of the set clause. |
DELETE |
DELETE permission on the table |
DELETE permission on the table. SELECT permission on all columns appearing in the WHERE clause |
The ANSI_PERMISSIONS option can be set only for the PUBLIC group. No private settings are allowed.
AUTO_COMMIT option [ISQLControls whether a COMMIT is performed after each statement.
ON, OFF
OFF
If AUTO_COMMIT is on, a database COMMIT is performed after each successful statement. If the COMMIT fails, you have the option to execute additional SQL statements and perform the COMMIT again, or execute a ROLLBACK statement.
By default, a COMMIT or ROLLBACK is performed only when the user issues a COMMIT or ROLLBACK statement or a SQL statement that causes an automatic commit (such as the CREATE TABLE statement).
AUTO_REFETCH option [ISQLControls whether query results are fetched again after deletes, updates, and inserts.
ON, OFF
ON
If AUTO_REFETCH is on, the current query results that are displayed in the Data window will be refetched from the database after any INSERT, UPDATE or DELETE statement. Depending on how complicated the query is, this may take some time. For this reason, it can be turned off.
AUTOMATIC_TIMESTAMP optionControls interpretation of new columns with the TIMESTAMP data type.
ON, OFF
OFF
Controls whether any new columns with the TIMESTAMP data type that do not have an explicit default value defined are given a default value of the Transact-SQL timestamp value as a default. The AUTOMATIC_TIMESTAMP option is included for Transact-SQL compatibility. The default is OFF.
For more information, see Setting options for Transact-SQL compatibility.
BACKGROUND_PRIORITY optionTo limit impact on the performance of connections other than the current connection.
ON or OFF
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
OFF
When set to ON, it requests that the current connection have minimal impact on the performance of other connections. This option allows tasks for which responsiveness is critical to coexist with other tasks for which performance is not as important.
BELL option [ISQLControls whether the bell sounds when an error occurs.
ON, OFF
ON
Set this option according to your preference.
BLOB_THRESHOLD optionControls the size of value that the Message Agent treats as a long object (blob).
Integer, in kilobytes
256
Any value longer than the BLOB_THRESHOLD option is replicated as a blob. That is, it is broken into pieces and replicated in chunks, before being reconstituted by using a SQL variable and concatenating the pieces at the recipient site.
If you set BLOB_THRESHOLD to a high value in remote Adaptive Server Anywhere databases, blobs are not broken into pieces, and operations can be applied to Adaptive Server Enterprise by the Message Agent. Each SQL statement must fit within a message, so this only allows replication of small blobs.
BLOCKING optionControls the behavior in response to locking conflicts.
ON or OFF
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
ON
If BLOCKING is ON, any transaction that attempts to write must wait until every conflicting transaction releases its read lock. At that time, the write goes through. If BLOCKING is OFF, the transaction that attempts to write receives an error.
For more information, see Transaction blocking and deadlock.
CHAINED optionControls transaction mode in the absence of a BEGIN TRANSACTION statement.
ON, OFF
ON
OFF for Open Client and JDBC connections
Controls the Transact-SQL transaction mode. In Unchained mode (CHAINED = OFF), each statement is committed individually unless an explicit BEGIN TRANSACTION statement is executed to start a transaction. In chained mode (CHAINED = ON) a transaction is implicitly started before any data retrieval or modification statement.
CHAR_OEM_TRANSLATION option [ISQLControls whether ANSI-to-OEM code page translation is carried out.
ON, OFF, DETECT
DETECT
Each time Interactive SQL connects to a database or uses SET CONNECTION, it determines the setting of Char_OEM_Translation.
If the option is set to ON or OFF, translation is set accordingly. If the option is set to DETECT (the recommended and default setting), Interactive SQL fetches the collation label from the database and examines it. If the collation label starts with a string that generally indicates an ANSI code page, then Interactive SQL turns off translation.
The strings that indicate ANSI code pages are as follows:
WIN_
ISO_
SJIS
EUC_
UTF
EBCDIC
813
819
For all other prefixes, translation is turned on. When the option is set to DETECT, Interactive SQL displays a message in the status window indicating the collation label and the display translation setting.
CHECKPOINT_TIME optionSet the maximum number of minutes that the database server will run without doing a checkpoint.
Integer
Can be set only for the PUBLIC group. You must shut down and restart the database server for the change to take effect.
60
This option is used with the RECOVERY_TIME option to decide when checkpoints should be done.
For information on checkpoints, see The checkpoint log.
CLOSE_ON_ENDTRANS optionControls the closing of cursors at the end of a transaction.
ON, OFF
ON
When CLOSE_ON_ENDTRANS is set to ON, cursors are closed whenever a transaction is committed unless the cursor was opened WITH HOLD. The behavior when a transaction is rolled back is governed by the ANSI_CLOSE_CURSORS_AT_ROLLBACK option.
When CLOSE_ON_ENDTRANS is set to OFF, cursors are not closed at either a commit or a rollback, regardless of the ANSI_CLOSE_CURSORS_AT_ROLLBACK option setting or whether the cursor was opened WITH HOLD or not.
Setting this to OFF provides Adaptive Server Enterprise compatible behavior.
COMMAND_DELIMITER option [ISQLSets the string that indicates the end of a statement in Interactive SQL.
String
Semi-colon (;)
If the command delimiter is set to a string beginning with a character that is valid in identifiers, the command delimiter must be preceded by a space.
COMMIT_ON_EXIT option [ISQLControls behavior when Interactive SQL disconnects or terminates.
ON, OFF
ON
Controls whether a COMMIT or ROLLBACK is done when you leave Interactive SQL. When COMMIT_ON_EXIT is set to ON, a COMMIT is done; otherwise a ROLLBACK is done.
CONTINUE_AFTER_RAISE_ERROR optionControls behavior following a RAISERROR statement.
ON, OFF
OFF
ON for Open Client and JDBC connections
Deprecated option For more information, see ON_TSQL_ERROR option. |
The RAISERROR statement is used within procedures and triggers to generate an error. When this option is set to OFF, the execution of the procedure or trigger is stopped whenever the RAISERROR statement is encountered.
If you set the CONTINUE_AFTER_RAISERROR switch to ON, the RAISERROR statement no longer signals an execution-ending error. Instead, the RAISERROR status code and message are stored and the most recent RAISERROR is returned when the procedure completes. If the procedure which caused the RAISERROR was called from another procedure, the RAISERROR is not returned until the outermost calling procedure terminates.
Intermediate RAISERROR statuses and codes are lost after the procedure terminates. If, at return time, an error occurs along with the RAISERROR, then the information for the new error is returned and the RAISERROR information is lost. The application can query intermediate RAISERROR statuses by examining the @@error global variable at different execution points.
CONVERSION_ERROR optionControls the reporting of data type conversion failures on fetching information from the database.
ON, OFF
ON
This option controls whether data type conversion failures, when data is fetched from the database or inserted into the database, are reported by the database as errors (CONVERSION_ERROR set to ON) or as a warning (CONVERSION_ERROR set to OFF).
When CONVERSION_ERROR is set to ON, the SQLE_CONVERSION_ERROR error is generated. If the option is set to OFF, the warning SQLE_CANNOT_CONVERT is produced.
If conversion errors are reported as warnings only, the NULL value is used in place of the value that could not be converted. In Embedded SQL, an indicator variable is set to -2 for the column or columns that cause the error.
COOPERATIVE_COMMITS optionControls when commits are written to disk.
ON or OFF
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
ON
If COOPERATIVE_COMMITS is set to OFF, a COMMIT is written to disk as soon as the database server receives it, and the application is then allowed to continue.
If COOPERATIVE_COMMITS is set to ON (the default), the database server does not immediately write the COMMIT to the disk. Instead, the application waits for up to the maximum length set by the COOPERATIVE_COMMIT_TIMEOUT option for something else to put on the pages before they are written to disk.
Setting COOPERATIVE_COMMITS to ON, and increasing the COOPERATIVE_COMMIT_TIMEOUT setting, increases overall database server throughput by cutting down the number of disk I/Os, but at the expense of a longer turnaround time for each individual connection.
If both COOPERATIVE_COMMITS and DELAYED_COMMITS are set to ON, and the COOPERATIVE_COMMIT_TIMEOUT interval passes without the pages getting written, the application is resumed (as if the commit had worked), and the remaining interval (DELAYED_COMMIT_TIMEOUT - COOPERATIVE_COMMIT_TIMEOUT) is used as a DELAYED_COMMIT interval. The pages will then be written, even if they are not full.
COOPERATIVE_COMMIT_TIMEOUT optionGoverns when a COMMIT entry in the transaction log is written to disk.
Integer, in milliseconds
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
250
This option has meaning only when COOPERATIVE_COMMITS is set to ON. The database server waits for the specified number of milliseconds for other connections to fill a page of the log before writing to disk. The default setting is 250 milliseconds.
DATE_FORMAT optionSets the format for dates retrieved from the database.
String
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
'YYYY-MM-DD'. This corresponds to ISO date format specifications.
The format is a string using the following symbols:
Symbol |
Description |
|---|---|
yy |
Two digit year |
yyyy |
Four digit year |
mm |
Two digit month, or two digit minutes if following a colon(as in hh:mm) |
mmm[m...] |
Character short form for months—as many characters as there are m's |
d |
Single digit day of week, (0 = Sunday, 6 = Saturday) |
dd |
Two digit day of month |
ddd[d...] |
Character short form for day of the week |
hh |
Two digit hours |
nn |
Two digit minutes |
ss[.ss..] |
Seconds and parts of a second |
aa |
AM or PM (12 hour clock) |
pp |
PM if needed (12 hour clock) |
jjj |
Day of the year, from 1 to 366. |
Each symbol is substituted with the appropriate data for the date that's being formatted. Any format symbol that represents character rather than digit output can be put in upper case which causes the substituted characters to also be in upper case. For numbers, using mixed case in the format string suppresses leading zeros.
You can control the padding of numbers by changing the case of the symbols. Same-case symbols (MM, mm, DD, dd) all pad number with zeroes. Mixed case (Mm, mM, Dd, or dD) cause the number to not be zero padded: the value takes as much room as required. For example
SELECT dateformat( '1998/01/01', 'yyyy/Mm/Dd')
returns the following value:
1998/1/1
The following table illustrates DATE_FORMAT settings, together with the output from the following statement, executed on Thursday May 21, 1998
SELECT CURRENT DATE
DATE_FORMAT |
SELECT CURRENT DATE |
|---|---|
yyyy/mm/dd/ddd |
1998/05/21/thu |
jjj |
141 |
mmm yyyy |
may 1998 |
mm-yyyy |
05-1998 |
DATE_ORDER optionControls the interpretation of date formats.
'MDY', 'YMD', or 'DMY'
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
YMD. This corresponds to ISO date format specifications.
For Open Client and JDBC connections, the default is set to MDY.
The database option DATE_ORDER is used to determine whether 10/11/12 is Oct 11 1912, Nov 12 1910, or Nov 10 1912. The option can have the value 'MDY', 'YMD', or 'DMY'.
DEFAULT_TIMESTAMP_INCREMENT optionSpecifies the number of microseconds to add to a column of type TIMESTAMP in order to keep values in the column unique.
Integer
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
1
Since a TIMESTAMP value is precise to six decimal places in Adaptive Server Anywhere, by default 1 microsecond (0.000001 of a second) is added to differentiate between two identical TIMESTAMP values.
Some software, such as Microsoft Access, truncates TIMESTAMP values to three decimal places, making valid comparisons a problem. Setting this value to a larger number, such as 0.001 of a second (an option value of 100), alleviates this problem.
DELAYED_COMMITS optionDetermines when the server returns control to an application following a COMMIT.
ON or OFF
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
OFF. This corresponds to ISO COMMIT behavior.
When set to ON, the database server replies to a COMMIT statement immediately instead of waiting until the transaction log entry for the COMMIT has been written to disk. When set to OFF, the application must wait until the COMMIT is written to disk.
When this option is ON, the log is written to disk when the log page is full or according to the DELAYED_COMMIT_TIMEOUT option setting, whichever is first. There is a slight chance that a transaction may be lost even though committed if a system failure occurs after the server replies to a COMMIT, but before the page is written to disk. Setting DELAYED_COMMITS to ON, and the DELAYED_COMMIT_TIMEOUT option to a high value, promotes a quick response time at the cost of security.
If both COOPERATIVE_COMMITS and DELAYED_COMMITS are set to ON, and if the COOPERATIVE_COMMIT_TIMEOUT interval passes without the pages getting written, the application is resumed (as if the commit had worked), and the remaining interval (DELAYED_COMMIT_TIMEOUT - COOPERATIVE_COMMIT_TIMEOUT) is used as a DELAYED_COMMIT interval, after which the pages will be written, even if they are not full.
DELAYED_COMMIT_TIMEOUT optionDetermines when the server returns control to an application following a COMMIT.
Integer, in milliseconds.
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
500
This option has meaning only when DELAYED_COMMITS is set to ON. it governs when a COMMIT entry in the transaction log is written to disk. With DELAYED_COMMITS set to ON, the database engine waits for the number of milliseconds set in the DELAYED_COMMIT_TIMEOUT option for other connections to fill a page of the log before writing the current page contents to disk.
For more information, see DELAYED_COMMITS option.
DELETE_OLD_LOGS optionControls whether transaction logs are deleted when their messages have been replicated.
ON, OFF
OFF
This option is used by SQL Remote and by the Adaptive Server Anywhere Replication Agent. The default setting is OFF. When it is set to ON, the Message Agent (DBREMOTE) deletes each old transaction log when all the changes it contains have been sent and confirmed as received.
DESCRIBE_JAVA_FORMAT option [ISQL]Controls whether Java objects are interpreted as strings (for display) or as binary (for loading and unloading).
varchar, binary
OFF
When set to varchar, Interactive SQL converts all data fetched from the database to strings. The database server calls the toString() method on Java columns to provide formatted output for the Interactive SQL data window.
When set to binary, Interactive SQL does no data conversion.
DIVIDE_BY_ZERO_ERROR optionControls the reporting of division by zero.
ON, OFF
ON
This option indicates whether division by zero is reported as an error. If the option is set ON, then division by zero results in an error with SQLSTATE 22012.
If the option is set OFF, division by zero is not an error. Instead, a NULL is returned.
ECHO option [ISQLControls whether statements are echoed before they are executed.
ON, OFF
ON
This option is most useful when you use the READ statement to execute a Interactive SQL command file.
ESCAPE_CHARACTER optionThis option is reserved for system use. Do not change the setting of this option.
FIRE_TRIGGERS optionControls whether triggers are fired in the database.
ON, OFF
ON
When set to ON, triggers are fired. When set to OFF, no triggers are fired, including referential integrity triggers (such as cascading updates and deletes). Only a user with DBA authority can set this option. The option is overridden by the -gf command-line option, which turns off all trigger firing regardless of the FIRE_TRIGGERS setting.
This option is relevant when replicating data from Adaptive Server Enterprise to Adaptive Server Anywhere, because all actions from Adaptive Server Enterprise transaction logs are replicated to Adaptive Server Anywhere, including actions carried out by triggers.
FLOAT_AS_DOUBLE optionControls the interpretation of the FLOAT keyword.
ON, OFF
OFF
ON for Open Client and JDBC connections
The FLOAT_AS_DOUBLE option makes the FLOAT keyword behave like Adaptive Server Enterprise's FLOAT keyword when a precision is not specified.
When enabled (set to ON), all occurrences of the keyword FLOAT are interpreted as equivalent to the keyword DOUBLE within SQL statements.
By default, Adaptive Server Anywhere FLOAT values are interpreted by Adaptive Server Enterprise as REAL values. Since Adaptive Server Enterprise treats its own FLOAT values as DOUBLE, enabling this option makes Adaptive Server Anywhere treat FLOAT values in the same way Enterprise treats FLOAT values.
REAL values are four bytes; DOUBLE values are eight bytes. According to the ANSI SQL/92 specification, FLOAT can be interpreted based on the platform. It is up to the database to decide what size the value is, so long as it can handle the necessary precision. Adaptive Server Enterprise and Adaptive Server Anywhere exhibit different default behavior.
The FLOAT_AS_DOUBLE option takes effect only when no precision is specified. For example, the following statement is not affected by the option setting:
create table t1( c1 float(5) )
The following statement is affected by the option setting:
create table t2( c1 float) // affected by option setting
HEADINGS option [ISQLControls whether headings will be displayed for the results of a SELECT statement.
ON, OFF
ON
Set this option according to your preference.
INPUT_FORMAT option [ISQLSets the default data format expected by the INPUT statement.
String. See below.
ASCII
Certain file formats contain information about column names and types. Using this information, the INPUT statement will create the database table if it does not already exist. This is a very easy way to load data into the database. The formats that have enough information to create the table are: DBASEII, DBASEIII, DIF, FOXPRO, LOTUS, and WATFILE.
Allowable input formats are:
ASCII Input lines are assumed to be ASCII characters, one row per line, with values separated by commas. Alphabetic strings may be enclosed in apostrophes (single quotes) or quotation marks (double quotes). Strings containing commas must be enclosed in either single or double quotes. If single or double quotes are used, double the quote character to use it within the string. Optionally, you can use the DELIMITED BY clause to specify a different delimiter string than the default, which is a comma(,).
Three other special sequences are also recognized. The two characters \n represent a newline character, \\ represents a single backslash character, and the sequence \xDD represents the character with hexadecimal code DD.
DBASE The file is in dBASE II or dBASE III format. Interactive SQL will attempt to determine which format, is based on information in the file. If the table doesn't exist, it will be created.
DBASEII The file is in dBASE II format. If the table doesn't exist, it will be created.
DBASEIII The file is in dBASE III format. If the table doesn't exist, it will be created.
DIF The file is in Data Interchange Format. If the table doesn't exist, it will be created.
FIXED Input lines are in fixed format. The width of the columns can be specified using the COLUMN WIDTHS clause. If they are not specified, column widths in the file must be the same as the maximum number of characters required by any value of the corresponding database column's type.
FOXPRO The file is in FoxPro format. The FoxPro memo field is different than the dBASE memo field. If the table doesn't exist, it will be created.
LOTUS The file is a Lotus WKS format worksheet. INPUT assumes that the first row in the Lotus WKS format worksheet consists of column names. If the table doesn't exist, it will be created. In this case, the types and sizes of the columns created may not be correct because the information in the file pertains to a cell, not to a column.
WATFILE The input will be a WATFILE file. If the table doesn't exist, it will be created.
ISQL_LOG option [ISQLControls logging behavior.
String containing a file name.
Empty string.
If ISQL_LOG is set to a non-empty string, all Interactive SQL statements are added to the end of the named file. Otherwise, if ISQL_LOG is set to the empty string, Interactive SQL statements are not logged.
Individual session only |
ISOLATION_LEVEL optionControls the locking isolation level.
0, 1, 2, or 3
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
0
1 for Open Client and JDBC connections
This option controls the locking isolation level as follows.
0 Allow dirty reads, nonrepeatable reads, and phantom rows.
1 Prevent dirty reads. Allow nonrepeatable reads and phantom rows.
2 Prevent dirty reads and guarantee repeatable reads. Allow phantom rows.
3 Serializable. Do not allow dirty reads, guarantee repeatable reads, and do not allow phantom rows.
For more information, see Isolation levels and consistency.
JAVA_HEAP_SIZE optionTo limit the memory used by Java applications for a connection.
Integer
Can be set for an individual connection or the PUBLIC group. Takes effect immediately. DBA permissions are required to set this option for any connection.
1000000
This option sets the maximum size (in bytes) of the memory that is allocated to Java applications on a per-connection basis. Per-connection memory allocations typically consist of the user's working set of allocated Java variables and Java application stack space.
While a Java application is executing on a connection, the per-connection allocations come out of the fixed cache of the database server, so it is important that a run-away Java application is prevented from using up too much memory.
JAVA_NAMESPACE_SIZE optionTo limit the memory used by Java applications for a connection.
Integer
Can be set only for the PUBLIC group. Takes effect immediately.
4000000
This option sets the maximum size (in bytes) of the memory that is allocated to Java applications on a per-database basis.
Per-database memory allocations include Java class definitions. Because class definitions are effectively read-only, they are shared between connections. Consequently, their allocations come right out of the fixed cache, and this option sets a limit on the size of these allocations.
LOGIN_MODE optionControls the use of integrated logins for the database.
Standard, Mixed, or Integrated
Can be set only for the PUBLIC group. Takes effect immediately.
Standard
This option specifies whether integrated logins are permitted. The following values are accepted (the values are case insensitive):
Standard This is the default setting, which does not permit integrated logins. An error occurs if an integrated login connection is attempted.
Mixed With this setting, both integrated logins and standard logins are allowed.
Integrated With this setting, all logins to the database must be made using integrated logins.
Caution
Setting the LOGIN_MODE database option to Integrated restricts connections to only those users who have been granted an integrated login mapping. Attempting to connect with a user ID and password generates an error. The only exceptions to this are users with DBA authority (full administrative rights).
For more information on integrated logins see Using an integrated login
LOGIN_PROCEDURE optionA login procedure that sets connection compatibility options at startup. By default the procedure calls the sp_login_environment procedure to determine which options to set.
String
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
sp_login_environment
This login procedure calls the sp_login_environment procedure at run time to determine the database connection settings.
You can customize the default database option settings by creating a new procedure and setting LOGIN_PROCEDURE to call the new procedure. You should not edit either sp_login_procedure or sp_tsql_environment.
MAX_CURSOR_COUNT optionA resource governor to limit the maximum number of cursors that a connection can use at once.
Integer
Can be set for an individual connection or the PUBLIC group. Takes effect immediately. DBA permissions are required to set this option for any connection.
50
This resource governor allows a DBA to limit the number of cursors per connection that a user can use. If an operation would exceed the limit for a connection, an error is generated, indicating that the governor for the resource has been exceeded.
If a connection executes a stored procedure, that procedure is executed under the permissions of the procedure owner. However, the resources used by the procedure are assigned to the current connection.
You can remove resource limits by setting the option to 0 (zero).
MAX_STATEMENT_COUNT optionA resource governor to limit the maximum number of prepared statements that a connection can use at once.
Integer
Can be set for an individual connection or the PUBLIC group. Takes effect immediately. DBA permissions are required to set this option for any connection.
50
This resource governor allows a DBA to limit the number of prepared statements per connection a user can use. If an operation would exceed the limit for a connection, an error is generated, indicating that the governor for the resource has been exceeded.
If a connection executes a stored procedure, that procedure is executed under the permissions of the procedure owner. However, the resources used by the procedure are assigned to the current connection.
You can remove resource limits by setting the option to 0 (zero).
MIN_PASSWORD_LENGTH optionSets the minimum length for new passwords in the database.
Integer, greater than or equal to zero.
The value is in bytes. For single-byte character sets, this is the same as the number of characters.
Can be set for the PUBLIC group. Takes effect immediately. DBA permissions are required to set this option.
0 characters
This option allows the database administrator to impose a minimum length on all new passwords for greater security. Existing passwords are not affected.
Set the minimum length for new passwords to 6 bytes.
SET OPTION PUBLIC.MIN_PASSWORD_LENGTH = 6
NEAREST_CENTURY optionControls the interpretation of two-digit years, in string-to-date conversions.
Integer between 0 and 100
50 for databases created with Version 6 or later.
0 for databases created with Version 5.5 or earlier.
This option controls the handling of two-digit years, when converting from strings to dates or timestamps.
The NEAREST_CENTURY setting is a numeric value that acts as a rollover point. Two digit years less than the value are converted to 20yy, while years greater than or equal to the value are converted to 19yy.
The historical Adaptive Server Anywhere behavior is to add 1900 to the year. Adaptive Server Enterprise behavior is to use the nearest century, so any year value yy is less than 50, the year is set to 20yy.
NON_KEYWORDS optionTurns off individual keywords, allowing their use as identifiers.
String
The empty string.
This option turns off individual keywords, or all keywords introduced since a specific release of the product. This provides a way of ensuring that applications created with older versions of the product are not broken by new keywords. If you have an identifier in your database that is now a keyword, you can either add double quotes around the identifier in all applications or scripts, or turn off the keyword using the NON_KEYWORDS option.
In addition to specifying individual keywords, you can turn off all keywords since a specified release, using one of the following special values in the list of keywords:
keywords_4_0_d, keywords_4_0_c, keywords_4_0_b, keywords_4_0_a, keywords_4_0, keywords_5_0_01, keywords_5_0
The following statement prevents TRUNCATE and SYNCHRONIZE from being recognized as keywords:
SET OPTION NON_KEYWORDS = 'TRUNCATE, SYNCHRONIZE'
The following statement prevents all keywords introduced since release 4.0d from being recognized as keywords:
SET OPTION NON_KEYWORDS = 'keywords_4_0_d'
Each new setting of this option replaces the previous setting. The following statement clears all previous settings.
SET OPTION NON_KEYWORDS =
A side-effect of this options is that SQL statements that use a turned off keyword cannot be used: they produce a syntax error.
NULLS option [ISQLSpecifies how NULL values in the database are displayed.
ON, OFF
(NULL)
Set this according to your preference.
ON_ERROR option [ISQLControls what happens if an error is encountered while reading statements from a command file.
String. See below for allowed values.
PROMPT
Controls what happens if an error is encountered while reading statements from a command file, as follows:
STOP Interactive SQL stops reading statements from the file and returns to the statement window for input.
PROMPT Interactive SQL prompts the user to see if the user wishes to continue.
CONTINUE The error is ignored and Interactive SQL continues reading statements from the command file. The INPUT statement continues with the next row, skipping the row that caused the error.
EXIT Interactive SQL terminates.
NOTIFY_CONTINUE The error is displayed in a message box with a single button. Execution continues once the button is clicked.
NOTIFY_STOP The error is displayed in a message box with a single button. Execution of the script stops once the button is clicked.
NOTIFY_EXIT The error is displayed in a message box with a single button. Interactive SQL terminates once the button is clicked.
ON_TSQL_ERROR optionControls what happens if an error is encountered in a stored procedure/
String. See below for allowed values.
CONDITIONAL
Transact-SQL procedure language overview
Controls what happens if an error is encountered while executing a Transact-SQL stored procedure.
STOP Stop execution immediately upon finding an error.
CONDITIONAL If the procedure uses ON EXCEPTION RESUME, and the statement following the error handles the error, continue, otherwise exit.
CONTINUE Continue execution, regardless of the following statement. If there are multiple errors, the first error encountered in the stored procedure is returned. This option most closely mirrors Adaptive Server Enterprise behavior.
This option deprecates CONTINUE_AFTER_RAISEERROR as a way of simulating Adaptive Server Enterprise behavior for stored procedures..
OUTPUT_FORMAT option [ISQLSets the output format for the data retrieved by the SELECT statement and redirected into a file, or output using the OUTPUT statement.
String. See below for allowed values.
ASCII
The valid output formats are:
ASCII The output is an ASCII format file with one row per line in the file. All values are separated by commas, and strings are enclosed in apostrophes (single quotes). The delimiter and quote strings can be changed using the DELIMITED BY and QUOTE clauses. If ALL is specified in the QUOTE clause, then all values (not just strings) will be quoted.
Three other special sequences are also used. The two characters \n represent a newline character, \\ represents a single backslash character, and the sequence \xDD represents the character with hexadecimal code DD.
DBASEII The output is a dBASE II format file with the column definitions at the top of the file. Note that a maximum of 32 columns can be output. Also, note that columns longer than 255 characters will be truncated in the file.
DBASEIII The output is a dBASE III format file with the column definitions at the top of the file. Note that a maximum of 128 columns can be output. Also, note that columns longer than 255 characters will be truncated in the file.
DIF The output is a file in the standard Data Interchange Format.
FIXED The output is fixed format, with each column having a fixed width. The width for each column can be specified using the COLUMN WIDTH clause. If this clause is omitted, the width for each column is computed from the data type for the column, and is large enough to hold any value of that data type. No column headings are output in this format.
FOXPRO The output is a FoxPro format file (the FoxPro memo field is different than the dBASE memo field) with the column definitions at the top of the file. Note that a maximum of 128 columns can be output. Also, note that columns longer than 255 characters will be truncated in the file.
LOTUS The output is a Lotus WKS format worksheet. Column names will be put as the first row in the worksheet. Note that there are certain restrictions on the maximum size of Lotus WKS format worksheets that other software (such as Lotus 1-2-3) can load. There is no limit to the size of file Interactive SQL can produce.
SQL The output is a Interactive SQL INPUT statement required to recreate the information in the table.
TEXT The output is a TEXT format file, which prints the results in columns with the column names at the top and vertical lines separating the columns. This format is similar to that used to display data in the Interactive SQL data window.
WATFILE The output is a WATFILE format file, with the column definitions at the top of the file.
OUTPUT_LENGTH option [ISQLControls the length used when Interactive SQL exports information to an external file.
Integer
0 (no truncation)
This option controls the length used when Interactive SQL exports information to an external file (using output redirection or the OUTPUT statement).
PERCENT_AS_COMMENT optionControls the interpretation of the percent character.
ON, OFF
ON
It is recommended that you not use % as a comment marker.
Versions of this product before Version 6 treated the percent character (%) in SQL statements exclusively as a comment delimiter. Since Version 5, alternative comment markers such as //, /* */, and -- (double dash) have been available. The double-dash style is the SQL/92 comment delimiter.
Adaptive Server Enterprise treats % as a modulo operator and does not support the Adaptive Server Anywhere mod function. Writing a statement that works in both environments and performs a modulo operation was previously impossible.
The PERCENT_AS_COMMENT option controls the meaning of %. The default setting for is ON, for backwards compatibility. You can set the option to OFF for compatibility with Adaptive Server Enterprise.
Procedures, triggers and views that were created with %-style comments are converted to double-dash comments when they are stored in the catalog.
Existing procedures must be recreated before changing option |
The Sybase Central code editor does not highlight %-style comments. If you wish to have your comments highlighted in the Sybase Central editor, you should use one of the other comment delimiters.
PRECISION optionSpecifies the maximum number of digits in the result of any decimal arithmetic.
Integer, with a maximum of 127
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
30
Precision is the total number of digits to the left and right of the decimal point. The SCALE option specifies the minimum number of digits after the decimal point when an arithmetic result is truncated to the maximum PRECISION.
Multiplication, division, addition, subtraction, and aggregate functions can all have results that exceed the maximum precision.
For example, when a DECIMAL(8,2) is multiplied with a DECIMAL(9,2), the result could require a DECIMAL(17,4). If PRECISION is 15, only 15 digits will be kept in the result. If SCALE is 4, the result will be a DECIMAL(15,4). If SCALE is 2, the result will be a DECIMAL(15,2). In both cases, there is a possibility of overflow.
PREFETCH optionThe PREFETCH option acts as a toggle allowing you to turn fetching on and off.
ON, OFF
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
ON
This option controls whether rows are fetched to the client side in advance of being made available to the client application. Fetching a number of rows at a time, even when the client application requests rows one at a time (for example, when looping over the rows of a cursor) both cuts down on response time and improves overall throughput by cutting down the number of requests to the database.
The setting of PREFETCH is ignored by Open Client and JDBC connections.
QUERY_PLAN_ON_OPEN optionControls whether a plan is returned when a cursor is opened.
ON, OFF
OFF
In early versions of the software, each time an OPEN was done on a cursor, the server would return in the SQLCA sqlerrmc field a string representing the query plan (limited to 70 bytes). A more complete description can be obtained using the EXPLAIN statement or the PLAN function. For this reason, computing and returning the query plan on an OPEN is needed only for compatibility with old applications. The QUERY_PLAN_ON_OPEN option controls whether the plan is returned on an OPEN. By default, the setting is OFF.
QUOTED_IDENTIFIER optionControls the interpretation of strings that are enclosed in double quotes.
ON, OFF
ON
OFF for Open Client and JDBC connections
This option controls whether strings that are enclosed in double quotes are interpreted as identifiers (ON) or as literal strings (OFF). The QUOTED_IDENTIFIER option is included for Transact-SQL compatibility.
Sybase Central resents QUOTED_IDENTIFER temporarily to ON if it is set to OFF. A message is displayed informing you of this change. The change is in effect only for the Sybase Central connection.
For more information, see Setting options for Transact-SQL compatibility.
RECOVERY_TIME optionSets the maximum length of time, in minutes, that the database server will take to recover from system failure.
Integer, in minutes
Can be set only for the PUBLIC group. Takes effect when server is restarted.
2
This option is used with the CHECKPOINT_TIME option to decide when checkpoints should be done.
Adaptive Server Anywhere uses a heuristic to estimate the recovery time based on the operations that have been performed since the last checkpoint. Thus, the recovery time is not exact.
For more information, see Recovery from system failure.
ROW_COUNTS optionSpecifies whether the database will always count the number of rows in a query when it is opened.
ON, OFF
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
OFF
If this option is set to OFF, the row count is usually only an estimate. If this option is set to ON, the row count is always accurate. Opening queries may take significantly longer.
REPLICATE_ALL optionAllows an entire database to act as a primary site in a Replication Server setup.
ON, OFF
OFF
This option is used by the LTM only. When it is set to ON, the entire database is set to act as a primary site in a Replication Server installation. All changes to the database are sent to Replication Server by the LTM.
REPLICATION_ERROR optionFor SQL Remote, allows you to specify a stored procedure to be called by the Message Agent when a SQL error occurs.
Stored procedure name.
No procedure
For SQL Remote, the REPLICATION_ERROR option allows you to specify a stored procedure to be called by the Message Agent when a SQL error occurs. By default no procedure is called.
The procedure must have a single argument of type CHAR, VARCHAR, or LONG VARCHAR. The procedure is called once with the SQL error message and once with the SQL statement that causes the eror.
Although the option allows you to track and monitor SQL errors in replication, you must still design them out of your setup; this option is not intended to resolve such errors.
RI_Trigger_time optionControls the relative timing of referential integrity checks and trigger actions.
BEFORE, AFTER
AFTER
The option can be set to either BEFORE or AFTER. When it's set to AFTER, referential integrity actions are executed after the UPDATE or DELETE.
Only the PUBLIC setting can be used; any other setting is ignored.
Prior to release 5.5, the referential integrity triggers were always fired BEFORE. Starting with release 5.5, the default has been AFTER.
The reason for the change of default behavior is that BEFORE triggers can lead to infinitely recursing triggers, when delete self-referencing rows.
SCALE optionSpecifies the minimum number of digits after the decimal point when an arithmetic result is truncated to the maximum PRECISION.
Integer, with a maximum of 127.
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
6
Multiplication, division, addition, subtraction, and aggregate functions can all have results that exceed the maximum precision. See PRECISION option for an example.
SQL_FLAGGER_ERROR_LEVEL optionControls the response to any SQL that is not part of a specified set of SQL/92.
E, I, F, or W
W
This option flags any SQL that is not part of a specified set of SQL/92 as an error.
The allowed values of level are as follows:
E Flag syntax that is not entry-level SQL/92 syntax
I Flag syntax that is not intermediate-level SQL/92 syntax
F Flag syntax that is not full-SQL/92 syntax
W Allow all supported syntax
SQL_FLAGGER_WARNING_LEVEL optionControls the response to any SQL that is not part of a specified set of SQL/92.
E, I, F, or W
W
This option flags any SQL that is not part of a specified set of SQL/92 as a warning.
The allowed values of level are as follows:
E Flag syntax that is not entry-level SQL/92 syntax
I Flag syntax that is not intermediate-level SQL/92 syntax
F Flag syntax that is not full-SQL/92 syntax
W Allow all supported syntax
STATISTICS option [ISQLControls whether execution times, optimization strategies and other statistics are displayed in the statistics window.
0, 3, 4, 5, or 6.
3
When the option is set to 0, the statistics window is not displayed. Otherwise, the value represents the height of the statistics window, in lines.
STRING_RTRUNCATION optionDetermines whether an error is raised when an INSERT or UPDATE truncates a CHAR or VARCHAR string.
ON, OFF
OFF
If the truncated characters consist only of spaces, no exception is raised. The setting of ON corresponds to ANSI/ISO SQL/92 behavior. When it is set to OFF, the exception is not raised and the character string is silently truncated.
SUBSCRIBE_BY_REMOTE optionControls interpretation of NULL or empty-string SUBSCRIBE BY values.
ON, OFF
ON
When the option is set to ON, operations from remote databases on rows with a SUBSCRIBE BY value that is NULL or an empty string assume that the remote user is subscribed to the row. When it is set to OFF, the remote user is assumed not to be subscribed to the row.
TEXTSIZE optionControls the maximum size (in bytes) of text or image type data to be returned with a SELECT statement.
Integer, in bytes.
32765 bytes
The @@textsize global variable stores the current setting. To reset to the default size (32765 bytes), which is also the maximum value, use the statement:
set textsize 0
THREAD_COUNT optionHistorical
Integer
Not applicable
This option is now ignored by the server. For Version 6 you should use the -gn server command-line option to set the maximum number of requests that the server handles simultaneously.
For information on the command-line switch, see The database server.
TIME_FORMAT optionSets the format for times retrieved from the database.
A string composed of the symbols listed below.
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
HH:NN:ss.SSS
For Open Client and JDBC connections the default is set to HH:NN:SS.SSS.
The format is a string using the following symbols:
hh Two digit hours (24 hour clock)
nn Two digit minutes
mm Two digit minutes if following a colon (as in hh:mm)
ss[.s...] Two digit seconds plus optional fraction
Each symbol is substituted with the appropriate data for the time that is being formatted. Any format symbol that represents character rather than digit output can be put in uppercase, which causes the substituted characters to also be in uppercase. For numbers, using mixed case in the format string suppresses leading zeros.
TIMESTAMP_FORMAT optionSets the format for timestamps that are retrieved from the database.
A string composed of the symbols listed below.
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
YYYY-MM-DD HH:NN:ss.SSS
For Open Client and JDBC connections the default is set to YYYY-MM-DD HH:NN:SS.SSS.
The format is a string using the following symbols:
Symbol |
Description |
|---|---|
yy |
Two digit year |
Yyyy |
Four digit year |
Mm |
Two digit month, or two digit minutes if following a colon(as in 'hh:mm') |
Mmm[m...] |
Character short form for months—as many characters as there are m's |
dd |
Two digit day of month |
Ddd[d...] |
Character short form for day of the week |
hh |
Two digit hours |
nn |
Two digit minutes |
ss.ssssss |
Seconds and fractions of a second, up to six decimal places. Not all platforms support timestamps to a precision of six places. |
aa |
am or pm (12 hour clock) |
pp |
Pm if needed (12 hour clock) |
f |
Use French days and months (deprecated) |
Each symbol is substituted with the appropriate data for the timestamp that is being formatted. Any format symbol that represents character rather than digit output can be put in uppercase, which causes the substituted characters to also be in uppercase. For numbers, using mixed case in the format string suppresses leading zeros.
TRUNCATION_LENGTH option [ISQLControls the truncation of wide columns for displays to fit on a screen.
Integer
30
When SELECT statement results are displayed on the screen, each column of output is limited to the width of the screen. The TRUNCATION_LENGTH option is used to reduce the width of wide columns so that more than one column will fit on the screen. A value of 0 means that columns are not truncated.
The default TRUNCATION_LENGTH is 30. For character-mode systems, this is an actual number of characters. For windowing systems, TRUNCATION_LENGTH is used to estimate an area of the screen to be used for display since proportional fonts are used.
TSQL_HEX_CONSTANT optionControls whether hexadecimal constants are treated as binary typed constants.
ON, OFF
ON
When this option is set to ON, hexadecimal constants are treated as binary typed constants. To get the historical behavior, set the option to OFF.
TSQL_VARIABLES optionControls whether the @ sign can be used as a prefix for Embedded SQL host variable names.
ON, OFF
OFF
ON for Open Client and JDBC connections
When this options set to ON, you can use the @ sign instead of the colon as a prefix for host variable names in Embedded SQL. This is implemented primarily for Transact-SQL compatibility.
VERIFY_ALL_COLUMNS optionControls whether messages that contain updates published by the local database are sent with all column values included.
ON, OFF
OFF
This option is used by SQL Remote only. When it is set to ON, messages that contain updates published by the local database are sent with all column values included, and a conflict in any column triggers a RESOLVE UPDATE trigger at the subscriber database.
VERIFY_THRESHOLD optionControls which columns are verified when updates are replicated.
Integer, in bytes
1000
This option is used by SQL Remote only. If the data type of a column is longer than the threshold, old values for the column are not verified when an UPDATE is replicated. This keeps the size of SQL Remote messages down, but has the disadvantage that conflicting updates of long values are not detected.
WAIT_FOR_COMMIT optionDetermines when foreign key integrity is checked, as data is manipulated.
ON or OFF
Can be set for an individual connection or the PUBLIC group. Takes effect immediately.
OFF
If this option is set to ON, the database does not check foreign key integrity until the next COMMIT statement. Otherwise, all foreign keys that are not created with the CHECK ON COMMIT option are checked as they are inserted, updated or deleted.