unloaddb Command--Create Files for Unloading and Reloading a Database

The unloaddb command creates command files that the DBA uses to unload the data from a database and reload the data into a new, empty database.

Use unloaddb when a database must be totally rebuilt, or for checkpointing the database. The unloaddb command unloads all objects in the database, including tables, views, and permissions.

• Unload file—contains commands to read sequentially through the database, copying every user table into its own file in the named directory.

• Reload file—contains commands to load a new, empty database with the information contained in the files created by the unload file.

The DBA must execute these files to accomplish the unloading and reloading of the database. It is important that the database be recreated with the reload file before doing any work (for example, creating tables, forms, and reports) in the new database.

The unloaddb command uses a version of the copydb command to generate the copy commands in the unload and reload files. Consequently, all limitations of the copydb command apply to the unloaddb command.

unloaddb dbname|vnode::dbname [+user=[authuser]] [-uusername]
[-c] [-ddirname] [-source=dirname] [-dest=dirname]
[-P] [-Ggroupid] [-group_tab_idx] [-parallel]
[-journal] [-with_sequences] [-no_seq] [-no_systabs] [‑with_blankline]
[‑with_csv | ‑with_ssv] [‑on_logfull=commit|abort|notify] [‑nvarchar]
[-add_drop] [-relpath] [‑compress] [filesize=size]

Specifies the name of the database, and if required, the vnode , as described in Standard Flags and Parameters

Specifies or prompts for the user name and password used for connection authentication, as described in Standard Flags and Parameters.

Specifies the effective user for the session, as described in Standard Flags and Parameters

Creates printable data files, which is useful for transporting databases between computer systems whose internal representations of non-ASCII data differ.

Unloaddb cannot create printable files if (1) binary data is stored in varchar columns, or (2) tables contain user-maintained logical keys.

For nchar, nvarchar, or long nvarchar columns or when the installation character set is UTF8, the data files generated are in UTF-8 encoding.

The UTF-8 encoded data files containing data from char, varchar, or long varchar columns can be reloaded only into installations installed with the UTF8 character set.

Stores the unload and reload files in the location specified by dirname instead of the default current directory. The specification can be either a full or relative directory specification.

The dirname must not be the actual database directory, because the files created by unloaddb may have the same names as the tables in the database. The actual database directory is: $II_DATABASE/ingres/data/default/dbname.

Specifies the source directory from which the database will be reloaded. An empty dirname specification ("") denotes the current directory. The -source specification overrides a -d specification for the reload file.

If a source is specified without a destination (no -d or -dest), then the default unload directory is used.

The source directory specification is not checked for validity or existence. This allows the scripts to be moved to another machine for reloading.

Specifies the destination directory into which the database will be unloaded. An empty dirname specification (“.”) denotes the current directory. The -dest specification overrides a -d specification for the unload file.

If a destination is specified without a source (no -source) then the default reload directory is used.

The destination directory specification is not checked for validity or existence. This allows the scripts to be moved to another machine for unloading.

Specifies a group identifier, as described in Standard Flags and Parameters.

Replaces the SET NOJOURNALING statement in the unload scripts with the SET JOURNALING statement, and disables specifying the WITH NOJOURNALING option on each CREATE TABLE statement in the unload script.

Unloads/reloads all user objects, but ignores all the system objects. This is useful if there are many different schemas in one database.

Generates redundant blank lines after every row in each data file generated. By default, redundant blank lines are not generated. This option applies only when using the –c flag to generate ASCII data files.

Builds indexes in the command file immediately after the respective table creation. Without this flag, all indexes are created for all tables toward the end of the command file. The usermod command uses this flag to limit the loss of non-persistent indexes if it encounters a failure.

Adds a SET SESSION WITH ON_LOGFULL=value statement to the generated reload file.

Exports strings in UTF-8 encoding so that the data files are portable. On CREATE TABLE statements, column data types char and varchar are changed to nchar and nvarchar, respectively. The ‑nvarchar flag changes the default copy specification for character types from varchar to nvarchar. You do not need to specify the ‑c flag.

Note: The generated copy.in script cannot be used to reload data into a non-Unicode database.

Writes a DROP statement also, before writing the CREATE statements. This is useful when the scripts are run repeatedly in case of errors, and tables are already created.

Removes the paths from the file names; the files will thus be created and copied from the current directory.

Splits the output file into multiple parts, where size is the number of megabytes per part.

cd /mydir/backup
unloaddb empdata
unload.ing
destroydb empdata
createdb empdata
reload.ing
sysmod empdata

2. Unload the empdata database, specifying separate source and destination directories:

3. Unload the empdata database from the $HOME directory, specifying the current directory as the source and destination directories: