Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.1 (Français) » SQL Anywhere Server - Database Administration » Administering Your Database » Database administration utilities

 

Unload utility (dbunload)

Unloads a database into a SQL command file.

Syntax
dbunload [ options ] [ directory ]
Option Description
@data

Read in options from the specified environment variable or configuration file. 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. See File Hiding utility (dbfhide).

-ac "keyword=value; ..."

Connect to an existing database and reload the data directly into it, combining the operations of unloading a database and reloading the results into an existing database. This option is not supported on Windows Mobile.

For example, you could 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.

The following command (entered all on one line) loads a copy of the c:\mydata.db database into an existing database file named c:\mynewdata.db:

dbunload -c "UID=DBA;PWD=sql;DBF=c:\mydata.db"
         -ac "UID=DBA;PWD=sql;DBF=c:\mynewdata.db"

If the original database was created using version 9 or earlier of SQL Anywhere and the new database is not already running, you must provide a database server name in the -ac option. For example:

dbunload -c "UID=DBA;PWD=sql;DBF=c:\mydata.db"
          -ac "UID=DBA;PWD=sql;DBF=c:\mynewdata.db;ENG=newserver"

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

-an database

Combine the operations of unloading a database, creating a new database, and loading the data using this option. This option is not supported on Windows Mobile or when rebuilding version 9 or earlier databases on Mac OS X on Intel.

The options specified when you created the source database are used to create the new database. However, you can change the initialization options as necessary by specifying other supported dbunload options (such as -ap to change the page size or -et to enable table encryption).

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

dbunload -c "UID=DBA;PWD=sql;DBF=c:\mydata.db"
         -an c:\mydatacopy.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.

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.

The file specified by -an is relative to the database server.

-ap size Set the page size of the new database. This option is ignored unless -an or -ar is also used. The page size for a database can be (in bytes) 2048, 4096, 8192, 16384, or 32768, with the default being the page size of the original database. 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. See -gp server option.
-ar [ directory ]

Create a new database with the same settings as the old database, reload it, and replace the old database. However, you can change the initialization options as necessary by specifying other supported dbunload options (such as -ap to change the page size or -et to enable table encryption).

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. This option is not supported on Windows Mobile or when rebuilding version 9 or earlier databases on Mac OS X on Intel.

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. See Backing up databases involved in synchronization and replication.

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.

If you are rebuilding an encrypted database, the encryption key for the original and new databases must be the same.

Using the -ar option resets the database truncation points to zero.

-c "keyword=value; ..."

Specify the connection parameters for the source database. 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 sample database, connecting as user ID DBA with password sql. The data is unloaded into the c:\unload directory.

dbunload -c "DBF=samples-dir\demo.db;UID=DBA;PWD=sql" c:\unload

For information about samples-dir, see Samples directory.

-cm { sql | dbinit }

Display in the server messages window the CREATE DATABASE or dbinit command to create a database that is the same as the one being unloaded. If -an is also specified, the command that is displayed is the command to create the new database.

  • sql   Displays the CREATE DATABASE statement that is written to the reload.sql file.

  • dbinit   Displays the Initialization utility (dbinit) command.

When displaying the statement or command for an existing strongly-encrypted database (-an is not specified) the encryption key cannot be obtained from the database, so a question mark (?) appears in the ENCRYPTED clause or -ek option.

The creation command or statement is not displayed if you unload a database that was created with a version 10 or earlier database server.
-cp Compress the table data output files by appending the COMPRESSED keyword to the UNLOAD TABLE statements it executes. This option has no effect when specified with -an or -ar.
-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.
-dc

Force all computed columns in the database to be recalculated. By default, computed column values are not recalculated. When the -dc option is specified, a new section is added to the reload.sql script to recompute computed columns. Statements of the following form are added.

ALTER TABLE "owner"."table-name"
ALTER "computed-column" SET COMPUTE (compute-expression);

