SAP Sybase IQ 16 databases upgraded from SAP Sybase IQ 15.x are initially set to run in SAP Sybase IQ 15.x compatibility mode. To complete the change from 15.x to 16.0, you must explicitly change several 15.x compatibility settings to complete the 16.0 upgrade.
In Fast Projection (FP) indexes, continuous NBit dictionary compression 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. All data types except LOB (both character and binary) and BIT data types may be NBit columns.
If FP_NBIT_IQ15_COMPATIBILITY is OFF, IQ UNIQUE determines whether the column loads as Flat FP or NBit. Setting IQ UNIQUE to 0 loads the column as Flat FP. Columns without an IQ UNIQUE constraint load as NBit up to the NBit auto-sizing limits.
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. If this option is ON, all new HG indexes are non-tiered. To take advantage of the new structure, set this option to OFF. Use sp_iqrebuildindex to convert non-tiered HG indexes to tiered HG and vice-versa .
Constraint | Description |
---|---|
IQ UNIQUE |
In SAP Sybase IQ 16.0, IQ UNIQUE explicitly defines the expected cardinality of a column and determines whether the column loads as Flat FP or NBit. Columns retain their IQ UNIQUE(n) value during a 15.x to 16.0 database upgrade. Setting IQ UNIQUE to 0 loads the column as Flat FP. Columns without an IQ UNIQUE constraint or columns with an IQ UNIQUE n value less that is less than the limit defined by the FP_NBIT_AUTOSIZE_LIMIT option is not necessary. Auto-size functionality automatically sizes all low or medium cardinality columns as NBit. Use IQ UNIQUE in cases where you want to where you want to load the column as Flat FP or when you want to load as NBit and the number of distinct values exceeds the auto-size limits. |
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 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. |
Some load operations may require more large memory than the 2GB default provides. If memory requirements exceed the default, use the - iqlm startup option to increase the memory that SAP Sybase IQ can dynamically request from the OS. Set –iqlm as a switch as part of the command or configuration file that starts the server.
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.
Reserved words cannot be used as object names.
An SAP Sybase IQ 15.x database could contain tables, columns, and other objects named row. In SAP Sybase IQ 16.0, row is a reserved word and cannot be used as an object name.
// QUOTED_IDENTIFIER ON | OFF select * from [row]; alter table row2 rename [row] to col_row; // QUOTED_IDENTIFIER='ON' select "row" from row2; alter table "row" rename rownew;
Procedure |
Description |
---|---|
sp_iqcolumnmetadata |
Returns index metadata for all columns in one or more tables. |
sp_iqindexmetadata |
Returns details about column indexes, including the index types (Flat FP, NBit, HG, and tiered HG), distinct counts, IQ UNIQUE n value, and NBit dictionary size. |
sp_iqrebuildindex |
Rebuilds FP indexes (Flat FP as NBit, or NBit as Flat FP) and HG indexes (single HG as tiered HG, or tiered HG as single HG). Before you can insert or update new data, you must rebuild all columns greater than 255 bytes wide. The index_clause can reset IQ UNIQUE n to an explicit value from 0 (to recast an NBit column to Flat FP) up to the limits defined in the FP_NBIT_AUTOSIZE_LIMIT and FP_NBIT_LOOKUP_MB options. sp_iqrebuildindex also enables read-write access to columns that contain large object (LOB) data. LOB columns migrated from 15.x databases are read-only until you run sp_iqrebuildindex. The estimated cardinality for NBit columns with an IQ UNIQUE value below or equal to the FP_NBIT_AUTOSIZE_LIMIT is stored as 0 regardless of the FP_NBIT_IQ15_COMPATIBILITY setting. This affects the value returned from sp_iqindexmetadata. |
sp_iqindexrebuildwidedata |
Identifies wide columns that you must rebuild before they are available for read/write activities. sp_iqindexrebuildwidedata also generates a list of statements that you can use to rebuild the columns. This applies to CHAR, VARCHAR, BINARY, and VARBINARY columns wider than > 255 characters, as well as all Long Varchar and Long Binary columns. |