Although the SAP® Sybase® IQ 16 New Features Summary describes all new SAP Sybase IQ functionality, some features may require additional action on your part to take advantage of the new architecture.
Customers upgrading from a previous release, for example, may need to change some initial compatibility options or rebuild wide columns to accommodate different datatypes. The new load engine provides better performance, but requires changes to the default memory allocation to use all available hardware resources efficiently.
Continuous NBit dictionary compression replaces 1, 2, 3 byte dictionary compression as the default column storage mechanism in 16.0. All datatypes except LOB (character and binary) and BIT datatypes can be NBit columns.
The IQ UNIQUE column constraint determines whether a column loads as Flat FP or NBit FP. An IQ UNIQUE n value set to 0 loads the column as Flat FP. An n value greater than 0 but less than the FP_NBIT_AUTOSIZE_LIMIT creates a NBit column initially sized to n. Columns without an IQ UNIQUE constraint implicitly load as NBit up to the auto-size limit.
Using IQ UNIQUE with an n value less than the auto-size limit is not necessary. The load engine automatically sizes all low or medium cardinality columns as NBit. Use IQ UNIQUE in cases where you want to load the column as Flat FP or when you want to load a column as NBit when the number of distinct values exceeds the auto-size limits.
Large memory represents the maximum amount of memory that SAP Sybase IQ can dynamically request from the OS for temporary use. Because some load operations may require more large memory than the 2GB default provides, adjust the startup options that control large and cache memory allocation based on the total amount of available physical memory.
As a general rule, large memory requirements represent one third of the total available physical memory allocated to SAP Sybase IQ. To ensure adequate memory for the main and temporary IQ stores, set the –iqlm, –iqtc, and –iqmc startup parameters so that each parameter receives one third of all available physical memory allocated to SAP Sybase IQ.
In most cases, you should allocate 80% of total physical memory to SAP Sybase IQ to prevent SAP Sybase IQ processes from being swapped out. Adjust actual memory allocation to accommodate other processes running on the same system. For example, on a machine with 32 cores and 128GB of total available physical memory, you would allocate 100GB (approximately 80% of the 128GB total) to SAP Sybase IQ processes. Following the general rule, you would set the –iqlm, –iqtc, and –iqmc parameters to 33GB each.
See -iqlm iqsrv16 Server Option and -iqmc iqsrv16 Server Option in the Utility Guide.
Changes to FP and HG indexes take advantage of the new column compression mechanism and improve load performance.
Index | Description |
---|---|
New Fast Projection (FP) Indexes | Take advantage of the new continuous NBit dictionary compression, which replaces
FP(1), FP(2), and FP(3) byte dictionary compression. FP(1), FP(2), and FP(3) indexes roll
over to NBit(8), NBit(16), and NBit(24) respectively. If FP_NBIT_IQ15_COMPATIBILITY='OFF', IQ UNIQUE constraints applied to the column determine whether the column loads as Flat FP or NBit. See Fast Projection ( FP ) Index in Administration: Database. |
New tiered HG index structure | Decouples load performance from HG
index size. In 15.x, load throughput could degrade as the amount
of data in an HG index
increased. As the index grew, loading the same amount of data
could take more time. The new tiered structure decouples load
performance from the HG index
size to increase throughput. The CREATE_HG_WITH_EXACT_DISTINCTS option 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. Use sp_iqrebuildindex to convert non-tired HG indexes to tiered HG and vice-versa. See CREATE_HG_WITH_EXACT_DISTINCTS Option in Reference: Statements and Options . |
New stored procedures return information about column indexes and constraints.
Some database options are not enabled to take advantage of 16.0 features. Maintaining limited compatibility after a database upgrade provides some flexibility to transition existing applications.
Option | Description |
---|---|
FP_NBIT_IQ15_COMPATIBILITY | Provides tokenized FP support
similar to that available in 15.x. This option is ON by default in all
16.0
databases upgraded from 15.x and OFF in all newly created 16.0 databases.
|
CREATE_HG_WITH_EXACT_DISTINCTS | Determines whether new HG indexes
explicitly created with a CREATE
INDEX command, or implicitly creating or altering
a table with a PRIMARY KEY or a FOREIGN KEY declaration, are
tiered or non-tiered. This option is ON 16.0 in all databases
upgraded from 15.x and all newly created 16.0 databases. If
this option is ON, all new HG
indexes are non-tiered. To take advantage of the new tiered
HG index structure, set
this option to OFF. Use sp_iqrebuildindex to convert non-tiered HG indexes to tiered HG and vice-versa. |
CREATE_HG_AND_FORCE_PHYSICAL_DELETE | Governs 16.0
delete behavior for tiered HG indexes. This option determines
whether SAP Sybase IQ
performs a physical delete immediately or defers the delete to a
point later in the load. CREATE_HG_AND_FORCE_PHYSICAL_DELETE is ON by default, which instructs SAP Sybase IQ to perform physical deletes. |
REVERT_TO_V15_OPTIMIZER | REVERT_TO_V15_OPTIMIZER forces the query
optimizer to mimic SAP Sybase IQ
15.x behavior.
REVERT_TO_V15_OPTIMIZER='ON' by default
in all 16.0
databases upgraded from 15.x.
REVERT_TO_V15_OPTIMIZER='OFF' by default
in all newly created SAP Sybase IQ
16.0
databases. If you plan to use SAP Sybase IQ hash partitioning features, set the REVERT_TO_V15_OPTIMIZER='OFF' in databases upgraded from 15.x to 16.0. |