If your tables contain context-sensitive computed values, such as CURRENT DATE, it is recommended that you use the ALTER TABLE statement to recalculate computed column values instead of using the -dc option. See ALTER TABLE statement.

-e table, ...

Exclude the specified tables from the reload.sql file. Table names are always case insensitive, even in case sensitive databases.

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. If a table has foreign keys referring to it, the database cannot be rebuilt without the contents of the table.

It is recommended that you only use the -e option with the -d option to unload data for all tables except those identified by -e.

-ea algorithm

Specify the encryption algorithm used for database or table encryption (-et). Specify -ea simple for simple encryption (do not specify -ek or -ep). Simple encryption is equivalent to obfuscation and is intended only to keep data hidden in the event of casual direct access of the database file, to make it more difficult for someone to decipher the data in your database using a disk utility to look at the file.

For greater security, specify AES or AES256 for 128-bit or 256-bit strong encryption, respectively. Specify AES_FIPS or AES256_FIPS for 128-bit or 256-bit FIPS-approved encryption, respectively. For strong encryption, you must also specify the -ek or -ep option. For more information about strong encryption, see Strong encryption.

To create a database that is not encrypted, specify -ea none, or do not include the -ea option (and do not specify -e, -et, -ep, or -ek).

If you do not specify the -ea option, the default behavior is as follows:

  • -ea none, if -ek, -ep, or -et is not specified
  • -ea AES, if -ek or -ep is specified (with or without -et)
  • -ea simple, if -et is used without -ek or -ep

Algorithm names are case insensitive.

Composants nécessaires sous licence distincte

Les cryptages ECC et certifié FIPS exigent une licence distincte. Toutes les technologies de cryptage fort sont soumises à la réglementation en matière d'exportation.

Pour plus d'informations, reportez-vous à la section Separately licensed components.

-ek key

Specify an encryption key in the dbunload command 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 the algorithm specified by the -ea option. If you specify the -ek option without specifying -ea, the AES algorithm is used. See Strong encryption.

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.

-ep

Prompt for 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. See Strong encryption.

-er

Remove encryption from encrypted tables during an unload procedure.

When rebuilding a database that has table encryption enabled, you must specify either -er or -et to indicate whether the new database has table encryption enabled, otherwise you get an error when attempting to load the data into the new database.

The following command unloads a database (mydata.db) that has encrypted tables, into a new database (mydatacopy.db) that does not have table encryption enabled, removing encryption from any encrypted tables:

dbunload -an c:\mydatacopy.db -er 
         -c "UID=DBA;PWD=sql;
         DBF=c:\mydata.db;
         DBKEY=29bN8cj1z"
-et

Enable database table encryption in the new database (-an or -ar must also be specified). If you specify the -et option without the -ea option, the AES algorithm is used. If you specify the -et option, you must also specify -ep or -ek. You can change the table encryption settings for the new database to be different than those of the database you are unloading.

When rebuilding a database that has table encryption enabled, you must specify either -er or -et to indicate whether the new database has table encryption enabled, otherwise you get an error when attempting to load the data into the new database.

The following example unloads a database (mydata.db) that has tables encrypted with the simple encryption algorithm, into a new database (mydatacopy.db) that has table encryption enabled, and uses AES_FIPS encryption with the key 34jh:

dbunload -an c:\mydatacopy.db -et -ea AES_FIPS
         -ek 34jh
         -c "UID=DBA;PWD=sql;DBF=c:\mydata.db"
-g

Materialized views   By default, materialized views defined as MANUAL REFRESH are not initialized after a reload. If you want these materialized views to be initialized as part of the reload process, specify the -g option. Specifying -g causes the database server to execute the sa_refresh_materialized_views system procedure. See sa_refresh_materialized_views system procedure.

When deciding whether to use the -g option, consider that initializing all materialized views may cause the reload process to take significantly longer to complete. On the other hand, not using the -g option means that the first query that attempts to use an uninitialized materialized view must wait while the database server initializes the view, which may cause an unexpected delay. If you do not use the -g option, you can also manually initialize materialized views after the reload completes. See Initialize materialized views.

