Unload utility (dbunload)

dbunload [ options ] [ directory ]

Option Description

@data

Reads in options from the specified environment variable or configuration file. See 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; ..."

Connects to an existing database and reloads 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 dbinit 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 new database must have the same file name as the original database, then use the DBN connection parameter to specify the correct database names. See DatabaseName (DBN) connection parameter.

The following command (entered all on one line) loads a copy of the old database into a new database that has the same file name mydata.db.

dbunload -c "UID=DBA;PWD=sql;DBN=old;DBF=c:\old_directory\mydata.db"
         -ac "UID=DBA;PWD=sql;DBN=new;DBF=c:\new_directory\mydata.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;Server=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

Combines 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 (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 [ k ] Sets 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. Use k to specify units of kilobytes (for example, -ap 4k). 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 dbeng12/dbsrv12 server option.

-ar [ directory ]

Creates a new database with the same settings as the old database, reloads it, and replaces 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. The transaction log management is handled only if the database is used in replication: if there is no SQL Remote publisher, then the old transaction log is not needed and is deleted instead of being copied to the specified directory. See Backups for 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; ..."

Specifies the connection parameters for the source database. 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=%SQLANYSAMP12%\demo.db;UID=DBA;PWD=sql" c:\unload

-cm { sql | dbinit }

Displays 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 dbinit utility 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 Compresses 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 Does not generate any of the database definition statements (CREATE TABLE, CREATE INDEX, and so on); reload.sql contains statements to reload the data only.

-dc

Forces 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, ...

Excludes 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

Specifies 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-certified 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.

Note

Separately licensed component required.

ECC encryption and FIPS-certified encryption require a separate license. All strong encryption technologies are subject to export regulations.

See Separately licensed components.

-ek key

Specifies 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

Prompts 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

Removes 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

Enables 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 call 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. However, 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 Initializing a materialized view.
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 call the sa_refresh_text_indexes system procedure. See sa_refresh_text_indexes system procedure.

-ii 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.

-ix 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.

-k

Populates 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. Additionally, this option ensures that auditing is disabled on the tracing database, even if auditing is enabled on the production database. 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 Diagnostic tracing and sa_diagnostic_auxiliary_catalog table.

-kd

Reloads the database into a single dbspace file. This option is useful for creating a tracing database if the computer where the tracing database is being created does not have the same directory structure as the production database. See Creating an external tracing database (Sybase Central).

-l

Forces 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. Usually 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.

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.

Caution

The generated file should not be used to create a new database because 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 run a reload.sql script file generated with -no option results in an error.

-o filename Writes output messages to the named file. The location of this file is relative to dbunload.

-p char Replaces the default escape character (\) for external unloads (dbunload -x option) with another character. This option is available only when you run this utility at a command prompt.

-q Runs in quiet mode—messages are not displayed. This option is available only when you run this utility at a command prompt. If you specify -q, you must also specify -y or the unload will fail if reload.sql already exists.

-qc Closes 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.

-qr Prevents progress messages from being created and displayed when loading tables and creating indexes.

-r reload-file Modifies the name and directory of the generated reload SQL script 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,...

Specifies 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 Prevents an index from being used to order data. 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 Displays 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 at a command prompt.

-xi Performs an external unload by unloading data to the dbunload client, and then using the LOAD statement in the generated reload script file, reload.sql, to repopulate the database with data.

-xx Performs an external unload by unloading data to the dbunload client, and then using the Interactive SQL INPUT statement in the generated reload SQL script file, reload.sql, to repopulate the database with data.

-y

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

There are special considerations for unloading databases involved in replication. See Remote database extraction and SQL Remote upgrades.

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

Note

For information about rebuilding an existing database into a version 12 database, see SQL Anywhere Server upgrades.

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 script 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 unloads 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 Login policies.

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.

If you rebuild a version 11 or earlier database using the Unload utility (dbunload), the new database has global checksums enabled by default, even if the original database had checksums turned off. Although it is not recommended, you can disable global checksums for a database using the ALTER DATABASE statement. See ALTER DATABASE statement and Corruption detection using checksums.

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 Exporting data with the Unload Data window.

There are special considerations for unloading databases involved in replication. See Remote database extraction.

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 dbeng12/dbsrv12 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.

Note

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

Note

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.

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 statements (UNLOAD/LOAD) versus external commands (Interactive SQL INPUT and OUTPUT statements). However, internal statements 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;Host=host-name -xx

When you perform an external unload, the beginning of 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 (???).

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 run 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.

If a failure occurs during an internal rebuild of a database using -ar, the new database and transaction log file have the .dbr and .logr file extensions, respectively. Use the following steps to apply the unprocessed.sql file and finish the reload manually:

Start the new database.
Apply the updated unprocessed.sql file.
Shut down the database.
Move original-name.db and original-name.log to a new directory.
Rename the original-name.dbr and original-name.logr files to original-name.db and original-name.log respectively.

Run the following command:

dblog -t original-name.log original-name.db

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=myenc.db;START=dbeng12 -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 (dbeng12/dbsrv12) 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 dbeng12/dbsrv12 server option and DatabaseKey (DBKEY) connection parameter.

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 script 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.