User Guide : A. Command Reference : unloaddb Command--Create Files for Unloading and Reloading a Database
 
Share this page                  
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.
Two command files are created:
Unload file—contains commands to read sequentially through the database, copying every user table into its own file in the named directory.
Reload filecontains commands to load a new, empty database with the information contained in the files created by the unload file.
On Linux, the file names are unload.ing and reload.ing.
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.
The unloaddb command has the following format:
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]
dbname
Specifies the name of the database, and if required, the vnode , as described in Standard Flags and Parameters
+user=[authuser]
Specifies or prompts for the user name and password used for connection authentication, as described in Standard Flags and Parameters.
-uusername
Specifies the effective user for the session, as described in Standard Flags and Parameters
-c
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.
-ddirname
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.
-source=dirname
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.
-dest=dirname
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.
-P
Prompts for password if the session requires a password.
-Ggroupid
Specifies a group identifier, as described in Standard Flags and Parameters.
-journal
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.
-with_sequences
Print statements related to sequences only.
-no_seq
Does not print statements related to sequences.
-no_systabs
Unloads/reloads all user objects, but ignores all the system objects. This is useful if there are many different schemas in one database.
-with_blankline
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.
-with_csv
Generates a comma-separated data file.
-with_ssv
Generates a semicolon-separated data file.
-group_tab_idx
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.
-on_logfull=commit|abort|notify
Adds a SET SESSION WITH ON_LOGFULL=value statement to the generated reload file.
‑nvarchar
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.
-add_drop
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.
-relpath
Removes the paths from the file names; the files will thus be created and copied from the current directory.
-compress
Includes WITH COMPRESSION clause in the generated files.
filesize=size
Splits the output file into multiple parts, where size is the number of megabytes per part.
unloaddb Examples
1. Unload and reload the empdata database:
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:
unloaddb empdata -source="misc/loaddir/" -dest="misc/dumpdir"
Copy statements in the reload script would have the form:
copy emps () from 'misc/loaddir/emps.bob'
Copy statements in the unload script would have the form:
copy emps () into 'misc/dumpdir/emps.bob'
3. Unload the empdata database from the $HOME directory, specifying the current directory as the source and destination directories:
unloaddb empdata -source="" -dest=""
Copy statements in the reload script would have the form:
copy emps () from 'emps.bob'
Copy statements in the unload script would have the form:
copy emps () into 'emps.bob'