Abstract
The ‘unload’ command is not part of SQL, it is functionality built into dbaccess, other command line utilities and some GUI tools. This article shows how to extract data to a flat file directly from SQL in your application code (for instance), but only in version 11.50.xC6 or later.
Content
The following SQL will create an external table (with the same columns and data types as the existing table ‘agent’), and insert data into it. By default the format of the file specified in the DATAFILE clause is ascii and pipe delimited, so it is exactly as it would have been if created with the ‘unload’ statement in dbaccess.
Additionally, as with temporary tables, a ‘SELECT . . . . INTO . . . .‘ statement can include the definition and creation of an external table, so all you need is –
Once the SQL above has been executed, the external table will remain defined in the database, hence the inclusion of the ‘drop’ statement, which will categorically not delete /tmp/agent.unl.
Other options available when creating an external table can also be included in this sort of statement e.g.
Or, if you’re unloading a lot of data to a flat file
Another option when creating the external table (either specifically or via the ‘INTO’ clause of the INSERT statement) is to specify the data format, as below. The ‘INFORMIX’ option will cause the data to be written to the external table in the same format in which it is stored within the tablespace, and this is can be very much quicker as no translation from the ‘internal’ format to ascii is required. So if you want to unload a vast amount of data which will only be accessed by IDS (e.g. data migration) this is useful functionality – both the write and read operations could take a fraction of the time.
Caveats
This statement does require that one has permission to create a table in the current database, otherwise an error will be returned, -388. Equally one must have o/s permission to create the destination file.
The SQL is executed by the Informix instance, so the file will be created on the server where Informix is running.
Conclusion
In order to unload data from your application (4GL, C, perl using DBI and so on) you no longer need to execute an o/s command which runs dbaccess, just run the SQL – but dbaccess still is useful though!
Disclaimer
The code fix suggested above is provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.
Contact us
If you have any questions or would like to find out more unloading a flat file with Informix, simply contact us.
