Informix has two logical backup commands: onunload and dbexport. onunload is much faster than dbexport, but it has many more constraints placed on it than dbexport has. There are advantages and disadvantages to each utility.
onunload exports, or unloads, a database or table into a binary output file, consisting of a logical backup of a database or table. The resulting file can be used to import data into another database using the onload utility. onunload is faster because it copies the data in page mode, instead of having to convert the data to ASCII format as dbexport does. However, since the files are binary, only the same version of onload can read an onunload backup. This utility typically is used for moving data between computers but cannot be used for moving data between databases that are not the same version. onunload locks the database in shared mode while it is running. An error is returned if it cannot obtain a shared lock.
dbexport unloads a database into text files and creates a schema file. The resulting files can be used to import data into another database using the dbimport utility. Since the files are ASCII format, they can be moved between databases running different versions of Informix. The dbexport command is not part of OnLine XPS and Informix Dynamic Server with Advanced Decision Support and Extended Parallel Options, but onunload is. It is much slower than onunload since it has to convert the data to ASCII format. It also can back up only a database, whereas onunload can back up a database or table. dbexport locks the database in exclusive mode while it is running. An error is returned if it cannot obtain an exclusive lock.
Tip
I cover only onunload and onload in this chapter for three reasons: One is that these programs require only a shared lock, where dbexport requires an exclusive lock. Another is that onunload feels more like a backup and recovery tool, whereas dbexport ’s emphasis seems to be moving data between different databases. The final reason is that I can’t cover everything in a book of this size. onunload and dbexport use the same method of determining what device to use, as well as its block size and tape size, so that portion of this section may be useful to you if you plan on using dbexport.
Since the export created by onunload is actually a binary file, there are constraints on how that file may be used:
The Informix instance receiving the data must use the same page size and the same representation of numeric data as the instance that performed the onunload.
The Informix instance must be running the same version of the onunload/onload utilities.
When unloading and loading a table, onunload does not preserve access privelege, synonyms, views, constraints, triggers, or default values. You must use dbschema to transfer this information. (This information is preserved if you unload and load an entire database.)
You must have DBA privileges or be user informix to unload a database.
You must be the owner of a table, have DBA privileges, or be user
informixto unload a table.The logging mode of the database is not preserved.
The onunload utility can write the exported data to a file on a hard drive or to a tape device. It can unload an entire database or just a table within that database. The syntax of the command is as follows:
$onunload[-l|-bblocksize-stapesize-tdevice]database[[:owner].table]
There are two methods of specifying the device parameters for
onunload. The first is to use the
-l option, which specifies that
onunload should read the
device,
tapesize, and
blocksize values from the
onconfig file. It will use
LTAPEDEV, LTAPESIZE, and
LTAPEBLK values, respectively.
Warning
Please note that LTAPEDEV is the same device that
you are using for your logical log backups. That means that if you do
not specify a tape device, you could accidentally overwrite your
logical backup tape! It is therefore safer to specify another device
if you can. If you cannot, make sure you take your logical backup
tape out of the drive before running this command.
The second method for specifying device parameters is to specify each
of them individually using the -b
blocksize, -s
tapesize, and -t
device options.
At a minimum, you must specify the name of the database to unload. To do this, simply name the database as the last argument of the onunload command. Optionally, you can specify a name of a table and the owner of that table. To do this, add the name of the table and its owner after the name of the database using this format:
database:owner.table
onload is onunload ’s counterpart and will read the logical backup created by onunload. It will create a database or table in the specified dbspace and load the data from the logical backup into that database or table. If you do not specify a dbspace to load the database or table into, it will be loaded into the root dbspace.
As with onunload, you can specify the device
parameters for onload by using the
-b
blocksize,
-s
tapesize, and
-t
device options. You
also can use the -l option, which tells
onload to read the device parameter information
from the onconfig file.
You specify the name of the database or table to load the same way you do with the onunload utility. Specify the database by listing it as the last option. To specify the owner and table to load, list them after the database name in the following format:
database:owner.table
Now that you have specified the device to use and the database or table to load, there are options that determine how the database or table is created:
- Target dbspace
Specify the dbspace where the database or table will be stored using the -d
dbspaceoption.- New index name
You can rename an index during an onload restore using the -i
oldindex newindexoption. This is done to avoid conflict with existing index names.- Relocated data fragment
The -fd
olddbspacenewdbspaceoption can be used to move a data fragment from one dbspace to another.- Relocated index fragment
The -fi
indexnameolddbspacenewdbspaceoption can be used to move an index from one dbspace to another.