Skip to main content

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    
FeedbackOpens in a new tab