Migration to Sybase IQ 15.1 creates a new Catalog Store. Migration creates and modifies system tables, system procedures, and options in the new Catalog Store to enable 15.1 features and removes preexisting options eliminated for version 15.1. For lists of these options and features, see the New Features Guide.
Unlike previous versions, Sybase IQ 15.1 preserves
previous settings of database options and reapplies them after you
create the new Sybase IQ 15.1 database. You no longer need
to run SELECT * from SYS.SYSOPTIONS before upgrading.
In Sybase IQ 15.1, 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 “SQL syntax for dbspace management and partitioning”, in Chapter 1, “New Features in Sybase IQ 15.0,” in the New Features Guide.
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 another filename. 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, then the new store marked as ‘MAIN’ has DBSpaceName = ‘IQ_SYSTEM_MAIN’, DBFileName = ‘IQ_SYSTEM_MAIN’ and path = ‘new_main_store.iq.’ For MPX migration, the location of the new main store must be visible to all nodes on the multiplex, and the -ms_filename argument is required 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. For main store size guidelines, see the New Features Guide.
Example 1 Schema unloading allows you to verify schema correctness and approximates 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” for more information.
Run iqunload -n to create the empty schema. This creates a SQL file with the entire schema called reload.sql in the current directory. The reload.sql file also contains a template CREATE DATABASE command that is commented out.
The generated reload.sql will not
contain any checkpoints. If you have a very large schema, you may
wish to edit the reload.sql and add a few checkpoints now and then.
If you do not, then IQ will end up with a lot of object metadata
objects requiring lots of (-iqmc) main cache
memory.
Using Interactive SQL or Sybase Central, create a new Sybase IQ 15.1 database with IQ SIZE and TEMPORARY SIZE clauses to create an IQ_SYSTEM_MAIN of 10 GB and IQ_SYSTEM_TEMP of 5 GB.
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 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 /dev/rdsk/c1t0d1 -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
