LOAD and UNLOAD

Informix®

Informix provides two SQL instructions to export / import data from / into a database table:

The UNLOAD instruction copies rows from a database table into a text file:
UNLOAD TO "filename.unl" SELECT * FROM tab1 WHERE ..
The LOAD instructions insert rows from a text file into a database table:
LOAD FROM "filename.unl" INSERT INTO tab1

SAP ASE

SAP® ASE has LOAD and UNLOAD instructions, but those commands are related to database backup and recovery. Do not confuse with Informix commands.

Solution

LOAD and UNLOAD instruction are implemented in the Genero BDL runtime system with basic INSERT (for LOAD) or SELECT (for UNLOAD) SQL commands. The LOAD and UNLOAD instruction can be supported with various database servers.

However, LOAD and UNLOAD require the description of the column types in order to work, that can lead to some differences in the data formatting.

Note: If no transaction is started, the LOAD instruction will automatically execute a BEGIN WORK and COMMIT WORK when finished, or ROLLBACK WORK if a row insertion failed while loading. Terminating a transaction will automatically close cursors not defined WITH HOLD option. To workaround this situation, see more details in the LOAD reference topic.

The LOAD and UNLOAD BDL instructions are supported with SAP ASE with some limitations:

  • The LOAD instruction does not work with tables using emulated SERIAL columns because the generated INSERT statement holds the SERIAL column which is actually a IDENTITY column in SAP ASE. See the limitations of INSERT statements when using SERIAL types.

  • With SAP ASE, Informix DATETIME data is stored in BIGDATETIME columns, but BIGDATETIME columns are similar to Informix DATETIME YEAR TO FRACTION(5) columns. Therefore, when using LOAD and UNLOAD, those column values are converted to text data with the format "YYYY-MM-DD hh:mm:ss.fffff".