iqlsunload [ options ] directory [ @data ]
data:[ environment variable | file
directory is a required parameter that contains a path relative to the database on the database server, not relative to the path where iqlsunload is executed. This specification is required to generate extract_data.sql. The directory must exist. The unload procedure on the server side does not create it. The iqlsunload utility unloads the actual data files to this target directory when you use Interactive SQL to run extract_data.sql. For example, extract_data.stql references the directory as follows:
CREATE VARIABLE @extract_directory varchar(2048); SET @extract_directory = '/testIQ15/database/IQ127_beta2_mpx_test/demo_r1/'; SET TEMPORARY OPTION temp_extract_directory = '/testIQ15/database/IQ127_beta2_mpx_test/demo_r1/';
The iqlsunload application takes one or more optional parameters. Use the appropriate parameters for desired behavior; you cannot perform both functions in the same command.
Parameter |
Description |
|---|---|
-al |
Unload IQ local store schema and data. |
-c “keyword=value;...” |
Supply database connection parameters. |
-h |
Print out the syntax (help) for the utility. |
-o <file> |
Log output messages, including errors, to file. |
-q |
Suppress messages and windows. |
-r <directory> |
Specify the directory where SQL scripts are generated. The default reload command file is reload.sql in the current directory. The directory is relative to the current directory of the client application, not the server. |
-t <list> |
Output listed tables only. Can specify OwnerName.TableName or TableName alone. (Cannot be specified with -al). |
-v |
Output verbose messages. |
-y |
Replace existing reload schema SQL script without confirmation. |
The iqlsunload utility requires a minimum server version of Sybase IQ 12.7 ESD #5.
The database must be running before you can run iqlsunload.
The specified directory is a path relative to the database on the database server, not relative to the path where iqlsunload is executed.
iqlsunload connects to the database using connection parameters -c in a way which is consistent with other tools like dbping and dbisql as described in the Utility Guide.
Run iqlsunload from within the $IQDIR15/lsunload directory so that it picks up updated libraries before resolving any IQ 12.7 libraries.
Objects unloaded Unloading the local store does not unload database objects that are not part of the local store.
The -al parameter unloads these persistent objects:
Base tables
Global temporary tables
Indexes
Domains (user-defined data types)
Constraints (column check constraint, table constraint, primary key, foreign key, unique, default, IQ unique, not null)
Views
Stored procedures and functions
Messages
Remote servers and external logins
Events
Sybase IQ 15.2 no longer allows users with empty user names.
Users with empty user names cannot be dropped with the 12.6 or 12.7 server and cannot be migrated. The schema reload operation warns that an empty user name has been encountered and that the user will not be re-created. The reload operation ignores such users and any associated objects.
When unloading tables:
Join indexes, which do not exist in a local store, are unloaded only when the user specifies unloading schema and data for a list of tables (-t). All tables involved in the index must be specified with the -t parameter.
When extracting table schema and data only, be sure that the collation of the database you run the reload script against matches the collation of the database specified on the initial iqlsunload command.
If you do not qualify table names with owner names, iqlsunload extracts table data from all tables with that table name.
The iqlsunload utility ignores any system tables or nonexistent tables specified.
Example Unload local stores from a database called mpxtest2, extracting any table data to the directory /mydevice/test_dir/unload_dir:
iqlsunload -o iqunload_624.out -al -c “UID=DBA;PWD=SQL;ENG=myserver_mpxtest02” /mydevice/test_dir/unload_dir
This command creates these scripts in the current directory:
reload_schema.sql – recreates schema for unloaded objects (either objects from local store or tables selected by the user.) This script is executed against a node that writes to the multiplex. This node could can be either the existing writer node or a writer or coordinator for the multiplex after migration, depending on where you plan to recreate the schema.
extract_data.sql – extracts table data for the unloaded tables from the local store. Execute this script in Interactive SQL while connected to the query node from which it was generated. When this script executes, it generates the data files into the directory data. This script’s contents follow the guidelines set out in Chapter 7, “Moving Data In and Out of Databases,” in the Sybase IQ System Administration Guide.
reload_data.sql – loads extracted table data. This script is executed on the node where you ran reload_schema.sql and reloads the data extracted from the extract_data.sql file.
You must have DBA authority to run this utility.
