See the following table for steps to address elements that cause iqunload failures.
Problem |
Solution |
---|---|
A |
Remove the owner name. |
If a |
Prefix the table name with the owner name. |
If an object name (such as a table, column, variable or parameter name) corresponds to a reserved word introduced in a later version of Sybase IQ or SQL Anywhere (such as NCHAR), then the reload fails. (For reserved words, see the Sybase IQ Reference Manual and SQL Anywhere Server SQL Reference.) For example: CREATE PROCEDURE p( ) BEGIN DECLARE NCHAR INT; SET NCHAR = 1; END |
Change all references to the reserved word to use a different name. For variable names, prefixing the name with @ is a common convention that prevents naming conflicts. |
Views that use Transact–SQL™ outer joins (by specifying *= or =*) may not be created properly when they are reloaded. |
Add the following line to the reload script: SET TEMPORARY OPTION tsql_outer_joins='on' and also set this option for your database.You should later rewrite any views or Stored Procedures that use Transact–SQL outer joins. |
Stored procedures that use Transact–SQL outer joins may not work correctly. |
Rewrite views and stored procedures. |
Functions
that have |
|
If the examples in the above two tables do not apply to you, skip the next section, and proceed directly to either “Migrating simplex databases” or “Migrating multiplex databases”.
Sybase recommends this procedure to avoid the SQL syntax problems that cause failures.
Copy the following scripts from $IQDIR15/unload to $ASDIR/scripts:
unloadold.sql
unload.sql
optdeflt.sql
opttemp.sql
Use your 12.x server to start the database you want to migrate.
Use the iqunload utility to run a schema unload. For example:
iqunload –n –c "UID=DBA;PWD=SQL;ENG=my_eng;DBN=my_dbname".
The schema unload produces a reload.sql script that contains the schema for the database being migrated in the directory from which you ran the unload.
As an aid to users, the generated reload.sql file contains a sample CREATE DATABASE statement, for example:
-- CREATE DATABASE command: CREATE DATABASE '/iq-15/unload/127/db/iq127db.db' LOG ON '/iq-15/unload/127/db/iq127db.log' CASE IGNORE ACCENT IGNORE PAGE SIZE 4096 COLLATION 'ISO_BINENG' BLANK PADDING ON JCONNECT OFF CHECKSUM OFF IQ PATH 'iq127db.iq' IQ SIZE 50 TEMPORARY PATH 'iq127db.iqtmp' TEMPORARY SIZE 25 MESSAGE PATH 'iq127db.iqmsg' IQ PAGE SIZE 131072 BLOCK SIZE 8192
Running the reload.sql script does not execute the sample statement, but you can use this generated statement as a template when you create your database with Sybase IQ 15.1. If you want to create a completely new Sybase IQ database, see Chapter 1, “New Features in 15.x,” in the New Features Guide for sizing guidelines.
Start the utility database using the Sybase IQ 15.1 Server, and create a new, empty, Sybase IQ 15.1 database.
Use Interactive SQL to run the reload.sql script that contains the unloaded schema against the new Sybase IQ 15.1 database. This loads the schema of the old database into an Sybase IQ 15.1 database. If there are any SQL syntax errors, you will receive warnings when running the SQL script. Ignore the errors found in order to get a complete list of the problems. Modify the database to be migrated to eliminate the errors found. See “Correcting invalid SQL syntax” to determine how to fix the problems.
Perform this process iteratively if necessary until you can cleanly reload the schema unloaded from the database to be migrated, against the empty 15.1 database.