iAnywhere.com - Unloading a database using the dbunload command-line utility

Unloading a database using the dbunload command-line utility

Syntax

dbunload [ options ] [ directory ]

Option	Description
@data	Read options from the specified environment variable or configuration file.
-ac "keyword=value; ..."	Supply connection parameters for the reload.
-an database	Create a database file with the same settings as the database being unloaded, and automatically reloads it.
-ap size	Specify the page size of the new database (-an or -ar must also be specified).
-ar [directory]	Rebuild and replace database.
-c "keyword=value; ..."	Supply database connection parameters for unload.
-d	Unload data only.
-e table, ...	Do not unload listed tables.
-ea algorithm	Specify which strong encryption algorithm to encrypt your database with: you can choose AES or AES_FIPS.
-ek key	Specify encryption key for new database.
-ep	Prompt for encryption key for new database.
-ii	Internal unload, internal reload (default).
-ix	Internal unload, external reload.
-jr	Ignore Java when unloading or reloading.
-m	Do not preserve user ids for a replicating database.
-n	No data—schema definition only.
-o filename	Log output messages to a file.
-p char	Specify the escape character for external unloads (default "\").
-q	Quiet mode—no windows or messages.
-r reload-file	Specify the name and directory of generated reload Interactive SQL command file (default reload.sql).
-t table,...	Unload only the listed tables.
-u	Unordered data. Do not use indexes to unload data.
-v	Verbose messages.
-xi	External unload, internal reload.
-xx	External unload, external reload.
-y	Replace the command file without confirmation.

Description

The directory is the destination directory where the unloaded data is to be placed. The reload.sql command file is always relative to the current directory of the user.

In the default mode, or if -ii or -ix is used, the directory used by dbunload to hold the data is relative to the database server, not to the current directory of the user.

For details of how to supply a filename and path in this mode, see UNLOAD TABLE statement.

If -xi or -xx is used, the directory is relative to the current directory of the user.

If no list of tables is supplied, the whole database is unloaded. If a list of tables is supplied, only those tables are unloaded.

Unloaded data includes the column list for the LOAD TABLE statements generated in the reload.sql file. Unloading the column list facilitates reordering of the columns in a table. Tables can be dropped or recreated, and then repopulated using reload.sql.

Exit codes are 0 (success) or non-zero (failure).

There are special considerations for unloading databases involved in replication. For information, see Unloading and reloading a database participating in replication.

Password case sensitivity

When a database is unloaded using -an or -ar, the password case sensitivity setting is preserved. For example, if you unloaded a database with case sensitive passwords and specified -an or -ar, the newly-created database would also have case-sensitive passwords. When you do not specify -an or -ar, password case sensitivity is determined as follows:

if the password was originally entered into a database that used case sensitive passwords, then the password remains case sensitive, even when reloaded into a case-insensitive database.
if the password was originally entered into a database with case insensitive passwords, then the lowercase version of the password must be used if the password is reloaded into a case sensitive database.

Encrypted databases

If you want to unload a strongly encrypted database, you must provide the encryption key. You can use the DatabaseKey (DBKEY) connection parameter to provide the key in the command. Alternatively, if you want to be prompted for the encryption key rather than entering it in plain view, you can use the -ep server option as follows:

dbunload -c "dbf=enc.db;start=dbeng9 -ep"

If you are using dbunload -an to unload a database and reload into a new one, and you want to use the -ek or -ep options to set the encryption key for the new database, keep the following in mind:

If the original database is strongly encrypted, you need to specify the key for the original database using the DatabaseKey (DBKEY) connection parameter in the -c option, not using -ek or -ep.
Using the -ek and -ep options, it is possible to unload an unencrypted database and reload into a new, strongly encrypted database. When you use -ep and -an, you must confirm the key correctly or the unload fails.
If the original database is strongly encrypted, but the -ek and -ep options are not used, then the new database will be encrypted with simple encryption.
The -ek and -ep options are ignored if -an is not specified.

For more information about encryption, see -ep server option or the DatabaseKey connection parameter [DBKEY].

Rebuilding a database

To unload a database, start the database server with your database, and run the Unload utility with the DBA user ID and password.

To reload a database, create a new database and then run the generated reload.sql command file through Interactive SQL.

On Windows 95/98/Me, Windows NT/2000/XP, and UNIX, there is a file (rebuild.bat, rebuild.cmd, or rebuild) that automates the unload and reload process.

For more information, see The Rebuild utility.

Unload utility options

@data

Use this option to read in options from the specified environment variable or configuration file. If both exist with the same name, the environment variable is used.

For more information about configuration files, see Using configuration files.

If you want to protect passwords or other information in the configuration file, you can use the File Hiding utility to obfuscate the contents of the configuration file.

For more information, see Hiding the contents of files using the dbfhide command-line utility.

Connection parameters for reload database (-ac)

This option causes the Unload utility to connect to an existing database and reload the data directly into it. You can combine the operation of unloading a database and reloading the results into an existing database using this option.

Typically, you would create a new database using the Initialization utility, and then reload it using this option. This method is useful when you want to change initialization options, such as page size or collation. If you are changing collations, the -xx option should also be used to ensure that character set translation occurs properly for both the old and new databases.

For example, the following command (which should be entered all on one line) loads a copy of the asademo.db database into an existing database file named newdemo.db:

dbunload -c "uid=DBA;pwd=SQL;dbf=asademo.db" -ac "uid=DBA;pwd=SQL;dbf=newdemo.db"

If you use this option, no interim copy of the data is created on disk, so you do not specify an unload directory in the command. This provides greater security for your data.

Create a database for reloading (-an)

You can combine the operations of unloading a database, creating a new database, and loading the data using this option. This option applies to personal server connections, and network server connections over shared memory. When you specify -an, the case sensitivity setting for passwords is preserved.

Typically, you would use this option when you do not want to change the initialization options of your database. The options specified when you created the source database are used to create the new database.

For example, the following command (which should be entered all on one line) creates a new database file named asacopy.db and copies the schema and data of asademo.db into it:

dbunload -c "uid=DBA;pwd=SQL;dbf=asademo.db" -an asacopy.db

If you use this option, no interim copy of the data is created on disk, so you do not specify an unload directory in the command. This provides greater security for your data, but at some cost for performance.

You do not need to specify a directory for this option.

When the new database is created, the dbspace file names have an R appended to the file name to prevent file name conflicts if the dbspace file for the new database is created in the same directory as the dbspace for the original database. For example, if an unloaded database has a dbspace called library in the file library.db, then the library dbspace for the new database is library.dbR.

Set page size for the new database (-ap)

This option allows you to set the page size of the new database. The page size for a database can be (in bytes) 1024, 2048, 4096, 8192, 16384, or 32768, with 2048 being the default. You must specify either -an or -ar with this option. If there are already databases running on the database server, the server's page size (set with the -gp option) must be large enough to handle the new page size.

For more information, see -gp server option.

Rebuild and replace database (-ar)

This option creates a new database with the same settings as the old database, reloads it, and replaces the old database. If you use this option, there can be no other connections to the database, and the database connection must be local, not over a network. When you specify -an, the case sensitivity setting for passwords is preserved.

If you specify an optional directory, the transaction log offsets are reset for replication purposes, and the transaction log from the old database is moved to the specified directory. The named directory should be the directory that holds the old transaction logs used by the Message Agent and the Replication Agent. The transaction log management is handled only if the database is used in replication: if there is no SQL Remote publisher or LTM check, then the old transaction log is not needed and is deleted instead of being copied to the specified directory.

For more information on transaction log management, see Backup methods for remote databases in replication installations.

Connection parameters for source database (-c)

For a description of the connection parameters, see Connection parameters. The user ID should have DBA authority, to ensure that the user has permissions on all the tables in the database.

For example, the following statement unloads the asademo database running on the sample_server server, connecting as user ID DBA with password SQL. The data is unloaded into the c:\unload directory.

dbunload -c "eng=sample_server;dbn=asademo;uid=DBA;pwd=SQL" c:\unload

Unload data only (-d)

With this option, none of the database definition commands are generated (CREATE TABLE, CREATE INDEX, and so on); reload.sql contains statements to reload the data only.

No data output for listed tables (-e)

This option is accessible only when you run this utility at a command prompt. If you want to unload almost all of the tables in the database, the -e option unloads all tables except the specified tables. A reload.sql file created with the -e option should not be used to rebuild a database because the file will not include all the database tables.

Specify encryption algorithm (-ea)

This option allows you to choose which strong encryption algorithm used to encrypt your new database. You can choose either AES (the default) or AES_FIPS for the FIPS-approved algorithm. AES_FIPS uses a separate library and is not compatible with AES. Algorithm names are case insensitive. If you specify the -ea option, you must also specify -ep or -ek. If you specify -ea without specifying -an, the -ea option is ignored.

For more information about strong database encryption, see Strong encryption.

Separately licensable option required

Strong database encryption using AES_FIPS requires that you obtain the separately-licensable SQL Anywhere Studio security option and is subject to export regulations.

To order this component, see Separately-licensable components.

Specify encryption key (-ek)

This option allows you to specify an encryption key for the new database created if you unload and reload a database (using the -an option). If you create a strongly encrypted database, you must provide the encryption key to use the database or transaction log in any way. The algorithm used to encrypt the database is AES or AES_FIPS as specified by the -ea option. If you specify the -ek option without specifying -ea, the algorithm used will be AES. If you specify -ek without specifying -an, the -ek option is ignored.

Caution Protect your key! Be sure to store a copy of your key in a safe location. A lost key will result in a completely inaccessible database, from which there is no recovery.

For more information about strong database encryption, see Strong encryption.

Prompt for encryption key (-ep)

This option prompts you to specify an encryption key for the new database created if you unload and reload your database (using the -an option). It provides an extra measure of security by never allowing the encryption key to be seen in clear text. If you specify -ep without specifying -an, the -ep option is ignored. If you specify -ep and -an, you must input the encryption key twice to confirm that it was entered correctly. If the keys don't match, the unload fails.

For more information about strong database encryption, see Strong encryption.

Internal versus external unloads and reloads

The following options offer combinations of internal and external unloads and reloads: -ii, -ix, -xi, and -xx. A significant performance gain can be realized using internal commands (UNLOAD/LOAD) versus external commands (Interactive SQL's INPUT and OUTPUT statements). However, internal commands are executed by the server so that file and directory paths are relative to the location of the database server. Using external commands, file and directory paths are relative to the current directory of the user.

In Sybase Central, you can specify whether to unload relative to the server or client.

For more information on filenames and paths for the Unload utility, see UNLOAD TABLE statement.

Use internal unload, internal reload (-ii)

This option uses the UNLOAD statement to extract data from the database, and uses the LOAD statement in the reload.sql file to repopulate the database with data. This is the default.

Use internal unload, external reload (-ix)

This option uses the UNLOAD statement to extract data from the database, and uses the Interactive SQL INPUT statement in the reload.sql file to repopulate the database with data.

Ignore Java (-jr)

With this option, Java is ignored when unloading or reloading the database.

-m

With this option, user IDs are not preserved for a replicating database.

Unload schema definition only (-n)

With this option, none of the data in the database is unloaded; reload.sql contains SQL statements to build the structure of the database only.

Log output messages to file (-o)

Write output messages to the named file.

Escape character (-p)

The default escape character (\) for external unloads (dbunload -x option) can be replaced by another character using this option. This option is available only when you run this utility from a command prompt.

Operate quietly (-q)

Do not display output messages. This option is available only when you run this utility from a command prompt. If you specify -q, you must also specify -y or the unload will fail.

Specify reload filename (-r)

Modify the name and directory of the generated reload Interactive SQL command file. The default is reload.sql in the current directory. The directory is relative to the current directory of the client application, not the server.

Unload only listed tables (-t)

Provide a list of tables to be unloaded. By default, all tables are unloaded. Together with the -n option, this allows you to unload a set of table definitions only.

Output unordered data (-u)

Normally, the data in each table is ordered by the primary key. Use this option if you are unloading a database with a corrupt index, so that the corrupt index is not used to order the data.

Enable verbose mode (-v)

The table name of the table currently being unloaded, and how many rows have been unloaded, appears. This option is available only when you run this utility from a command prompt.

Use external unloading, internal reload (-xi)

This option unloads data to the dbunload client, and uses the LOAD statement in the generated reload command file, reload.sql, to repopulate the database with data.

Use external unloading, external reload (-xx)

This option unloads data to the dbunload client, and uses the Interactive SQL INPUT statement in the generated reload command file, reload.sql, to repopulate the database with data.

Operate without confirming actions (-y)

Choosing this option replaces existing command files without prompting you for confirmation. If you specify -q, you must also specify -y or the unload will fail.