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 introduced in the initial 16.0 version.
This topic highlights migration issues and features which impact load performance. See the appropriate topics in the SAP Sybase IQ core documentation set for details. Migration (Linux and UNIX) and Migration (Windows) are new administration manuals in 16.0, describing the steps for upgrading your database, steps to install maintenance releases, and information on upgrading to the role-based security model. Administration: Load Management is also new in 16.0, describing data import and export procedures.
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 value of the FP_NBIT_AUTOSIZE_LIMIT option.
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.
Index | Description |
---|---|
New Fast Projection (FP) Indexes | Take advantage of the new continuous NBit dictionary compression, which
replaces 1, 2, and 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. See CREATE_HG_WITH_EXACT_DISTINCTS Option in Reference: Statements and Options. Use sp_iqrebuildindex to convert non-tired HG indexes to tiered HG and vice-versa. |
Procedure | Description |
---|---|
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. See sp_iqindexmetadata Procedure in Reference: Building Blocks, Tables, and Procedures. |
sp_iqcolumnmetadata | Returns FP
index metadata for one or more user tables or all tables in the
database. See sp_iqcolumnmetadata Procedure in Reference: Building Blocks, Tables, and Procedures. |
sp_iqindexrebuildwidedata | Identifies wide columns that you must rebuild
before they are available for read/write activities. Output includes
statements that you can use with sp_iqrebuildindex to rebuild the columns. See sp_iqindexrebuildwidedata Procedure in Reference: Building Blocks, Tables, and Procedures. |
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. See sp_iqrebuildindex Procedure in Reference: Building Blocks, Tables, and Procedures. |
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. |