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.
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 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 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.
Notes:
• 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.
The unloaddb command has the following format:
unloaddb dbname|vnode::dbname[/server_class] [+user=[authuser]] [-uusername]
[-charset=characterset [-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] [filesize=size] [-synonymonly]
[-with_comments]
dbname
+user=[authuser]
-uusername
-charset=characterset
Creates a printable data file. The option -charset=UTF8 is recommended for transporting databases between computer systems whose internal representations of non-ASCII data differ or where character sets may differ. Use of this option potentially engages the transliteration support in the COPY function. Unlike the -c option, -charset can be used with -with_csv and -with_ssv.
-c
Creates printable UTF8 encoded 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
-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.
-nologging
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.
-parallel
Creates indexes using the parallel index creation syntax (to build multiple indexes concurrently).
-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.
-no_seq
Does not print lines 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.
-on_logfull=commit|abort|notify
Adds a SET SESSION WITH ON_LOGFULL=value statement to the generated reload file.
-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 UTF8 portable data files.
-with_csv
Generates a comma-separated data file.
-with_ssv
Generates a semicolon-separated data file.
-no_rep
Does not write Ingres Replicator objects (tables, indexes, events, procedures) of a replicated database to the unload file.
‑nvarchar
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.
For more information, see the Database Administrator Guide.
-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.
-synonymonly
Unloads synonyms for the user.
-with_comments
Prints statements only related to comments.