iqlsunload is a command line utility. Refer to this section for information about syntax, parameters, usage, examples, and permissions.
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 on the server side will 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. (Cannot be specified with -t) |
-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 |
Run quietly. Suppress messages and windows. |
-r <directory> |
Specify the directory where SQL scripts are generated. The default reload command file is reload.sqlin 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 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.
The iqlsunload tool 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 Sybase IQ Utility Guide.
You must run the iqlsunload tool 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 the following 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.1 no longer allows users with empty user names. Users of this type could formerly be created using statements like:
GRANT CONNECT TO ““ IDENTIFIED BY 'sql'
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 was encountered and the user will not be recreated. The reload operation ignores such users and any associated objects.
When unloading tables, note that:
Join indexes, which do not exist in a local store, are only unloaded 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
The preceding command creates three 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 will write to the multiplex. This node could either be 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 will reload the data extracted from the extract_data.sql file.
You must have DBA authority to run this utility.