Text indexes   By default, text indexes defined as MANUAL REFRESH are not initialized after a reload. If you want the text indexes initialized as part of the reload process, specify the -g option. Specifying -g causes the database server to execute the sa_refresh_text_indexes system procedure. See sa_refresh_text_indexes system procedure.

-ii Use 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.
-ix Use 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.
-k

Populate the sa_diagnostic_auxiliary_catalog table. This table maps database object IDs for tables, users, procedures, and so on, from the source database to the tracing database. It also causes all histograms to be unloaded/reloaded. This option is used when creating a tracing database, that is, a database that receives diagnostic tracing information. The sa_diagnostic_auxiliary_catalog table allows the server to simulate conditions that were present when tracing data was captured (for example, for use with Index Consultant, or application profiling). This option is most useful when specified with the -n option. See Advanced application profiling using diagnostic tracing and sa_diagnostic_auxiliary_catalog table.

-l

Force the current value of SYSTABCOL.max_identity to be preserved across a database rebuild.

By default, when a database containing tables with autoincrement columns is rebuilt, the database server calculates the next available value for each autoincrement column based on the current contents of the tables. In most cases, this is enough; however, if rows have been deleted from the end of the range of values, values can be reused, which is not desirable in some cases.

Specifying the -l option adds calls to the sa_reset_identity system procedure to the generated reload.sql script for each table that contains an autoincrement value, preserving the current value of SYSTABCOL.max_identity.

See also:

-m Do not preserve user IDs for databases involved in replication.
-n Do not unload database data; reload.sql contains SQL statements to build the structure of the database only. If you want the reload.sql file to contain LOAD TABLE or INPUT statements, use -nl instead.
-nl Unload the structure (the same behavior as the -n option), but the resulting reload.sql file also includes LOAD TABLE or INPUT statements for each table. No user data is unloaded when this option is used. When you specify -nl, you must also include a data directory so that the LOAD/INPUT statements can be generated, even though no files are written to the directory. This option allows you to generate a reload script without unloading data. You can unload the data by specifying -d. If a database contains a table whose data should not be unloaded, unloading the data for that table can be skipped using dbunload -d -e table-name.
-no

Unload the database objects ordered by name. By default, dbunload generates objects in the order they were created. Specifying the -no option may be useful for comparing database schemas when the databases contain the same objects, but the creation order was different.

Object definitions are grouped by object type in alphabetical order in the reload.sql file if -no is specified:

  • users
  • group memberships
  • tables
  • indexes and foreign keys
  • views
  • procedures
  • functions
  • triggers
  • events
  • web services

The object definitions are output in owner,name order. In some cases a third element is included in the ordering (for example, foreign key role name, trigger name).

The -no option cannot be used with the -n, -nl, -ar, -an, or -ac option. To simplify comparisons, it is recommended that you use -no option when comparing the reload scripts for databases that were created using the same version of the database server because of minor differences in the object definitions.

Caution

The generated file should not be used to create a new database because in some cases the object creation order can be important. For example, if procedure p2 calls procedure p1 and p1 returns a result set, it may be important to define p1 before p2. Attempting to execute a reload.sql script generated with -no option results in an error.

-o filename Write output messages to the named file. The location of this file is relative to dbunload.
-p char Replace the default escape character (\) for external unloads (dbunload -x option) with another character. This option is available only when you run this utility from a command prompt.
-q Run in quiet mode—do not display messages or windows. 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 if reload.sql already exists.
-qc Close the messages window once the unload completes. By default, the dbunload messages window remains open until a user closes it. This option is only available on Windows Mobile.
-r reload-file Modify the name and directory of the generated reload 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.
-t table,...

Specify 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. Table names are always case insensitive, even in case sensitive databases.

