Reference Manual
   CHAPTER 4. Database Administration Utilities     

The Unload utility

With the Unload utility, you can unload a database and put a set of data files in a named directory. The Unload utility creates the Interactive SQL command file reload.sql to rebuild your database from scratch. It also unloads all of 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.

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

Accessing the Unload utility 

You can access the Unload utility in the following ways:

• From Sybase Central, for interactive use under Windows 95 or NT.

• From the system command line, using the dbunload command-line utility. This is useful for incorporating into batch or command files.

The Unload utility should be run from 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 from the DBA user ID. (Usually, it will be run on a new database where the only user ID is DBA with password SQL.)

Objects owned by dbo 

The dbo user ID owns a set of Adaptive Server Enterprise-compatible system objects in a database.

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

Unloading and replication 

There are special considerations for unloading databases involved in replication.

     For information, see Unloading and reloading a consolidated database.

  Unloading a database from Sybase Central

  To unload a running database:

1. Connect to the database.

2. Right-click the database and click Unload in the popup menu. The Unload Database wizard is displayed.

3. Follow the instructions in the wizard.

  To unload a database file or a running database:

1. Open the Database Utilities folder in the left panel.

2. Double-click Unload Database in the right panel. The Unload Database Wizard is displayed.

3. Follow the instructions in the Wizard.

     For full information on unloading a database from Sybase Central, see the Sybase Central online Help.

  The DBUNLOAD command-line utility

Syntax 

dbunload [ switches ] [ directory ]

Windows 3.x
syntax 

dbunloaw [ switches ] [ directory ]

Switch	Description
-ac "keyword=value; ..."	Connect to the database specified in the connect string to do the reload.
-an database	Creates a database file with the same settings as the database being unloaded, and automatically reloads it.
-c "keyword=value; ..."	Supply database connection parameters
-d	Unload data only
-e list	No data output for listed tables
-ii	Internal unload, internal reload (default)
-ix	Internal unload, external reload
-j nnn	Repeated unload of view creation statements
-n	No data—schema definition only
-o file	Log output messages to file
-p char	Escape character for external unloads (default "\")
-q	Quiet mode—no windows or messages
-r reload-file	Specify name and directory of generated reload Interactive SQL command file (default reload.sql)
-t list	Output only the listed tables
-u	Unordered data
-v	Verbose messages
-xi	External unload (when client and server are on different machines), internal reload
-xx	External unload (when client and server are on different machines), external reload
-y	Replace the command file without confirmation
directory	The directory where the unloaded data is to be placed. Not needed for -an or -ac.

Description 

In the default mode, 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 the -x switch is used, the directory is relative to the current directory of the user. The reload.sql command file is always relative to the current directory of the user, regardless of whether -x is used.

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

     For more information about the command-line switches, see Unload utility options.

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

  Unload utility options

Reload the data to an existing database (-ac)     You can combine the operation of unloading a database and reloading the results into an existing database using this option.

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 on the command line. This provides greater security for your data, but at some cost for performance.

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.

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 on the command line. This provides greater security for your data, but at some cost for performance.

Connection parameters ( -c )     For a description of the connection parameters, see Connection parameters. If the connection parameters are not specified, connection paremeters from the SQLCONNECT environment variable are used, if set. 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 is accessible only from the command-line utility. By default, the optional table-list defines the tables to be unloaded. If you wish to unload almost all of the tables in the database, the -e command-line switch unloads all tables except the specified tables.

Use internal unload, internal reload ( -ii )     Accessible only from the command-line utility, this switch 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 )     Accessible only from the command-line utility, this switch 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 a file (-o)     Logs all output messages to a file.

Escape character ( -p )     The default escape character (\) for external unloads (DBUNLOAD -x switch) can be replaced by another character, using this option. This option is available only from the command-line utility.

Operate quietly ( -q )     Display no messages except errors. This option is available only from the command-line utility.

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.

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 the data unordered ( -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, is displayed. This option is available only from the command-line utility.

Use external unloading, internal reload ( -xi )     Accessible only from the command-line utility, this switch uses the Interactive SQL OUTPUT statement to extract data from the database, 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 )     Accessible only from the command-line utility, this switch uses the Interactive SQL OUTPUT statement to extract data from the database, and uses the Interactive SQL INPUT statement in the generated reload command file, reload.sql, to repopulate the database with data.

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

Operate without confirming actions ( -y )     Without this option, you are prompted to confirm the replacement of an existing command file.

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.

In Windows 95 and NT, and UNIX, there is a file (rebuild.bat, rebuild.cmd, or rebuild) that automates the unload and reload process.