Ingres - Importing/Exporting data

November 2016

Ingres is an open source database management system that is growing day by day and is supported by a huge online community as well. There are custom commands in Ingres that users can utilise for importing or exporting data. One can use unloaddb or copydb commands to do this. However, the main difference is in the parameters of these commands. While unloaddb regroups all the basic objects, copydb regroups only the invoked objects. The c-option of these commands can be used for importing or exporting data in ASCII format for making a portable copy of the target database.



Ingres allows you to export data from one 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 two 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 


Four 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 which can generate programs to call these files containing SQL code.

Notes


To make a portable copy from one OS to another, use the-c option (available for each of these two 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.

Related :

This document entitled « Ingres - Importing/Exporting data » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.