SET OPTION option_keyword = value
The SET OPTION statement is used to set execution options, such as the compile mode, SQL configuration settings, and the locale settings governing date, time, and numeric conventions. Only one keyword option can be set by each SET OPTION statement.
Other SET OPTION arguments (not documented here) are parsed by InterSystems IRIS for SQL compatibility, but perform no operation.
Because SET OPTION prepares and executes quickly, and is generally run only once, InterSystems IRIS does not create a cached query for SET OPTION in ODBC, JDBC, or Dynamic SQL.
The following options are supported by InterSystems IRIS:
The COMPILEMODE option sets the compile mode to DEFERRED, IMMEDIATE, INSTALL, or NOCHECK for the current namespace. The default is IMMEDIATE. Changing from DEFERRED to IMMEDIATE compile mode causes any classes in the Deferred Compile Queue to be compiled immediately. If all class compilations are successful, InterSystems IRIS sets SQLCODE to 0. If there are any errors, SQLCODE is set to -400. Class compilation errors are logged in the ^mtemp2 ("Deferred Compile Mode","Error"). If SQLCODE is set to -400, you should view this global structure for more precise error messages. The INSTALL compile mode is similar to the DEFERRED compile mode, but it should only be used for DDL installations where there is no data in the tables.
The NOCHECK compile mode is similar to IMMEDIATE, except that it skips checking of the following constraints when compiling: If a table is dropped, InterSystems IRIS does not check foreign key constraints in other tables that reference the dropped table. If a foreign key constraint is added, InterSystems IRIS does not check existing data to ensure that it is valid for this foreign key. If a NOT NULL constraint is added, InterSystems IRIS does not check existing data for NULLs or assign the field’s default value. If a UNIQUE or Primary Key constraint is deleted, InterSystems IRIS does not check if a foreign key in this table or another table references the dropped key.
The LOCK_TIMEOUT numeric option lets you set the default lock timeout for the current process. The LOCK_TIMEOUT value is the number of seconds to wait when trying to establish a lock during SQL execution. This lock timeout is used when a locking conflict prevents the current process from immediately locking a record, table, or other entity for a LOCK, INSERT, UPDATE, DELETE, or SELECT operation. InterSystems SQL continue to try to establish the lock until the timeout expires, at which point an SQLCODE -110 or -114 error is generated.
Available values are positive integers and zero. The timeout setting is per process. You can determine the lock timeout setting for the current process using the $SYSTEM.SQL.Util.GetOption("ProcessLockTimeout") method.
If you do not set the lock timeout for the current process, it defaults to the current system-wide lock timeout setting. If your ODBC connection disconnects and reconnects, the reconnected process uses the current system-wide lock timeout setting. The default system-wide lock timeout is 10 seconds.
For further details on locking conflicts and per-process and system-wide SQL lock timeout settings, refer to the LOCK command.
The PKEY_IS_IDKEY boolean option specifies whether primary keys are also ID keys system-wide. Available values are TRUE and FALSE. If TRUE, and the field does not contain data, the primary key is created as an ID key. That is, the primary key of the table also becomes the IDKey index in the class definition. If the field does contain data, the IDKey index is not defined. If the primary key is defined as the IDKey index, data access is more efficient, but a primary key value, once set, can never be modified. Once set, you cannot change the value assigned to a primary key, nor can you assign a different key as the primary key. Use of this option also changes the primary key collation default; primary key string values default to EXACT collation. If FALSE, the primary key and ID key are defined as independent, which is less efficient. However, primary key values are modifiable, and primary key string values default to the current collation type default, which is SQLUPPER by default.
To set the PKEY_IS_IDKEY option, you must have the %Admin_Manage:USE privilege. Otherwise, you receive an SQLCODE -99 error (Privilege Violation). Once set, this option takes effect system-wide for all processes. The system-wide default for this option can also be set using:
The system-wide $SYSTEM.SQL.Util.SetOption() method configuration option DDLPKeyNotIDKey. To determine the current setting, call $SYSTEM.SQL.CurrentSettings() which displays Are primary keys created through DDL not ID keys; the default is 1.
A Management Portal configuration setting. Select System Administration, Configuration, SQL and Object Settings, SQL. View or modify the current setting of Define primary key as ID key for tables created via DDL.
The PKEY_IS_IDKEY setting remains in effect until reset through another SET OPTION PKEY_IS_IDKEY or until the InterSystems IRIS Configuration is reactivated, which resets this parameter to the InterSystems IRIS System Configuration setting.
By default, delimited identifiers are supported system-wide. The SUPPORT_DELIMITED_IDENTIFIERS boolean option allows you to change support for delimited identifiers system-wide. Available values are TRUE and FALSE. If TRUE, a string delimited by double quotation marks is considered an identifier within an SQL statement. If FALSE, a string delimited by double quotation marks is considered a string literal within an SQL statement.
To set the SUPPORT_DELIMITED_IDENTIFIERS option, you must have the %Admin_Manage:USE privilege. Otherwise, you receive an SQLCODE -99 error (Privilege Violation). Once set, this option takes effect system-wide for all processes. The SUPPORT_DELIMITED_IDENTIFIERS setting remains in effect until reset through another SET OPTION SUPPORT_DELIMITED_IDENTIFIERS, or until changed system-wide by the $SYSTEM.SQL.Util.SetOption() method DelimitedIdentifiers option.
To determine the current setting, call $SYSTEM.SQL.CurrentSettings().
Locale options are keyword options used to set your InterSystems IRIS Locale settings for date, time, and numeric conventions for the current process. The available keyword options are AM, DATE_FORMAT, DATE_MAXIMUM, DATE_MINIMUM, DATE_SEPARATOR, DECIMAL_SEPARATOR, MIDNIGHT, MINUS_SIGN, MONTH_ABBR, MONTH_NAME, NOON, NUMERIC_GROUP_SEPARATOR, NUMERIC_GROUP_SIZE, PM, PLUS_SIGN, TIME_FORMAT, TIME_PRECISION, TIME_SEPARATOR, WEEKDAY_ABBR, WEEKDAY_NAME, and YEAR_OPTION. All of these options can be set to a literal, and all take a default (American English conventions). The TIME_PRECISION option is configurable (see below). If you set any of these options to an invalid value, InterSystems IRIS issues an SQLCODE -129 error (Illegal value for SET OPTION locale property). See the ObjectScript $ZDATETIME function for an explanation of date and time formats and options.
|Date/Time Option Keyword||Description|
|AM||String. Default is 'AM'|
|DATE_FORMAT||Integer. Default is 1. Available values are 0 through 15. For an explanation of these date formats, see the ObjectScript $ZDATE function.|
|DATE_MAXIMUM||Integer. Default is 2980013 (12/31/9999). Can be set to an earlier date, but not to a later date.|
|DATE_MINIMUM||Positive Integer. Default is 0 (12/31/1840). Can be set to a later date, but not to an earlier date.|
|DATE_SEPARATOR||Character. Default is '/'|
|DECIMAL_SEPARATOR||Character. Default is '.'|
|MIDNIGHT||String. Default is 'MIDNIGHT'|
|MINUS_SIGN||Character. Default is '-'|
|MONTH_ABBR||String. Default is ' Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec'. (Note that this string begins with a space character, which is the default separator character.)|
|MONTH_NAME||String. Default is ' January February March April May June ... November December'. (Note that this string begins with a space character, which is the default separator character.)|
|NOON||String. Default is 'NOON'|
|NUMERIC_GROUP_SEPARATOR||Character. Default is ','|
|NUMERIC_GROUP_SIZE||Integer. Default is 3.|
|PM||String. Default is 'PM'|
|PLUS_SIGN||Character. Default is '+'|
|TIME_FORMAT||Integer. Default is 1. Available values are 1 through 4. For an explanation of these time formats, see the ObjectScript $ZTIME function.|
|TIME_PRECISION||Integer from 0 through 9 (inclusive). Default is 0. The number of digits of fractional seconds. Configurable, as described below.|
|TIME_SEPARATOR||Character. Default is ':'|
|WEEKDAY_ABBR||String. Default is ' Sun Mon Tue Wed Thu Fri Sat'. (Note that this string begins with a space character, which is the default separator character.)|
|WEEKDAY_NAME||String. Default is ' Sunday Monday Tuesday Wednesday Thursday Friday Saturday'. (Note that this string begins with a space character, which is the default separator character.)|
|YEAR_OPTION||Integer. Default is 0. Available values are 0 through 6. For an explanation of these ways of representing 2-digit and 4-digit years, see the ObjectScript $ZDATE function.|
To configure TIME_PRECISION system-wide, go to the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. View and edit the current setting of Default time precision for GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP. This specifies the number of digits of precision for fractional seconds. The default is 0. The range of allowed values is 0 through 9 digits of precision. The actual number of meaningful digits of fractional seconds is platform-dependent.
SQL and Object Settings Pages listed in System Administration Guide.
SQLCODE error messages listed in the InterSystems IRIS Error Reference