Alphabetical List of Options

The amount of time before SAP IQ removes a shut down node from the affinity map and reassigns its partitions to other nodes.

Controls the choice of algorithms for processing an aggregate.

Controls whether new columns created without specifying either NULL or NOT NULL are allowed to contain NULL values.

Controls what values you can set the SNAPSHOT_VERSIONING option to. Applies to RLV-enabled tables only.

Controls whether cursors that were opened WITH HOLD are closed when a ROLLBACK is performed.

Controls permissions checking for DELETE and UPDATE statements.

Controls the interpretation of using = and != with NULL.

Controls the behavior of the SUBSTRING (SUBSTR) function when negative values are provided for the start or length parameters.

Controls the range of updates that are permitted.

Enables client-side data transfer.

Specifies that the display of SAP IQ binary columns is consistent with the display of SAP ASE binary columns.

Specifies that output of SAP IQ functions, including INTTOHEX and HEXTOINT, is consistent with the output of SAP ASE functions.

Enables and disables auditing in the database.

Registers newly created tables in the RLV store, enabling row-level versioning. RLV-enabled tables are eligible for multiple writer concurrent access. You can override this setting at the table level using the CREATE_TABLE statement.

Maximum percentage of a user’s temp memory that a persistent bit-vector object can pin.

Controls the behavior in response to locking conflicts. BLOCKING is not supported on secondary nodes of a multiplex.

Controls the length of time a transaction waits to obtain a lock. BLOCKING_TIMEOUT is not supported on secondary nodes of a multiplex.

Controls the way SAP IQ determines whether to continue prefetching B-tree pages for a given query.

Restricts the size of the read-ahead buffer for the High_Group B-tree.

Determines per-page fill factor during page splits for B-tree structures.

The maximum percentage of main buffer cache to use for affinity. Non-affinity data can use this area if insufficient affinity data exists.

Sets the number of partitions to be used for the main and temporary buffer caches.

Controls transaction mode in the absence of a BEGIN TRANSACTION statement.

Set the maximum length of time, in minutes, that the database server runs without doing a checkpoint.

Sets the number of rows that are returned from remote servers for each fetch.

Controls closing of cursors at the end of a transaction.

Controls behavior following a RAISERROR statement.

Controls reporting of data type conversion failures on fetching information from the database.

Restricts implicit conversion between binary data types (BINARY, VARBINARY, and LONG BINARY) and other non-binary data types (BIT, TINYINT, SMALLINT, INT, UNSIGNED INT, BIGINT, UNSIGNED BIGINT, CHAR, VARCHAR, and LONG VARCHAR) on various operations. Also allows all explicit conversions to be permitted as implicit conversions on various operations.

Converts pre-version 12.4.2 VARCHAR data to compressed format.

Governs when a COMMIT entry in the transaction log is written to disk.

Controls when commits are written to disk.

Determines whether the database engine creates tiered HG or single-tiered HG indexes.

Governs 16.0 tiered HG index delete behavior.

Defines the number of cursor rows to buffer.

Determines the first day of the week.

Sets the format used for dates retrieved from the database.

Controls the interpretation of date formats.

Reports the progress of the sp_iqcheckdb system stored procedure.

Controls the percent of the cache used by the sp_iqcheckdb system stored procedure.

Controls whether or not MESSAGE statements that include a DEBUG ONLY clause are executed.

Dedicates a request handling task to handling requests from a single connection.

Changes the default dbspace where tables are created.

Sets the default disk striping value for all dbspaces.

Provides default selectivity estimates to the optimizer for most HAVING clauses in parts per million.

Specifies the code page used by READ and OUTPUT statements.

Sets an upper threshold in KB on the amount to write to a stripe before write operations move on to the next stripe.

Provides default selectivity estimates (in parts per million) to the optimizer for most LIKE predicates.

Provides default selectivity estimates (in parts per million) to the optimizer for leading constant LIKE predicates.

Enables you to override the default estimate of the number of rows to return from a proxy table.

Enables you to override the default estimate of the number of rows to return from a table UDF (either a C, C++, or Java table UDF).

Determines when the server returns control to an application following a COMMIT.

Determines when the server returns control to an application following a COMMIT.

Allows load, insert, update, or delete operations to bypass the referential integrity check, improving performance.

Controls the reporting of division by zero.

Temporary database option DQP_ENABLED allows you to enable or disable distributed query processing at the connection level.

