SQL Settings Reference
InterSystems SQL enables you to control various settings to set up your SQL environment. These settings can be controlled through various interfaces, including the Configuration Parameter File, the Management Portal, the SET OPTION command, and methods in the %SYSTEM.SQL.UtilOpens in a new tab class. However, not all options are available through all interfaces and some have different names across the multiple interfaces they might be available through. This page acts as a reference for how you can change each of the settings.
How to Change Each SQL Setting
Since there are many different SQL settings and multiple mechanisms to change them, the table below provides a summary of how you may change each one.
To find the SQL settings page in the Management Portal, navigate to System Administration > Configuration > SQL and Object Settings > SQL.
| Option Name / Management Portal Name (if applicable) | CPF Name | API | SET OPTION Name |
|---|---|---|---|
| AdaptiveMode / Turn off Adaptive Mode to disable run time plan choice and automatic tuning | AdaptiveMode | SetOption()Opens in a new tab | n/a |
| AllowExtrinsicFunction | ExtrinsicFunctions | SetOption()Opens in a new tab | n/a |
| AllowRowIDUpdate | AllowRowIDUpdate | n/a | n/a |
| ANSIPrecedence | ANSIPrecedence | SetOption()Opens in a new tab | n/a |
| AutoCommit | n/a | SetOption()Opens in a new tab | n/a |
| AutoParallel / Execute queries in a single process | AutoParallel | SetOption()Opens in a new tab | n/a |
| AutoParallelThreshold | AutoParallelThreshold | SetOption()Opens in a new tab | AUTO_PARALLEL_ THRESHOLD |
| BiasQueriesAsOutlier | BiasQueriesAsOutlier | n/a | n/a |
| BitmapFriendlyCheck | n/a | SetOption()Opens in a new tab | n/a |
| CachedQueryLockTimeout | n/a | SetOption()Opens in a new tab | n/a |
| CachedQuerySaveSource / Retain cached query source | SaveMAC | SetOption()Opens in a new tab | n/a |
| ClientMaxIdleTime / Client maximum idle time (seconds) | ClientMaxIdleTime | SetOption()Opens in a new tab | n/a |
| CollectionProjection | n/a | SetOption()Opens in a new tab | n/a |
| CompileModeDeferred | n/a | SetOption()Opens in a new tab | COMPILEMODE |
| CompileModeImmediate | n/a | SetOption()Opens in a new tab | COMPILEMODE |
| CompileModeInstall | n/a | SetOption()Opens in a new tab | COMPILEMODE |
| CompileModeNocheck | n/a | SetOption()Opens in a new tab | COMPILEMODE |
| DDLDefineBitmapExtent | DDLDefineBitmapExtent | SetOption()Opens in a new tab | n/a |
| DDLDropTabDelData | DropDelete | SetOption()Opens in a new tab | n/a |
| DDLFinal | DDLFinal | SetOption()Opens in a new tab | n/a |
| DDLNo201 / Ignore redundant DDL | DDLNo201 | n/a | n/a |
| DDLNo30 / Ignore redundant DDL | DDLNo30 | n/a | n/a |
| DDLNo307 / Ignore redundant DDL | DDLNo307 | n/a | n/a |
| DDLNo311 / Ignore redundant DDL | DDLNo311 | n/a | n/a |
| DDLNo315 / Ignore redundant DDL | DDLNo315 | n/a | n/a |
| DDLNo324 / Ignore redundant DDL | DDLNo324 | n/a | n/a |
| DDLNo333 / Ignore redundant DDL | DDLNo333 | n/a | n/a |
| DDLPKeyNotIDKey / Define primary key as ID key for tables created via DDL | IDKey | SetOption()Opens in a new tab | PKEY_IS_IDKEY |
| DDLSQLOnlyCompile | DDLSQLOnlyCompile | n/a | n/a |
| DDLUseExtentSet | DDLUseExtentSet | SetOption()Opens in a new tab | n/a |
| DDLUseSequence | DDLUseSequence | SetOption()Opens in a new tab | n/a |
| DefaultSchema / Default schema | DefaultSchema | n/a | DEFAULT_SCHEMA |
| DefaultTimePrecision | TimePrecision | SetOption()Opens in a new tab | n/a |
| DelimitedIdentifiers | DelimitedIds | SetOption()Opens in a new tab | SUPPORT_DELIMITED_ IDENTIFIERS |
| ECPSync | ECPSync | SetOption()Opens in a new tab | n/a |
| FastDistinct / GROUP BY and DISTINCT queries must produce original values | FastDistinct | SetOption()Opens in a new tab | EXACT_DISTINCT |
| FilterRefIntegrity | ReferentialChecks | SetOption()Opens in a new tab | n/a |
| IdentityInsert | n/a | SetOption()Opens in a new tab | n/a |
| IdTrxFrom | IdTrxFrom | SetDDLIdentifier Translations()Opens in a new tab | n/a |
| IdTrxTo | IdTrxTo | SetDDLIdentifier Translations()Opens in a new tab | n/a |
| IsolationMode | n/a | SetOption()Opens in a new tab | n/a |
| LockThreshold / Lock escalation threshold | LockThreshold | SetOption()Opens in a new tab | LOCK_ESCALATION_ THRESHOLD |
| LockTimeout / Lock timeout (seconds) | LockTimeout | SetOption()Opens in a new tab | LOCK_TIMEOUT |
| ODBCVarcharMaxlen / Default length for VARCHAR | ODBCVarcharMaxlen | n/a | n/a |
| ProcessLockTimeout | n/a | SetOption()Opens in a new tab | n/a |
| ParameterSampling / Turn on parameter sampling to sample the parameter value for query execution | ParameterSampling | SetOption()Opens in a new tab | n/a |
| QueryProcedures | QueryProcedures | SetOption()Opens in a new tab | n/a |
| RetainSQL | Comment | SetOption()Opens in a new tab | n/a |
| RTPC | RTPC | SetOption()Opens in a new tab | n/a |
| SQLFunctionArgConversion | n/a | SetOption()Opens in a new tab | n/a |
| SQLSecurity | DBMSSecurity | SetOption()Opens in a new tab | n/a |
| SelectMode | n/a | SetOption()Opens in a new tab | n/a |
| ServerDisconnectCode | n/a | SetOption()Opens in a new tab | n/a |
| ServerInitCode | n/a | SetOption()Opens in a new tab | n/a |
| TCPKeepAlive / TCP keepalive for client connections (seconds) | TCPKeepAlive | SetOption()Opens in a new tab | n/a |
| ToDateDefaultFormat / TO_DATE default format | TODATEDefaultFormat | SetOption()Opens in a new tab | n/a |
Settings that Require Permissions
In order to change the following settings, a user must have the %Admin:USE:
| AdaptiveMode | ExtrinsicFunctions | ANSIPrecedence | AutoParallel |
| SaveMAC | DropDelete | DDLFinal | IDKey |
| DDLUseExtentSet | DDLUseSquence | TimePrecision | DelimitedIds |
| ECPSync | FastDistinct | ReferentialChecks | IdTrxFrom |
| IdTrxTo | LockThreshold | LockTimeout | ParameterSampling |
| QueryProcedures | Comment | RTPC | DBMSSecurity |
| TCPKeepAlive | TODATEDefaultFormat |
