Understanding Data Storage Changes and Schema Reload

Migrating to Sybase IQ 15.4 creates a new catalog store and changes some Sybase IQ 12.7/12.6 options.

dbspaces

In Sybase IQ 15.4, 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 Sybase IQ main store, and temporary dbspaces into files under a single temporary dbspace, IQ_SYSTEM_TEMP, for a single Sybase 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.

Main Store

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.

Unloading a Schema

Schema unloading allows you to verify schema correctness and estimates how long migration may take.

Make sure that you copy the required scripts to your Sybase IQ 12.x server.
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.

Note: 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.4 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.

Migrating IQ_SYSTEM_MAIN

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.

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"