Unload to a flat file without dbaccess!

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.

	CREATE EXTERNAL TABLE agt_ext 
	  SAMEAS agent
	    USING (DATAFILES ("DISK:/tmp/agent.unl"));

	INSERT INTO agt_ext
	  SELECT * FROM agent;
	

Additionally, as with temporary tables, a ‘SELECT . . . . INTO . . . .‘ statement can include the definition and creation of an external table, so all you need is -

	SELECT *
	  FROM agent
	  INTO EXTERNAL agt_ext
	USING (DATAFILES ("DISK:/tmp/agent.unl"));
	DROP TABLE agt_ext; 
	

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.

	SELECT *
	  FROM agent
	  INTO EXTERNAL agt_ext
	USING (DATAFILES (“DISK:/tmp/agent.unl”),
			DELIMITER “,”);
	

Or, if you’re unloading a lot of data

	SELECT *
	  FROM agent
	  INTO EXTERNAL agt_ext
	USING (DATAFILES (“DISK:/tmp/agent1.unl”,
					   “DISK:/tmp/agent2.unl”),
					   “DISK:/tmp/agent3.unl”));
	

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.

	SELECT *
      FROM agent
	  INTO EXTERNAL agt_ext
	USING (FORMAT ‘INFORMIX’,
			DATAFILES ("DISK:/tmp/agent.unl"));
	

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.