Temporary database option DQP_ENABLED_OVER_NETWORK allows you to enable or disable distributed query processing over the network at the connection level.

Controls whether simple local predicates are executed before query optimization.

Allows a DBA to enable or disable the asynchronous IO used by the RLV persistence log.

Controls the data type conversion of large object variables.

Controls whether queries with an ambiguous syntax for multi-table joins are allowed or are reported as an error.

Controls which accumulator to use for SUM or AVG of floating-point numbers.

Causes SAP IQ to leak, rather than reclaim, database disk space during a DROP command.

Forces all cursors to be non-scrolling.

Controls whether cursors that have not been declared as updatable can be updated.

Specifies the number of lookup pages and cache memory allocated for Lookup FP indexes in SAP IQ 15. databases.

Controls the amount of main buffer cache allocated to FP indexes in SAP IQ 15 databases.

Limits the number of distinct values in columns that implicitly load as NBit FP.

Enforces sizing limits for explicit and implicit NBit columns.

Provides support for tokenized FP indexes similar to that available in SAP IQ 15.

Limits the total dictionary size for implicit NBit FP columns.

Sets a threshold for the total dictionary size for implicit NBit rollovers to Flat FP.

Specifies degree of parallelism used in the default index.

Controls the use of memory for the optimization of queries involving functional expressions against columns having enumerated storage.

Specifies the percent of space on each HG garray page to reserve for future incremental inserts into existing groups.

Specifies number of pages used for prefetch.

Determines per-page fill factor during page splits on the garray and specifies the percent of space on each HG garray page to reserve for future incremental inserts.

Specifies number of pages used for prefetch.

Controls the maximum percentage of a user’s temp memory that a hash object can pin.

Specifies the percent of hard disk I/Os allowed during the execution of a statement that includes a query involving hash algorithms, before the statement is rolled back and an error message is reported.

Specifies the algorithm used during a delete in a HG index.

Specifies the maximum number of Btree pages used in evaluating a range predicate in the HG index.

Specifies the amount of time, in minutes, that the client waits for an HTTP session to time out before giving up.

Creates a unique HG index on each IDENTITY/AUTOINCREMENT column, if the column is not already a primary key.

Enables users to insert values into or to update an IDENTITY or AUTOINCREMENT column.

Controls the choice of algorithms for processing an IN subquery.

Generates messages suggesting additional column indexes that may improve performance of one or more queries.

Sets the maximum number of unique advice messages stored by the index advisor to max_rows.

Controls the choice of indexes to use for queries.

Controls the optimizer’s inference of additional subquery predicates.

Sets the maximum size of the point-in-time recovery archive in megabytes.

Enables point-in-time recovery logging.

Sets a time interval between automatic backups of the point in time recovery logs.

Limits the allowed IQGOVERN_PRIORITY setting.

Assigns a priority to each query waiting in the -iqgovern queue.

Limits the time a high priority query waits in the queue before starting.

Controls the locking isolation level for catalog store tables.

Specifies the path of the Java VM for the database.

Specifies command line options that the database server uses when it launches the Java VM.

Controls how conservative the optimizer’s join result estimates are in unusually complex situations.

Enables or disables the optimization of the join order.

Controls the choice of algorithms when processing joins.

Controls the minimum number of tables being joined together before any join optimizer simplifications are applied.

Specifies the amount of memory to use for a load into a LF index.

Specifies LOAD statement behavior under certain conditions.

Controls logging of user connections.

Controls logging of cursor operations.

Controls whether deadlock reporting is turned on or off.

Controls the use of standard, integrated, Kerberos, LDAP, and PAM logins for the database.

Specifies a login procedure that sets connection compatibility options at start-up.

Controls the amount of space SAP IQ reserves in the IQ main store.

Limits the number of rows resulting from a Cartesian join.

Controls the maximum precision for numeric data sent to the client.

Controls the maximum scale for numeric data sent to the client.

Sets the maximum number of rows that the IQ optimizer considers for a GROUP BY CUBE operation.

Specifies a resource governor to limit the maximum number of cursors that a connection can use at once.

Sets the maximum number of rows that the IQ optimizer considers for a hash algorithm.

Controls the number of threads for each connection.

Controls the number of threads allocated to perform a single operation (such as a LIKE predicate on a column) executing within a connection.

Controls the maximum number of tables to be optimized for join order after optimizer simplifications have been applied.