A reload.sql file created with the -t option should not be used to rebuild a database because the file will not include all the database tables. If a table has foreign keys referring to it, the database cannot be rebuilt without the contents of the table.

It is recommended that you only use the -t option with the -d option to unload data for the tables identified by -t.

-u 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. Normally, the data in each table is ordered by the primary key or clustered index if one is defined for the table.
-v Display the name of the table being unloaded, and the number of rows that have been unloaded. This option is available only when you run dbunload from a command prompt.
-xi Perform an external unload by unloading data to the dbunload client, and then using the LOAD statement in the generated reload command file, reload.sql, to repopulate the database with data.
-xx Perform an external unload by unloading data to the dbunload client, and then using the Interactive SQL INPUT statement in the generated reload command file, reload.sql, to repopulate the database with data.
-y

Replace existing command files without prompting for confirmation. If you specify -q, you must also specify -y or the unload will fail if dbunload detects that a command file already exists.

There are special considerations for unloading databases involved in replication. See Extracting remote databases and Upgrading SQL Remote.

directory Specifies the directory where the unloaded data is to be placed. The reload.sql command file is always relative to the current directory of the user.
Remarks
Upgrading to version 11

For information about rebuilding an existing database into a version 11 database, see Upgrading SQL Anywhere.

When using dbunload with a version 10.0.0 or later database, the version of dbunload used must match the version of the database server used to access the database. If an older version of dbunload is used with a newer database server, or vice versa, an error is reported.

With the Unload utility, you can unload a database and put a set of data files in a named directory. The Unload utility creates an Interactive SQL command file to rebuild your database. It also unloads all the data in each of your tables into files in the specified directory, in comma-delimited format. Binary data is properly represented with escape sequences.

An internal unload/reload will unload information about the current status of each user by issuing UPDATE ISYSUSER statements. An external unload/reload does not include this information and the status of all users is reset. See Managing login policies overview.

When you rebuild a database by unloading and reloading it, the rebuilt database may be smaller than the original database. This decrease in database size may be the result of indexing changes in SQL Anywhere, and does not indicate a problem or a loss of data.

Note

Version 9 and earlier databases that require recovery cannot be reloaded with version 10 or later of the Unload utility (dbunload). You must reload the database with version 9 or earlier of dbunload.

You can also use the Unload utility to directly create a new database from an existing one. This avoids potential security problems with the database contents being written to ordinary disk files.

If you only want to unload table data, you can do so in one step using the Unload Data window in Sybase Central.

For more information, see Export data with the Unload Data window.

There are special considerations for unloading databases involved in replication. See Extracting remote databases.

You can access the Unload utility in the following ways:

  • From Sybase Central, using the Unload Database Wizard. See Export data with the Unload Database Wizard.

  • At a command prompt, using the dbunload command. This is useful for incorporation into batch or command files.

The Unload utility should be run by a user ID with DBA authority. This is the only way you can be sure of having the necessary privileges to unload all the data. In addition, the reload.sql file should be run by a user with DBA authority. (Usually, it is run on a new database where the only user ID is DBA with password sql.)

The database server -gl option controls the permissions required to unload data from the database. See -gl server option.

The dbo user ID owns a set of system objects in a database, including views and stored procedures.

The Unload utility does not unload the objects that were created for the dbo user ID during database creation. Changes made to these objects, such as redefining a system procedure, are lost when the database is unloaded. Any objects that were created by the dbo user ID since the initialization of the database are unloaded by the Unload utility, and so these objects are preserved.

When you unload a database, changes to permissions on system objects are not unloaded. You must grant or revoke these permissions in the new database.

Tip

Before rebuilding your database, it is recommended that you validate the reload process by reloading the database without any data, by running a command similar to the following:

dbunload -n -an new.db -c "UID=your-user-id;PWD=your-password;DBF=original-database-file"

You should fix any problems that are identified in the original database before rebuilding it.

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.

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

For more information about supplying a file name and path in this mode, see UNLOAD statement.

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.

