Post Upgrade Status

Indexes

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 SAP Sybase IQ 15, 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 .

Column Constraints

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.

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.

Database Options

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. If this option is ON, the database engine uses the `MINIMIZE_STORAGE`, `FP_LOOKUP_SIZE`, and `FP_LOOKUP_SIZE_PPM` options to optimize column storage. These options are ignored in 16.0. If this option is OFF, the database engine ignores 15.x options and columns conform to SAP Sybase IQ `NBit` storage options. Set this option to OFF to take advantage of `NBit` column compression.
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 structure, set this option to OFF. To take advantage of the new tiered structure, set this option to OFF. Use sp_iqrebuildindex to convert non-tiered `HG` indexes to tiered `HG` and vice-versa.
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 SAP Sybase IQ.

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.

If this option is ON, the database engine uses the MINIMIZE_STORAGE, FP_LOOKUP_SIZE, and FP_LOOKUP_SIZE_PPM options to optimize column storage. These options are ignored in 16.0.
If this option is OFF, the database engine ignores 15.x options and columns conform to SAP Sybase IQ NBit storage options.

Set this option to OFF to take advantage of NBit column compression.

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 structure, set this option to OFF.

To take advantage of the new tiered structure, set this option to OFF. Use sp_iqrebuildindex to convert non-tiered HG indexes to tiered HG and vice-versa.

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 SAP Sybase IQ.

Startup Options

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.

Object Names

Reserved words cannot be used as object names.

A 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.

To use a reserved word as an object name, enclosed the object name in brackets (regardless of the QUOTED_IDENTIFIER setting) or double quotes (if QUOTED_IDENTIFIER='ON' [default]):

// 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;

Stored Procedures

Use these stored procedures to review and change column indexes and constraints:

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 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.