[Ingres] Importing/ Exporting data

Ingres allows you to export data from an Ingres installation to another (it is a platform independent procedure).

unloaddb

copydb

The main difference between these two commands, lies in the parameters defining them:

unloaddb regroups all the basic objects (including sources of tools for front end type ABF, OpenROAD or Report Writer, the Replicator catalogs

copydb regroups objects that it will invoke.

Making use of these commands

copydb

Example

copydb mydatabase mytable

This command will generate 2 files: copy.in and copy.out as long as the table myTable and mydatabase exists

These files contain a series of SQL commands:

copy.out:
unloading data: COPY TABLE ... INTO ...

copy.in:
Creation of the table: CREATE TABLE ...
Structure of the table: MODIFY ...
Reloading data: COPY TABLE ... FROM ...
Creation of the indexes:CREATE INDEX ...


Using these files:

To retrieve data:

sql mydatabase < copy.out 

To reload data:

sql mydatabase < copy.in

unloaddb

Example

unloaddb mydatabase 

4 files will be generated: copy.in, copy.out, reload.ing and unload.ing (or reload.bat and unload.bat under Windows)

Just like the copydb command, copy.in anf copy.out files contain the SQL commands necessary for the unloading of data , creation of objects (tables, indexes, rules, procedures, views)and reloading data. The main difference is the unloaddb that can generate programs to call these files containing SQL code.

Notes

To make a portable copy from an OS to another use the-c option (available for each of these 2 commands) that will allow you to generate export/import data in an ASCII format.

The following variables are not exhaustive:
II_DATE_FORMAT: date format
II_DECIMAL: decimal separator
II_MONEY_PREC:precision money data type
II_MONEY_FORMAT: money data format type (currency)
II_NULL_STRING: text used to represent null value
These variables must be identical between the source and the target machine.

Declare the same locations: same number, names, and types. The directories may be different

Extend the target database to the same locations as the source database: using infodb command, database check the locations of both, use accessdb or extenddb to extend a base.

Capture the result (stdout) of a file, and look for any line beginning with E_. This indicates an error that Ingres needs to analyze.

If a reload or copy.in fails, it may be due to the fact that the objects you are trying to create, exist already.