[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 Configuring the SQL Shell.
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.
