Expert: Jean-Pierre Zuate Date: 5/9/2007 Subject: Ingres 6.4 db export to flat file
Question QUESTION: How can I export ingrs 6.4 db to flat file with any delimiter
ANSWER: Hello,
You can use the SQL instruction "copy table ..." and/or use one of the following Ingres utility : copydb or unloaddb.
Both copydb and unloaddb working on the same way :
1/ generate SQL scripts to copy data on flat file (copy.out) and to recreate tables and then load datas (copy.in)
2/ it's up to you to run the SQL files (.in or .out) to unload the datas
The difference between the both commands reside on the scope :
- copydb work only with the table of the user specified (with or without -u flag)
- unloaddb drain the entire database (with some applications stuffs like : abf (screen and code), open road, report writer, etc)
- unloaddb provide 2 more scripts : unload.ing and reload.ing
Run one time at least an unloaddb to see how unload.ing and reload.ing are constructed because they use some interstings flags for float values (the precision you use for export/import data).
If you need to reload your 6.4 data in an other Ingres environment (it is possible directly in Ingres2006 for example), take care of the following variables :
- II_MONEY_PREC
- II_MONEY_FORMAT
- II_DATE_FORMAT
- II_DECIMAL
- ... (etc)
See your environment (ingprenv and env) to see if those variables are set on your environment.
If you want more information about unloaddb and copydb you can download Ingres2006 documentation thoses commands are documented here (Command Reference Guide). The commands works nearly in the same way since Ingres 6.4 (take care of copydb options they are more developped since Ingres 2.6).
Hope this help,
Jean-Pierre
---------- FOLLOW-UP ----------
QUESTION: Thanks Jean.
But I want to export the db/table with delimiters. so that it can be consumed by other databases of other vendors.
Answer Hello,
All what you want is in the copy column format specification.
Here is an extract from the SQL Reference Guide.pdf (see http://docs.ingres.com/sqlref/ColumnFormats) about delimiters.
*** Begin extract ***
Delimiters
Delimiters are those characters in the data file that separate fields and mark
the end of records. Valid delimiters are listed in the following table:
Delimiter Description
Nl newline character
Tab tab character
Sp Space
nul or null null character
comma Comma
colon Colon
Dash Dash
lparen left parenthesis
rparen right parenthesis
X any non-numeric character
When a single character is specified as the delimiter, enclose that character in
quotes. If the data type specification is d0, the quotes must enclose the entire
format. For example, 'd0%' specifies a dummy column delimited by a percent
sign (%).
If the data type specification is char(0) or varchar(0), only the delimiter
character must be quoted. For example, char(0)'%' specifies a char field
delimited by a percent sign.
Do not use the space delimiter (sp) with char(0) fields: the char(0) format
uses spaces as padding for character and numeric columns.
When copying from a table into a file, insert delimiters independently of
columns. For example, to insert a newline character at the end of a line,
specify 'nl=d1' at the end of the column list. This directs the DBMS Server to
add one (d1) newline (nl) character. (Do not confuse lowercase 'l' with the
number '1'.)
*** End extract ***
