Database Option Changes

SAP Sybase IQ 16.0 includes database option changes.

Option	Description
AFFINITY_AUTOEXCLUDE_TIMEOUT	The amount of time before SAP Sybase IQ removes a shut down node from the affinity map and reassigns its partitions to other nodes. Allowed values are 0 to 10080 minutes (1 week). The default is 10 minutes.
AGGREGATION_PREFERENCE	The allowed value range is now -6 to 6. These values are new in SAP Sybase IQ 16.0: 4 – prefer aggregation with a distinct/grouping sort. 5 – prefer aggregation with a sort if grouping columns include all the partitioning keys of a hash partitioned table. 6 – prefer aggregation with a hash if grouping columns include all the partitioning keys of a hash partitioned table. -4 – avoid aggregation with a distinct/grouping sort. -5 – avoid aggregation with a sort if grouping columns include all the partitioning keys of a hash partitioned table. -6 – avoid aggregation with a hash if grouping columns include all the partitioning keys of a hash partitioned table.
ALLOW_SNAPSHOT_VERSIONING	New option. Applies to all base tables in the database (as opposed to RLV-enabled tables only). Restricts table versioning for all base tables to either table-level or row-level snapshot versioning. This option does not apply to the IQ catalog store. The default is YES.
APPEND_LOAD	The APPEND_LOAD option is deprecated. This option still exists but the setting will be ignored.
BASE_TABLES_IN_RLV	New option. Registers tables in the RLV store, enabling row-level versioning for IQ main store tables. RLV-enabled tables are eligible for multiple writer concurrent access. You can override this setting at the table level using the CREATE TABLE statement.
BLOCKING	New allowed value: ON. This setting causes any transaction attempting to obtain a lock that conflicts with an existing lock held by another transaction to wait until every conflicting lock is released, or until the blocking_timeout threshold is reached.
BLOCKING_TIMEOUT	New option. Controls how long (in milliseconds) a transaction waits to obtain a lock.
CACHE_AFFINITY_PERCENT	Allowed values are 0 -100. Maximum percentage of main cache to use for affinity. Non-affinity data can use this area if insufficient affinity data exists.
CREATE_HG_WITH_EXACT_DISTINCTS	Determines whether newly created HG indexes are tiered or non-tiered.This option is ON in all new 16.0 databases and all 16.0 databases migrated from 15.x.To take advantage of the new structure, set this option to OFF.
DQP_ENABLED	Set the temporary database option DQP_ENABLED OFF to disable DQP for the current connection. Set the option ON (the default value) to enable DQP for the current connection, but only when DQP is enabled by the policy option for the logical server of the current connection.
DQP_ENABLED_OVER_NETWORK	Set the temporary database option DQP_ENABLED_OVER_NETWORK to ON to enable DQP over the network for the current connection. The DQP_ENABLED database option must also be set to ON and the DQP_ENABLED logical server policy option must not be set to 1. If the DQP_ENABLED logical server policy option is set to 2, then this database option has no affect and all queries will use DQP over the network.
ENABLE_ASYNC_IO	New option. Allows a DBA to enable or disable the asynchronous IO used by the RLV persistence log for row-level versioning.
FLOATING_POINT_ACCUMULATOR	New option. Replaces LARGE_DOUBLES_ACCUMULATOR.
FP_LOOKUP_SIZE	Sets the number of lookup pages and reserves cache memory for FP indexes in SAP Sybase IQ databases where the FP_NBIT_IQ15_COMPATIBILITY option is ON.
FP_LOOKUP_SIZE_PPM	Controls the amount of main cache allocated to FP indexes in SAP Sybase IQ databases where the FP_NBIT_IQ15_COMPATIBILITY option is ON.
FP_NBIT_AUTOSIZE_LIMIT	New option. Limits the number of distinct values that an NBit column can load implicitly. Columns implicitly load as NBit up to the auto-size limits. Columns with an IQ UNIQUE n value set to 0 loads as Flat FP. Columns with an n value greater than 0 but less than the auto-size limit sets the NBit limit to n.
FP_NBIT_ENFORCE_LIMITS	New option. If this option is ON, and an NBit column exceeds explicit or implicit sizing limits, the operation throws an error and rolls back.
FP_NBIT_IQ15_ COMPATIBILITY	New option. Provides tokenized `FP` support similar to that available in SAP Sybase IQ 15. Defaults to OFF in a newly created 16.0 database. Set to ON in SAP Sybase IQ databases upgraded from 15.x.
FP_NBIT_LOOKUP_MB	New option. Limits the dictionary size for implicit NBit columns. Columns that exceed these limits rollover to Flat FP.
FP_NBIT_ROLLOVER_MAX_MB	New option. Sets the dictionary size (values and counts) for implicit NBit rollovers. If this option is ON, columns that exceed this limit rollover to Flat FP.
JOIN_PREFERENCE	The allowed value range is now -12 to 12. These values changed in SAP Sybase IQ 16.0: 6 – previously the action was prefer prejoin. The new action is prefer asymmetric sort merge join. -6 – previously the action was avoid prejoin. The new action is avoid asymmetric sort merge join. These values are new in SAP Sybase IQ 16.0: 8 – prefer asymmetric sort merge push down join. 9 – prefer partitioned hash join if the join keys include all the partition keys of a hash partitioned table. 10 – prefer partitioned hash-push down join if the join keys include all the partition keys of a hash partitioned table. 11 – prefer partitioned sort-merge join if the join keys include all the partition keys of a hash partitioned table. 12 – prefer partitioned sort-merge push-down join if the join keys include all the partition keys of a hash partitioned table. -8 – avoid asymmetric sort merge push down join. -9 – avoid partitioned hash join if the join keys include all the partition keys of a hash partitioned table. -10 – avoid partitioned hash-push down join if the join keys include all the partition keys of a hash partitioned table. -11 – avoid partitioned sort-merge join if the join keys include all the partition keys of a hash partitioned table. -12 – avoid partitioned sort-merge push-down join if the join keys include all the partition keys of a hash partitioned table.
JOIN_SIMPLIFICATION_THRESHOLD	New option - Controls the minimum number of tables being joined together before any join optimizer simplifications are applied.
LOG_DEADLOCKS	New option - Controls whether deadlock reporting is turned on or off.
LOGIN_MODE	New option. Add LDAPUA to the LOGIN_MODE option to use LDAP user authentication.
MAX_WARNINGS	Since SAP Sybase IQ no longer supports JOIN INDEXES, the MAX_WARNINGS option is no longer required.
MIN_ROLE_ADMINS	New option. Specifies the minimum number of role administrators for each role. Ensures that no role can be dropped if doing so reduces the remaining number of role administrators below a set value. Value range is 1 (default) – 10.
MINIMIZE_STORAGE	Behavior change. Minimizes disk space for columns in 16.0 databases where the FP_NBIT_IQ15_COMPATIBILITY option is ON. Ignored where the FP_NBIT_IQ15_COMPATIBILITY option is OFF.
NOTIFY_MODULUS	Behavior change – Default value for new SAP Sybase IQ 16.0 database changed from 100000 to 0. Default value remains unchanged for an upgraded pre-16.0 database.
QUERY_DETAIL	Default changed to ON.
QUERY_PLAN	Default changed to OFF.
QUERY_PLAN_AFTER_RUN	Default changed to ON.
QUERY_PLAN_MIN_TIME	New option. Specifies a threshold for query execution in microseconds. The query plan is generated only if query execution time exceeds the threshold. QUERY_PLAN must be ON.
QUERY_TIMING	Default changed to ON.
REVERT_TO_V15_OPTIMIZER	New option. Forces the query optimizer behaves as it did in 15.4. Forces the query optimizer to mimic 15.x behavior. This option is OFF in all newly created 16.0 databases. Set to ON in SAP Sybase IQ databases upgraded from 15.x. To take advatage of new DQP and algorithms and hash partitioning features, set this option to OFF in databases upgraded from 15.x.
ROUND_TO_EVEN	New option. When `ROUND_TO_EVEN` option is set to ON, the ROUND function rounds half to the nearest even number. When the option is set to OFF (the default), the ROUND function rounds half away from zero.
RV_AUTO_MERGE_EVAL_INTERVAL	New option. Configures the evaluation period used to determine when an automated background merge of the row-level versioned (RLV) and IQ main stores should occur. The default is 15 minutes.
RV_MERGE_NODE_MEMSIZE	New option. An automated merge of the row-level versioned (RLV) store and IQ main stores occurs based on the merge thresholds, including RV_MERGE_NODE_MEMSIZE. When this node threshold is exceeded, a merge will be triggered. The default is 75%.
RV_MERGE_TABLE_MEMPERCENT	New option. An automated merge of the row-level versioned (RLV) store and IQ main stores occurs based on the merge thresholds, including RV_MERGE_TABLE_MEMPERCENT. If this table threshold is exceeded, a merge will be triggered for the specific table. The default is 0%.
RV_MERGE_TABLE_NUMROWS	New option. An automated merge of the row-level versioned (RLV) store and IQ main stores occurs based on the merge thresholds, including RV_MERGE_TABLE_NUMROWS. If this table threshold is exceeded, a merge will be triggered for the specific table. The default is 10000000.
RV_RESERVED_DBSPACE_MB	New option. A portion of the RLV store must be reserved for memory used by data structures during critical operations. The default is The minimum of 50 Mb or half the size of the RLV dbspace.
SNAPSHOT_VERSIONING	New option. Applies to RLV-enabled tables only (as opposed to all base tables in the database). Controls whether RLV-enabled tables are accessed using single-writer table-level versioning, or multiple writer row-level versioning. This option does not apply to the IQ catalog store. The default is TLV.
TRUSTED_CERTIFICATE_FILE	New option. For TLS connections from SAP Sybase IQ to other servers. Specifies the file name containing the certificate of the certificate authorities (CA) that are trusted. The trusted CAs are the signers of the certificates used by: The external LDAP directory server for use with LDAP User Authentication The server certificate in use by the SAP Sybase IQ multiplex server for INC and MIPC connections.