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
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

Importing SQL Code

SQL Users, Roles, and Privileges