unloaddb Command--Create Command 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, integrity constraints, permissions, forms, graphs, and report definitions.

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

On Windows the file names are unload.bat and reload.bat. On UNIX, 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.

• If overflow occurs, you may need to edit the unload and reload files to specify another flag, for example, N instead of F in the default floating point specification.

• To optimize performance, run the sysmod and optimizedb commands after recreating the database.

• When unloaddb is run from an Ingres 9.1 or later installation against an older version of Ingres, the command file generated will contain the data type INGRESDATE or ANSIDATE instead of DATE for any date columns in CREATE TABLE statements.

For additional information on unloading a database, see the Database Administrator Guide.

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

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

Specifies 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. (On VMS, the directory is: II_DATABASE:[INGRES.DATA.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.

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.

Writes a SET NOLOGGING statement to the unload file to bypass the logging and recovery system. Use with caution. For more information see SET NOLOGGING in the SQL Reference Guide.

Creates indexes using the parallel index creation syntax (to build multiple indexes concurrently).

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.

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

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.

Does not write Ingres Replicator objects (tables, indexes, events, procedures) of a replicated database to the unload file.

Exports strings in UTF-8 encoding so that the data files are portable. On CREATE TABLE statements, column data types char, varchar, and long varchar are changed to nchar, nvarchar, and long 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.