Skip to main content

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

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
Turn off Adaptive Mode to disable run time plan choice and automatic tuning AdaptiveMode
Turn on parameter sampling to sample the parameter value for query execution ParameterSampling
Lock timeout (seconds) LockTimeout
TCP keepalive for client connections (seconds) TCPKeepAlive
Client maximum idle time (seconds) ClientMaxIdleTime
  • ANSIPrecedence – Specify operator precedence for SQL queries.
  • AdaptiveMode – Enable Adaptive Mode performance optimization options.
  • AllowRowIDUpdate – Allow user to update RowID values.
  • AutoParallel – Allow parallel processing instance-wide.
  • AutoParallelThreshold – Set the threshold for parallel processing.
  • BiasQueriesAsOutlier – Set query optimization to biased toward outliers.
  • ClientMaxIdleTime
  • 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.
  • LockThreshold – Set the SQL table-level lock threshold.
  • LockTimeout – Set the SQL lock timeout.
  • ODBCVarcharMaxlen – Set the MaxLen for ODBC fields of type VarChar.
  • ParameterSampling – Specify whether parameter sampling is enabled or not.
  • 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.
  • TODATEDefaultFormat – Set the default date format for the SQL TO_DATE() function.
  • TimePrecision – Set the default time precision for SQL scalar time functions.
FeedbackOpens in a new tab