Migrating to Sybase IQ 15.2 creates a new catalog store. Migration creates and modifies system tables, system procedures, and options in the new Catalog Store to enable 15.2 features and removes options eliminated for version 15.2.
Sybase IQ 15.2 preserves previous settings of
database options and reapplies them after you create the new 15.2 database.
You no longer need to run SELECT * from SYS.SYSOPTIONS
before upgrading.
In Sybase IQ 15.2, all user data should reside in a user dbspace comprised of one or more files. Migration converts main dbspaces into files under one user dbspace, IQ_MAIN, for the IQ main store, and temporary dbspaces into files under a single temporary dbspace, IQ_SYSTEM_TEMP, for a single IQ temporary store. Existing Catalog Store dbspaces remain as dbspaces with a single file. All of the old main dbspaces become files in the new iq_main user main dbspace. Migration sets the option PUBLIC.default_dbspace to the value “iq_main.”
The logical names for files created from converted dbspaces are the dbspace name followed by an underscore and the file ID. For example, a main dbspace with file ID 16384 becomes IQ_SYSTEM_MAIN_16384.
For details on creating new dbspaces after migration and how to move objects from one dbspace to another, see "Dbspace management example," in Chapter 5, "Working with Database Objects," in the System Administration Guide: Volume 1
Migration creates a new system file for the IQ_SYSTEM_MAIN dbspace that contains no tables. By default the name of this file is new_main_store.iq, but you can use the ms_filename argument to specify a different file name. The iqunload utility computes the size of the new IQ_SYSTEM_MAIN based on the size of your existing database.
If you accept the default settings for iqunload -au -c, the new store marked as MAIN has DBSpaceName = IQ_SYSTEM_MAIN, DBFileName = IQ_SYSTEM_MAIN and path = new_main_store.iq. For multiplex migration, the location of the new main store must be visible to all nodes on the multiplex, and you must use the -ms_filename argument to specify the path instead of the default value of new_main_store.iq.
When you migrate a database, you may specify which file to use for the new IQ_SYSTEM_MAIN dbspace, its name, whether or not to use a raw device, and the size of the main store and its reserve. See “Understanding iqunload” for iqunload parameters that determine these specifications and their defaults. See "Sizing guidelines for main and temporary stores," in Chapter 5, "Working with Database Objects,” in the System Administration Guide: Volume 1.
Example 1 Schema unloading allows you to verify schema correctness and estimates how long migration may take. For example:
Make sure that you have copied the required scripts to your IQ 12.x server. See “Correcting invalid SQL syntax”.
Run iqunload -n to create the empty schema. This creates a SQL file with called reload.sql in the current directory that contains the entire schema. The reload.sql file also contains a template CREATE DATABASE command that is commented out.
The generated reload.sql does not
contain any checkpoints. If you have a very large schema, you may
want to edit reload.sql and add a few checkpoints.
If you do not, IQ generates additional metadata objects that requiring
extra (-iqmc) main cache memory.
Use Interactive SQL or Sybase Central to create a new Sybase IQ 15.2 database with IQ SIZE and TEMPORARY SIZE clauses to create an IQ_SYSTEM_MAIN of 10GB and IQ_SYSTEM_TEMP of 5GB.
For example:
CREATE DATABASE 'test.db' IQ PATH 'test.iq' IQ SIZE 10240 TEMPORARY PATH 'test.iqtmp' TEMPORARY SIZE 5120
Start the new database, connect to it, and execute the reload.sql file generated by iqunload -n against the new database. The execution time roughly approximates the actual time to allow for database migration, excluding validation checks.
Example 2 You can specify the IQ_SYSTEM_MAIN size in the database migration command. The -ms_size parameter requires a value in MB, not GB. Omit -ms_size and -ms_reserve if you are specifying a raw device. For a raw device, you must specify an unused raw partition.
For example, this statement creates an IQ_SYSTEM_MAIN on a raw device:
iqunload -au -ms_filename \\\\.\\PhysicalDrive1 -c "UID=DBA;PWD=SQL;DBF=latest.db"
Example 3 In CREATE DATABASE syntax, size units are in MB, not GB. The following statement creates a database with 100 GB IQ_SYSTEM_MAIN with 100 GB reserve:
CREATE DATABASE 'test.db' IQ PATH 'test.iq' IQ SIZE 102400 IQ RESERVE 102400 TEMPORARY PATH 'test.iqtmp' TEMPORARY SIZE 5120
