Dumping all tables to separate files
A newcomer to Informix asked how to dump each table of a database to its own text file. Replies listed the standard options: UNLOAD TO <file> DELIMITER ... SELECT * FROM <table> in SQL, dbunload with a control file, the High-Performance Loader for large/fast unloads, and dbexport, which was recommended as the simplest: run "dbexport dbname" (needs exclusive access) and it creates a dbname.exp directory with the schema SQL plus one pipe-delimited file per table. A 4GL loop over systables and a shell script driving dbschema per table were also offered. On the follow-up question about ERD/non-SQL schema documentation, the answer was that Informix has none built in; third-party tools such as DbSchema or AGS Server Studio were suggested.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
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
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
On Oct 16, 2006, at 1:42 PM, wes.faul@gmail.com wrote:
> 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
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
<wes.faul@gmail.com> wrote in message
news:1161024129.542141.51830@m73g2000cwd.googlegroups.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
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.
Neil Truby wrote:
> <wes.faul@gmail.com> wrote in message
> news:1161024129.542141.51830@m73g2000cwd.googlegroups.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
>
> 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.
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
wes.faul@gmail.com wrote: > Neil Truby wrote: >> <wes.faul@gmail.com> wrote in message > > 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
<wes.faul@gmail.com> wrote in message
news:1161029509.247776.71130@m7g2000cwm.googlegroups.com...
>
> Neil Truby wrote:
>> <wes.faul@gmail.com> wrote in message
>> news:1161024129.542141.51830@m73g2000cwd.googlegroups.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
>>
>> 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.
>
> 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
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.
wes.faul@gmail.com wrote:
> 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
Hi,
If you have 4GL programming language you can do a runable like this
[cte@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
On Tuesday 17 October 2006 06:22, Fernando Ortiz wrote:
> wes.faul@gmail.com wrote:
> > 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
>
> Hi,
>
> If you have 4GL programming language you can do a runable like this
>
> [cte@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
>
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> 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