Dumping all tables to separate files

Discussion:

(too old to reply)

w***@gmail.com

2006-10-16 18:42:09 UTC

I have no experience with Informix, but am looking for some
suggestions. I need to dump out tables from an Informix database to
text files. Individual files per table would be ideal. Like I said, I
have no experience with Informix, and IBM's site doesn't have much to
offer as far as documentation. Is there some sort of "Database
Administration Utility" that allows you to specify tables and dump out
the data? If anybody has experience with it, I'm coming from a
Progress background.
Wes

Christine Normile

2006-10-16 18:59:13 UTC

Permalink

you can use unload within SQL:

UNLOAD TO <filename> DELIMITER "any character"
SELECT * from <tablename>.

You can use any valid SQL syntax.

you can use DBUNLOAD from the command line. You create a control
file with the above syntax.

you can use the High-Performance Loader (HPL) if the files are large
and spead is required.

you can use DBEXPORT and it will dump an entire database, each table
to an individual file.

Other than those, your options are limited.

Christine

Post by w***@gmail.com
I have no experience with Informix, but am looking for some
suggestions. I need to dump out tables from an Informix database to
text files. Individual files per table would be ideal. Like I said, I
have no experience with Informix, and IBM's site doesn't have much to
offer as far as documentation. Is there some sort of "Database
Administration Utility" that allows you to specify tables and dump out
the data? If anybody has experience with it, I'm coming from a
Progress background.
Wes
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list

Neil Truby

2006-10-16 19:55:58 UTC

Permalink

Christine Normile gave Chapter and Verse, but it seems to me that the
dbexport utility does almost exactly what you want with minimal effort.
Simply "sit" in the directory to which you wish to unload (and which has
enough free space, obviously) and type:

dbexport databasename

where databaseame is the nsmae of your database. This utility requies
exclusive access to the database being exported. It then creates a
subdirectory databasename.exp containing the SQL of the database schema, and
a pipe-delimited ASCII text file containing the unloaded rows for every
table in the database.

w***@gmail.com

2006-10-16 20:11:49 UTC

Permalink

Post by Neil Truby

Christine Normile gave Chapter and Verse, but it seems to me that the
dbexport utility does almost exactly what you want with minimal effort.
Simply "sit" in the directory to which you wish to unload (and which has
dbexport databasename
where databaseame is the nsmae of your database. This utility requies
exclusive access to the database being exported. It then creates a
subdirectory databasename.exp containing the SQL of the database schema, and
a pipe-delimited ASCII text file containing the unloaded rows for every
table in the database.

Thanks - I think this will work. Does Informix have any kind of ERD
diagram or dump of fields by table along with format, comments, etc...
that's not in SQL format? If not, I could probably parse out that
information.
Wes

Clive Eisen

2006-10-16 20:27:07 UTC

Permalink

Post by w***@gmail.com
Thanks - I think this will work. Does Informix have any kind of ERD
diagram or dump of fields by table along with format, comments, etc...
that's not in SQL format? If not, I could probably parse out that
information.
Wes

Not really

You could try http://www.dbschema.com/ which will suck a schema out and
display it for you.

Many other tools, no doubt, will do the same.

--
Clive Eisen
CTO Hildebrand Group

Neil Truby

2006-10-16 22:10:11 UTC

Permalink

Post by w***@gmail.com

Post by Neil Truby

Christine Normile gave Chapter and Verse, but it seems to me that the
dbexport utility does almost exactly what you want with minimal effort.
Simply "sit" in the directory to which you wish to unload (and which has
dbexport databasename
where databaseame is the nsmae of your database. This utility requies
exclusive access to the database being exported. It then creates a
subdirectory databasename.exp containing the SQL of the database schema, and
a pipe-delimited ASCII text file containing the unloaded rows for every
table in the database.

It doesn't. A think a 3rd party product, AGS Server Studio, can do this,
but you'd have to pay a modest licence fee.

Fernando Ortiz

2006-10-17 12:22:55 UTC

Permalink

Hi,

If you have 4GL programming language you can do a runable like this

[***@adela lib]$ cat ifxdump.4gl
DATABASE cte

MAIN
DEFINE w_table VARCHAR(100),
w_com, w_file CHAR(100)

SET ISOLATION TO DIRTY READ
SET LOCK MODE TO WAIT 50
DECLARE c_tab CURSOR FOR
SELECT tabname FROM systables WHERE tabid >= 100 AND tabtype =
"T"
FOREACH c_tab INTO w_table
LET w_file = "/ifxdump/", w_table
LET w_com = ' SELECT * FROM ', w_table
UNLOAD TO w_file w_com
END FOREACH
FREE c_tab
END MAIN

HTH

k***@dataintellect.com

2006-10-17 14:30:38 UTC

Permalink

Post by Fernando Ortiz

Hi,
If you have 4GL programming language you can do a runable like this
DATABASE cte
MAIN
DEFINE w_table VARCHAR(100),
w_com, w_file CHAR(100)
SET ISOLATION TO DIRTY READ
SET LOCK MODE TO WAIT 50
DECLARE c_tab CURSOR FOR
SELECT tabname FROM systables WHERE tabid >= 100 AND tabtype =
"T"
FOREACH c_tab INTO w_table
LET w_file = "/ifxdump/", w_table
LET w_com = ' SELECT * FROM ', w_table
UNLOAD TO w_file w_com
END FOREACH
FREE c_tab
END MAIN
HTH
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/iidnformix-list

do this in 2 steps:

1) select tabname from sysmaster where tabid > 100
## Get a list of all tables, push this list to a file

2) alter each tablename to look like this:
dbschema -d <dbname> -t <tablename> > tablename.sql

then run the script with all the above dbschema stmts in it