SAP Sybase IQ 16 databases upgraded from SAP Sybase IQ 12.7 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 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 .
| 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 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. |
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.
// 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 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.