ASA Database Administration Guide
Database Administration Utilities
The Unload utility
Unloading a database using the dbunload 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 the initialization options, such as page size, or collation. If you are changing collations, the -xx switch 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.
You do not need to specify a directory for this option.
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.
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
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.
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 the command prompt. If you wish 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 to encrypt your new database with. You can choose either AES or MDSR. 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.
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. If you specify the -ek option without specifying an algorithm (using the -ea option), the algorithm used will be AES. If you specify -ek without specifying -an, the -ek option is ignored.
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 the -ep option without specifying an algorithm (-ea option), the algorithm used will be AES. 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.
Internal versus external unloads and reloadsThe 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 statement). 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.
Repeated unload of view creation statements (-j) If your database contains view definitions that are dependent on each other, you can use this option to avoid failure when reloading the views into a database. This option causes view creation statements to be unloaded multiple times, as specified by the count entered. This count should be small, and should correspond to the number of levels of view dependency.
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 the command prompt.
Operate quietly (-q) Do not display output messages. This option is available only when you run this utility from the command prompt.
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 the 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.
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.
In Windows 95/98/Me and NT/2000/XP, and UNIX, there is a file (rebuild.bat, rebuild.cmd, or rebuild) that automates the unload and reload process.