SAP Sybase IQ 16 databases upgraded from 12.7 are initially set to run in 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.
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.
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 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. |
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.
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]):
| 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. |
In SAP Sybase IQ 15 and later, the character encoding specification for the EUC–TAIWAN collation now uses the EUC_TW character set. You must re-create indexes on data in version 12.7 or earlier databases that use the EUC_TAIWAN collation to make them work with SAP Sybase IQ 16.
Compare your existing params.cfg files with the new default.cfg file created by the installation. The installation does not update or overwrite existing params.cfg files. In each params.cfg file, update any parameter defaults that differ from those in the default.cfg file, while maintaining any customized parameter settings that are appropriate for your system. Add any new startup parameters in default.cfg to your params.cfg file. The -gl parameter, for example, is required for server startup in version 12.5 and later.
SAP Sybase IQ preserves the settings of all 12.7 database options that are still valid in migrated databases. Check for deprecated features.
Back up your databases again with the BACKUP statement. If you use the BACKUP statement instead of a system–level backup, you can run backups and queries concurrently.
For a multiplex migration, back up only the coordinator only in this manner. For secondary servers, run the dbbackup utility from the secondary server directory.