iqunload is a command line utility. Refer to this section for information about syntax, parameters, usage, examples, and permissions.
iqunload [ options ]
data:[ environment variable | file ]
The iqunload utility takes one or more parameters.
Option |
Description |
|---|---|
-ap <size> |
(Optional) Set page size for the new catalog store. |
-au |
(Required, except when specifying -n) Migrate the database. You can specify-au or-n as an argument, but not both. |
-c “keyword=value;...” |
(Required) Supply database connection parameters. You must specify a DBF parameter to specify the name of the database file for migration. The file path is either absolute or relative to the server startup directory. |
-dc |
(Optional) Recalculate computed columns in the database. |
-ms_filename |
Specify filename for new empty IQ_SYSTEM_MAIN store created during database migration. If not specified, the default new main store will be a file system file called new_main_store.iq. -ms_filename is optional for a simplex migration, but is required for a multiplex migration |
-ms_reserve |
(Optional) Size of new IQ_SYSTEM_MAIN reserve in MB. If unspecified, defaults to zero. |
-ms_size |
(Optional) Size of new IQ_SYSTEM_MAIN store in MB, computed by Sybase IQ based on the database size. The minimum, assuming a default page size, is 200MB. If the user specifies –ms_size value smaller than the computed value, IQ uses the computed value; otherwise the user–specified value is used. |
-n |
(Optional, except for schema unload.) Unload schema definition only. You can specify-au or-n as an argument, but not both. |
-new_startline |
(Optional) Specify startup switches for the new Sybase IQ 15.0 server that is the migration target. For a complete list of server startup switches, see the Utility Guide. |
-o <file> |
(Optional) Log output messages to file. |
-q |
(Optional) Run quietly. Suppress messages and windows. |
-r <file> |
(Optional) Specify name of generated the reload.sql command file. |
-t <list> |
(Optional) Output listed tables only. Can specify OwnerName.TableName or TableName alone. |
-v |
(Optional) Return verbose messages. |
-y |
(Optional) Replace existing reload schema SQL script with new output without confirmation. |
Example 1 Migrate a simplex database to 15.0 server:
iqunload –au –c “UID=DBA;PWD=SQL;DBF=W:\\mydevice\\test_dir\\test2.db”
Example 2 Unload schema from database and rename the reload.sql file to test2_reload.sql :
iqunload –n -c “UID=DBA;PWD=SQL;DBF=D:\\test_dir\\test2\\test2.db; ENG=myserver_test2” –r “D:\test\unload_dir\test2_reload.sql”
Example 3 Migrate database test3.db. The START connection parameter specifies switches for starting the database being reloaded. The –new_startline parameter specifies switches for starting the utility_db to create the new catalog store.
iqunload –au -c “ UID=DBA;PWD=SQL;DBF=D:\\test\\test3\\test3.db;START=–ch 128M –iqmc 50" –new_startline "–ch 256M –iqtc 400"
Do not include -iqnotemp XYZ in
the new start line or else migration will fail. As part of the
migration process, temp files will be added to IQ_SYSTEM_TEMP.
If the server is started with iqnotemp then the iqunload will NOT
be able to add these temp files.
Example 4 Migrate the database iq_demo.db using a raw device for a new IQ_SYSTEM_MAIN store.
Syntax example if you have a second physical drive 2:
iqunload –au –c "UID=DBA;PWD=SQL;DBF=asiqdemo.db" -ms_filename \\\\.\\PhysicalDrive2
Syntax example if you have a logical partition K:
iqunload –au –c "UID=DBA;PWD=SQL;DBF=asiqdemo.db" -ms_filename \\\\.\\K:
You may optionally specify the size and reserve size for the IQ_SYSTEM_MAIN store, for example:
–ms_size 10000 –ms_reserve 1000
Example 5 Migrate a database and specify startup parameters using a configuration file.
iqunload –au –c "UID=DBA;PWD=SQL;DBF=asiqdemo.db;start=@params.cfg"
Note that the user ID and password must be those for the old database. (The default password for 12.6 and 12.7 was upper case “SQL.”)
The specified user ID must have DBA authority.
For schema unloads, the iqunload utility requires the following minimum server versions:
Sybase IQ version 12.6 ESD #11.
Sybase IQ version 12.7 ESD #5.
For schema unload, start your existing 12.6 or 12.7 server and specify the -n parameter on the iqunload command. Schema unload creates schema load scripts and output files in the current directory.
When migrating a database, specify -au parameter.
Determining switches for migration The iqunload utility starts and communicates with a special IQ 12.7 server and the IQ 15.0 server. This section documents the default switches sent to these servers to help you understand the cache settings. You can use this information to determine if your existing IQ 12.7 / 12.6 server requires switches to be set during migration.
For most cases, you can skip to “Results of iqunload”.
The -au parameter causes the utility to start two servers:
iqunlspt – A special unload engine based on Sybase IQ 12.7 ESD 5.
iqsrv15 – The new Sybase IQ 15.0 server.
Make sure that your server starts cleanly in IQ 12.6
or 12.7 to minimize startup times.
iqunlspt starts on your Sybase IQ 12.6 or 12.7 database with the following command line arguments:
iqunlspt.exe -iqnotemp 100 -iqro 1 -c 48MB -gc 20 -gd all -gk all -gl all -gm 1 -gu all -ti 4400 -x shmem
iqunlspt also includes the -n parameter followed by a special randomly generated server name.
These cache settings suffice for most migration cases, although you may need to increase these values. At migration, data queries execute against the system catalogs, not IQ data, so the iqunlspt engine needs lower cache levels than complex IQ queries or multiple concurrent users. Note that the time required to start your existing Sybase IQ 12.6 or 12.7 database will also be required to start the iqunlspt.exe process. This time is included in the startup time for the iqunload.exe.
iqsrv15 starts with the following command line arguments:
iqsrv15 -gp 4096 -c 40p -gc 20 -gd all -gk all -gl all -gm 1 -gu all -qi -qs -ti 4400
iqsrv15 also includes the -n parameter followed by a special randomly generated server name. The -c 40p setting provides a larger cache for the catalog store, allowing the Sybase IQ 15.0 engine to execute many schema DDL statements.
Both of the server start commands above use the default values for -iqmc and -iqtc. If your Sybase IQ 12.6/12.7 server requires larger values for startup performance, consider adding these to the START= parameter (see docs below).
Results of iqunload The iqunload utility generates script and output files in the %IQTMP15% directory and removes them on completion.
By default, Sybase IQ allocates the lesser of 256MB or 40% of
physical memory for the Catalog Store cache size upper limit. Insufficient
cache causes iqunload errors like Tried
to allocate 4167598080 bytes, failed. If desired,
you can control the upper and lower limits for the Catalog Store cache
size by setting the –ch and -cl startup
switches through the StartLine [START] connection
parameter. For details about -ch and –cl,
see the Utility Guide.
Users with wide tables (large numbers of column/null values) should not decrease the Catalog Store page size for database migration.
If the database being migrated contains invalid views, Sybase IQ completes migration but issues user warnings. For example, this might occur if tables involved in a view were dropped.
If you set the IQTMP15 environment variable, you must set it to a valid directory name.
If the database being migrated is encrypted, then you must specify the DBKEY connection parameter to provide the encryption key for the database. The migrated database uses the same encryption key.
The iqunload utility automatically starts the old version of the server (12.6 or 12.7) in order to upgrade the catalog .db file, and shuts down the old server after the unload completes. The utility uses default values for the various cache sizes (catalog cache, main cache, cache). If the database being migrated is normally run with much higher cache values, specify these values as part of the START connection parameter to ensure that the server has adequate cache for the database being unloaded.
The current defaults are 48MB for the catalog cache size, 32MB for the main cache size and 24MB for the cache size.
Extra logging To start the IQ 15 server with extra logging, use the -new_startline parameter on the iqunload command to specify diagnostic parameters -z and -zr all.
For example:
iqunload -au -c "UID=DBA;PWD=SQL;DBF=W:\\iq-15\\unload\\127\\db\\iq127db.db" -new_startline "-z -zr all" -o d:\\iq15db.out
This statement causes the new iqsrv15 database engine to produce more verbose logging output to the file d:\iq15db.out.
For details about the -z diagnostic parameter, see the Utility Guide. For information about request logging (-zr), which has been updated for 15.0, see Request logging.
To start the bundled Sybase IQ 12.7 engine with extra logging, use the START= connection property to specify the -z and -zr all parameters. For example:
iqunload -v -au -c "UID=DBA;PWD=SQL;DBF=W:\\iq-15\\unload\\127\\db\\iq127db.db;START=-z -zr all -o d:\\iq127db.out"
This statement causes the bundled Sybase IQ 12.7 engine to produce more verbose logging output to the file d:\iq127db.out.
Examine the output file iq15db.out or iq127db.out for server error messages.
Cannot start database If the iqunload utility cannot start a database, it returns an error and echoes the command line so that you can correct errors.
Server not found If you see a message like the following, iqunload started the database but could not connect:
SQL error:Database server not found
Check to see if an iqunlspt process is running, and stop the process before retrying iqunload.
Open Task Manager and click the Processes tab. Locate the image name iqunlspt. Right-click iqunlspt and choose End Process.
If you try to run iqunload without killing the orphaned iqunlspt process, you may see the following error:
SQL error: Unable to start specified database: autostarting database failed.
Obsolete stored procedures logged Migration replaces 12.7 login procedures to support new 15.0 login management functionality. The procedure sp_login_environment replaces the 12.7 default login procedure DBA.sp_iq_process_login and the post-login procedure dbo.sa_post_login_procedure replaces 12.7 default post-login procedure DBA.sp_iq_process_post_login. The iqunload utility generally replaces obsolete options with their new defaults, but if the 12.7 option is set on a specific user instead of PUBLIC (the default), the log file may report errors like the following:
E. 10/31 16:53:40. Login procedure 'DBA.sp_iq_process_login' caused SQLSTATE '52W09' E. 10/31 16:53:40. Procedure 'sp_iq_process_login' not found
See Managing login policies overview. for an overview
of login management functionality.
