Skip to main content
Previous sectionNext section

[SQL]

This topic describes the settings on the SQL page of the Management Portal (System Administration > Configuration > SQL and Object Settings > SQL). The bottom of this page contains a list of parameters found in the [SQL] section of the CPF.

The SQL page is divided into the SQL tab and the SQL Shell tab . The SQL tab allows you to configure various SQL settings, which correspond to CPF parameters as shown in the table below. The SQL Shell tab options are described in the Configuring the SQL Shell section of the “Using the SQL Shell Interface” chapter in Using InterSystems SQL.

SQL Tab
SQL Tab Setting Equivalent CPF Parameter
Retain cached query source SaveMAC
Default time precision for GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP TimePrecision
Lock escalation threshold LockThreshold
TO_DATE default format TODATEDefaultFormat
Default length for VARCHAR ODBCVarcharMaxlen
Default schema DefaultSchema
Execute queries in a single process AutoParallel
Define primary key as ID key for tables created via DDL IdKey
Ignore redundant DDL statements Sets the DDLNo* parameters.
GROUP BY and DISTINCT queries must produce original values FastDistinct
Optimize queries based on parameter values BiasQueriesAsOutlier and RTPC
Lock timeout (seconds) LockTimeout
TCP keepalive for client connections TCPKeepAlive
  • AllowRowIDUpdate – Allow user to update RowID values.
  • ANSIPrecedence – Specify operator precedence for SQL queries.
  • AutoParallel – Allow parallel processingn instance-wide.
  • AutoParallelThreshold – Set the threshold for parallel processing.
  • BiasQueriesAsOutlier – Set query optimization to biased toward outliers.
  • Comment – Retain embedded SQL statements as comments in source code.
  • DBMSSecurity – Enable SQL security.
  • DDLDefineBitmapExtent – Specify whether a table created by a DDL statement defines a bitmap extent index.
  • DDLFinal – Specify whether a class created by a DDL statement is final.
  • DDLNo201 – Suppress error upon CREATE of a previously existing table.
  • DDLNo30 – Suppress error upon DROP of a nonexistent table.
  • DDLNo307 – Suppress error upon CREATE of a primary key constraint when one exists.
  • DDLNo311 – Suppress error upon ADD a foreign key, when a key of that name already exists.
  • DDLNo315 – Suppress error upon DROP of a nonexistent constraint.
  • DDLNo324 – Suppress error upon CREATE of a previously existing index.
  • DDLNo333 – Suppress error upon DROP of a nonexistent index.
  • DDLSQLOnlyCompile – Enable an SQL-only compile.
  • DDLUseExtentSet – Allow hashed names for globals that store index data of tables created by a DDL statement.
  • DDLUseSequence – Specify the function a table created by a DDL statement uses for ID assignment.
  • DefaultSchema – Set the default SQL schema name.
  • DelimitedIds – Enable interpreting double-quoted strings as delimited identifiers.
  • DropDelete – Specify whether DROP TABLE deletes the table’s data in addition to the table.
  • ECPSync – Ensure that the server and client cache are in sync.
  • ExtrinsicFunctions – Enable extrinsic functions in SQL statements.
  • FastDistinct – Allow SQL DISTINCT optimization.
  • IdKey – Set primary key constraint behavior.
  • IdTrxFrom – Define the From list of characters for Identifier Translation.
  • IdTrxTo – Define the To list of characters for Identifier Translation.
  • JavaClassPath – Define additional .jar files to load when the JDBC service is started.
  • JavaHome – Specify the Java version to use with the JDBC Gateway.
  • JDBCGatewayJVMArgs – Specify optional JVM arguments to use when starting the JDBC Gateway.
  • JDBCGatewayLog – Set the name of the JDBC Gateway log file.
  • JDBCGatewayPort – Set the port number for the JDBC Gateway.
  • JDBCGatewayUsePassphrase – Enable the passphrase requirement for JDBC connections from InterSystems IRIS® data platform to external applications.
  • LockThreshold – Set the SQL lock threshold.
  • LockTimeout – Set the SQL lock timeout.
  • ODBCVarcharMaxlen – Set the MaxLen for ODBC fields of type VarChar.
  • QueryProcedures – Specify whether all class queries project as SQL Stored Procedures.
  • RTPC – Enable Runtime Plan Choice (RTPC) query optimization.
  • ReferentialChecks – Enable foreign key constraint validation.
  • SaveMAC – Save the source code for cached query routines.
  • TCPKeepAlive – Set the number of seconds between keep-alive messages.
  • TimePrecision – Set the default time precision for SQL scalar time functions.
  • TODATEDefaultFormat – Set the default date format for the SQL TO_DATE() function.