SET OPTION (SQL)

The AUTO_PARALLEL_THRESHOLD option is set to an integer n that determines whether parallel processing should be applied to a query when automatic parallel processing is enabled. Because there are performance costs associated with parallel processing, a threshold needs to be established for when parallel processing is advantageous. The higher n is, the lower the chance that an InterSystems SQL query executes using parallel processing. The default is 3200. This is a system-wide setting. The value n corresponds roughly to the minimal number of tuples needed in the visited map for parallel processing to occur.

When AutoParallel is disabled, the AUTO_PARALLEL_THRESHOLD option has no effect.

This option can also be set using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method AutoParallelThreshold option.

For further details, refer to AutoParallelThreshold.

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.

This option can also be set using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method CompileMode options.

The DEFAULT_SCHEMA option sets the default schema system-wide for all namespaces. This default remains in effect until explicitly changed. The default schema name is used to supply a schema name for all unqualified table, view, or stored procedure names.

You can specify a literal schema name or specify _CURRENT_USER. If you specify _CURRENT_USER as the default schema name, InterSystems IRIS assigns the user name of the currently logged-in process as the default schema name. For further details, refer to Schema Name.

The EXACT_DISTINCT boolean option specifies whether DISTINCT processing (TRUE) or Fast Distinct processing (FALSE) should be used system-wide. The system-wide default is to use Fast Distinct processing.

When EXACT_DISTINCT=TRUE, GROUP BY and DISTINCT queries produce original values. When EXACT_DISTINCT=FALSE, Fast Distinct is enabled, causing SQL queries involving DISTINCT or GROUP BY clauses to run more efficiently by making better use of indexes (if indexes are available). However, the values returned by such queries are collated in the same way they are stored within the index. This means the results of such queries may be all uppercase. This may have an effect on case-sensitive applications.

This option can also be set using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method FastDistinct boolean option.

For further details, refer to FastDistinct.

The LOCK_ESCALATION_THRESHOLD option is set to an integer n that determines when to escalate row locking to table locking. The default is 1000. The value n is the number of inserts, updates, or deletes for a single table within a single transaction that will trigger a table-level lock when reached. This is a system-wide setting for all namespaces. For example, if the lock threshold is 1000 and a process starts a transaction and then inserts 2000 rows, after the 1001st row is inserted the process will attempt to acquire a table-level lock instead of continue to lock individual rows. This is to help keep the lock table from becoming too full.

This option can also be set using the $SYSTEM.SQL.Util.SetOption()Opens in a new tab method LockThreshold option.

For further details, see Modify Transaction Lock Threshold.

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 continues 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")Opens in a new tab 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()Opens in a new tab method configuration option DDLPKeyNotIDKey. To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab 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()Opens in a new tab method DelimitedIdentifiers option.

To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.

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.

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.