The LOAD TABLE statements generated by dbunload turn off check constraints and computed columns.

Exit codes are 0 (success) or non-zero (failure). See Software component exit codes.

Databases with materialized views

It is recommended that you refresh the materialized views in your database after rebuilding the database. See Refresh manual views.

Databases running diagnostic tracing

Tracing information is not unloaded as part of a database unload or reload operation. If you want to transfer tracing information from one database to another, you must do so manually by copying the contents of the sa_diagnostic_* tables; however, this is not recommended.

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. See UNLOAD statement.

When you use an external unload and reload to unload, reload, or rebuild a database and the character set of the database is incompatible with the character set of the host system on which dbunload is running, character set conversion may cause data to be corrupted as it is converted between the database character set and the host system's character set.

To avoid this problem, specify the database character set in the connection string for the database (-c and -ac options). For example, if the database character set is UTF-8, you should include "charset=utf-8" in the connection strings:

dbunload -c UID=user-ID;PWD=password;
CHARSET=utf-8;DBF=filename -ac UID=user-ID;
PWD=password;CHARSET=utf-8;ENG=server-name -xx

When you perform an external unload, the beginning of the reload.sql includes a commented CREATE DATABASE statement. This statement can be used to create a database that is equivalent to the one being unloaded.

If the unloaded database was created with version 9 or earlier of SQL Anywhere and had a custom collation, the COLLATION clause appears as follows:

COLLATION collation-label DEFINITION collation-definition

where collation-definition is a string that specifies the custom collation.

The only way to preserve a custom collation is to rebuild the database in a single step (internal unload). If you choose to unload the database, and then load the schema and data into a database that you create, then you must use one of the supplied collations.

If the unloaded database was created with strong encryption, the value of the KEY clause in the CREATE DATABASE statement appears as three question marks (???).

Failed unloads

If a failure occurs during an internal rebuild of a database using -ar or -an, after the table data has been reloaded and any indexes on the table have been rebuilt, dbunload creates a file named unprocessed.sql in the current directory. This file contains all the statements that were not executed as a result of the failure, and also includes the statement that caused the failure as a comment. The following is an example of an unprocessed.sql file:

-- The database reload failed with the following error:
-- ***** SQL error: the-SQL-ERROR 
-- This script contains the statements that were not executed as a
-- result of the failure. The statement that caused the failure is
-- commented out below. To complete the reload, correct the failing
-- statement, remove the surrounding comments and execute this script. 
/*
the failing statement 
go

*/

setuser "DBA" 
go

... the remainder of the statements to be processed

Having this file gives you the opportunity to correct, remove, or alter the failing statement(s). The unprocessed.sql file is only created after all the table data and referential integrity constraints have been reloaded. Using Interactive SQL, you can connect to the new database and execute the updated unprocessed.sql file. This allows you to complete the rebuild of the database without having to start the rebuild over again, which can save considerable time.

When the unprocessed.sql file is generated, dbunload stops and returns a failed error code to make other tools or scripts aware of the failed rebuild.

Encrypted databases

When you rebuild a database that has table encryption enabled, you must specify either -er or -et to indicate whether the new database has table encryption enabled, otherwise you get an error when attempting to load the data into the new database.

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=dbeng11 -ep"

If you are using the -an option 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, rather than 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. The dbunload -ek and -ep options apply to a new database, while the database server (dbeng11/dbsrv11) options and DBKEY= apply to existing databases.

  • When rebuilding databases involved in synchronization or replication, dbunload assumes that the encryption key specified with the -ek or -ep option is the encryption key of the original database, and the encryption key of the newly-rebuilt database.

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

Rebuilding a database

To unload a database, first ensure that the database is not already running. Then, run dbunload, specifying a DBA user and password, and referencing the database with the DBF= connection parameter.

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

To combine the unload and reload steps, follow the directions for unloading above, but add the -an option to specify the name of the new database file. See the descriptions of the -ac and -an options.