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.
If you are upgrading a multiplex database to SAP Sybase IQ 16.0, you must perform the steps in Release Bulletin > Installation and Upgrade > Database Upgrades > Upgrading a 15.x Multiplex Prevents the Coordinator from Starting [CR #733475] Upgrading a 15.x Multiplex Prevents the Coordinator from Starting. Failure to perform these steps may cause database corruption.
Customers upgrading from a previous release, for example, may need to change some initial compatibility options or rebuild wide columns to accommodate different datatypes. The new load engine provides better performance, but requires changes to the default memory allocation to use all available hardware resources efficiently.
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.
Continuous NBit dictionary compression replaces 1, 2, 3 byte dictionary compression as the default column storage mechanism in 16.0. All datatypes except LOB (character and binary) and BIT datatypes can be NBit columns.
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 auto-size limits.
Large memory represents the maximum amount of memory that SAP Sybase IQ can dynamically request from the OS for temporary use. Because some load operations may require more large memory than the 2GB default provides, adjust the startup options that control large and cache memory allocation based on the total amount of available physical memory.
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.
Changes to FP and HG indexes take advantage of the new column compression mechanism and improve load performance.
|
Index |
Description |
|---|---|
|
New Fast Projection (FP) Indexes |
Take advantage of the new continuous NBit dictionary compression, which 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. 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. |
New stored procedures return information about column indexes and constraints.
|
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. |
Some database options are not enabled to take advantage of 16.0 features. Maintaining limited compatibility after a database upgrade provides some flexibility to transition existing applications.