Sets an upper bound, expressed in megabytes, on the amount of temporary cache space that the optimizer can assume will be available for hash-partitioned hash-based query operators.

Specifies the number of prefix terms allowed in a text search expression.

Sets upper bound for parallel execution of GROUP BY operations and for arms of a UNION.

Sets a time limit so that the optimizer can disallow very long queries.

Limit the volume of RLV-enabled table data allowed to be transferred from the RLV store when a statement is executed on another node.

Specifies a resource governor to limit the maximum number of prepared statements that a connection can use at once.

Limits temporary store space used per connection.

Minimizes use of disk space for newly created columns in SAP IQ 15 databases.

Sets the minimum length for new passwords in the database.

Configures of the minimum number of required administrators for all roles.

Controls placement of output files for the IQ buffer cache monitor.

Timeout for autoexcluding a secondary node on the coordinator node. This option does not apply to the designated failover node.

Interval until the heartbeat thread wakes and performs periodic operations, such as checking for coordinator connectivity and cleaning up the connection pool on the secondary node. The heartbeat thread maintains a dedicated internal connection from secondary server to coordinator.

Time after which an unused connection in the connection pool on a secondary node will be closed.

Time, in seconds, before a heartbeat on a secondary server declares the coordinator offline if the heartbeat fails to reconnect to the coordinator after the first disconnect. This option also determines how long the coordinator keeps a global transaction in a suspended state.

Maximum number of connections allowed in the connection pool on a secondary node.

Maximum number of unused connections in the connection pool on a secondary node.

Time, in seconds, before a multiplex DQP leader reassigns incomplete distributed work to another DQP worker node.

Controls the interpretation of 2-digit years, in string to date conversions.

Generates the optimizer query plans instead of executing the plan.

Controls whether zero-length VARCHAR data is treated as NULLs for insert, load, and update operations.

Turns off individual keywords, allowing their use as identifiers.

Controls the default frequency of notify messages issued by certain commands.

Controls how the SAP IQ ODBC driver describes CHAR columns.

Controls the action taken, if an error is encountered during character conversion.

Controls the action taken if an error is encountered while executing statements in Interactive SQL.

Controls error handling in stored procedures.

Controls use of file system buffering for IQ Main dbspaces.

Controls the use of file system buffering for IQ Temporary dbspaces.

Specifies a login procedure whose result set contains messages that are displayed by the client application immediately after a user successfully logs in.

Specifies the maximum number of digits in the result of any decimal arithmetic, for queries on the catalog store only.

Allows you to turn fetching on or off or to use the ALWAYS value to prefetch the cursor results, even for SENSITIVE cursor types and for cursors that involve a proxy table.

Specifies the amount of memory used for prefetching.

Specifies the percent of memory used for prefetching.

Specifies the percent of prefetch resources designated for inserts to HG indexes.

Specifies the percent of prefetch resources designated for sorting objects.

Controls whether the original source definition of procedures, views, and event handlers is saved in system files. If saved, the formatted source is saved in the column source in SYSTABLE, SYSPROCEDURE, and SYSEVENT.

Controls whether progress messages are sent from the database server to the client..

Specifies whether or not to include additional query information in the Query Detail section of the query plan.

Gives a name to an executed query in its query plan.

Specifies whether or not additional query plans are printed to the SAP IQ message file.

Prints the entire query plan after query execution is complete.

Generates graphical query plans in HTML format for viewing in a Web browser.

Specifies the directory into which SAP IQ writes the HTML query plans.

Specifies a threshold for query execution. The post-query plan is generated only if query execution time exceeds the threshold.

Enables or prevents users from accessing query plans from the Interactive SQL client or from using SQL functions to get plans.

Allows you to specify whether or not SAP IQ generates and caches IQ plans for queries executed by the user.

Sets the row threshold for rejecting queries based on estimated size of result set.

Specifies the maximum estimated amount of temp space before a query is rejected.

Determines whether or not to collect specific timing statistics and display them in the query plan.

Controls the interpretation of strings that are enclosed in double quotes.

Sets the maximum length of time, in minutes, that the database server takes to recover from system failure.

Turns on individual keywords that are disabled by default.

Controls how a date, time, or timestamp value is passed to the client application when queried.

Setting this option ON forces the query optimizer to mimic SAP IQ  15.x behavior.

Controls behavior of the SQL function ROUND when queriying SAP IQ tables.

