Informix – dbaccess vs dbexport for Exporting Tables to Text Files

informixsolaris

Currently, we are using the dbaccess utility to export all tables in the Informix database to text files. These are used to "mirror" the database multiple Windows machines running MSSQL Server on a nightly basis.

I was looking into the dbexport utility to see if it would be a viable solution. The text export takes almost 2 hours and I'm looking to shrink that time down if possible. However, I'm not familiar with these Informix utilities and we do not have a test environment for this… so I'm wondering what exactly is the output from dbexport? Is there an option to export each individual table to a text file with dbexport? Thank you.

Best Answer

Using dbexport has similarities, advantages and disadvantages compared to dbaccess.

With DB-Access, you are presumably generating the list of UNLOAD statements that will copy the data to file. You probably don't lock the entire database, so changes can occur while you're unloading the data, which could lead to inconsistencies.

If you use DB-Export instead, then it automatically locks the database (so the unloaded data will be consistent), and generates both the schema and unloaded data files using the same format the DB-Access generates. The advantage is you don't have to work so hard.

The other part of the game is DB-Import (dbimport). It expects to create a database (you can choose the name) and load it from the exported data.

Neither DB-Export nor DB-Import has support for parallelism; both operate on one table at a time. With DB-Access, you could arrange for de facto parallelism (by using multiple invocations of DB-Access to unload or load the data into multiple tables).

With DB-Access, you can control which tables are exported. With DB-Export, all tables are exported.

With DB-Export and DB-Import, you don't have to fret about creating and loading the tables in a correct sequence; with DB-Access, you have to think about ensuring that the data is loaded in a correct sequence.

On the whole, DB-Export plus DB-Import is simpler than using DB-Access, but DB-Access is ultimately more flexible.

Related Question