Limits the number of rows returned from a query.

This option configures the evaluation period used to determine when an automated merge of the row-level versioned (RLV) and IQ main stores should occur.

This value maximizes utilization of the number of cores on the machine.

Sets the percentage of total RLV memory size as a merge threshold for the node.

Sets the percentage of memory used as a merge threshold for an RLV-enabled table. If the memory used surpasses the threshold, a merge will occur..

Sets the number of rows used as a merge threshold for an RLV-enabled table. If the number of rows used surpasses the threshold, a merge will occur.

A portion of the RLV store must be reserved for memory used by data structures during critical operations.

Specifies the minimum number of digits after the decimal point when an arithmetic result is truncated to the maximum PRECISION, for queries on the catalog store only.

Controls whether RLV-enabled tables are accessed using single-writer table-level versioning, or multiple writer row-level versioning. Applies to RLV-enabled tables only.

Specifies the number of significant digits to the right of the decimal in exponential notation that are used in equality tests between two complex arithmetic expressions.

Allows implicit use of the SORTKEY function on ORDER BY expressions.

Specifies the maximum percentage of currently available buffers a sort object tries to pin.

Controls the behavior in response to any SQL code that is not part of the specified standard.

Controls the response to any SQL that is not part of the specified standard.

Determines whether an error is raised when an INSERT or UPDATE truncates a CHAR or VARCHAR string.

Controls which algorithm to use for processing correlated subquery predicates.

Allows the user to change the threshold at which the optimizer decides to transform scalar subqueries into joins.

Allows a user to override the decisions of the optimizer when transforming (flattening) scalar or EXISTS subqueries into joins.

Controls the placement of correlated subquery predicate operators within a query plan.

Determines whether TDS debugging information appears in the server window.

Specifies the percentage of SAP IQ threads used to sweep out buffer caches.

Controls whether empty strings are returned as NULL or a string containing one blank character for TDS connections.

Specifies that any rows extracted by the data extraction facility are added to the end of an output file.

In combination with the TEMP_EXTRACT_SWAP option, specifies the type of extraction performed by the data extraction facility.

Specifies the delimiter between columns in the output of the data extraction facility for an ASCII extraction.

Controls whether a user is allowed to use the data extraction facility. Also controls the directory into which temp extract files are placed and overrides a directory path specified in the TEMP_EXTRACT_NAMEn options.

Specifies whether all quotes in fields containing quotes are escaped in the output of the data extraction facility for an ASCII extraction.

Specifies the names of the output files or named pipes used by the data extraction facility. There are eight options: TEMP_EXTRACT_NAME1 through TEMP_EXTRACT_NAME8.

Controls the representation of null values in the output of the data extraction facility for an ASCII extraction.

Controls the representation of null values in the output of the data extraction facility for an ASCII extraction.

Specifies the string to be used as the quote to enclose fields in the output of the data extraction facility for an ASCII extraction, when either the TEMP_EXTRACT_QUOTES option or the TEMP_EXTRACT_QUOTES_ALL option is set ON.

Specifies that string fields are enclosed in quotes in the output of the data extraction facility for an ASCII extraction.

Specifies that all fields are enclosed in quotes in the output of the data extraction facility for an ASCII extraction.

Specifies the delimiter between rows in the output of the data extraction facility for an ASCII extraction.

Specifies the maximum sizes of the corresponding output files used by the data extraction facility.

In combination with the TEMP_EXTRACT_BINARY option, specifies the type of extraction performed by the data extraction facility.

Controls the amount of space SAP IQ reserves in the temporary IQ store.

Checks for catalog store temporary space on a per connection basis.

Specifies the algorithm used during a delete in a TEXT index.

Sets the format used for times retrieved from the database.

Sets the format used for timestamps retrieved from the database.

Sets the result size threshold for TOP N algorithm selection.

Allows automatic trimming of partial multibyte character data.

Specifies the trust relationship for outbound Transport Layer Security (TLS) connections made by LDAP User Authentication, INC, and MIPC connections.

Controls whether the @ sign can be used as a prefix for Embedded SQL host variable names.

Adjusts memory use for the number of current users.

Specifies a user-supplied authentication function that can be used to implement password rules.

Specifies the percentage of the buffer caches above the wash marker.

Determines when foreign key integrity is checked as data is manipulated.

Specifies the algorithm used during a delete in a WD index.