LOAD and UNLOAD instructions

The LOAD and UNLOAD instructions can produce different data formats depending on the database server type.

When executing the LOAD and UNLOAD instruction from a BDL program, SQL commands are not sent as is to the database server.

In fact, the Genero BDL runtime system implements the LOAD and UNLOAD instructions, using basic INSERT (for LOAD) or SELECT (for UNLOAD) SQL commands.

With this implementation, 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.

Since each database has its own set of SQL data types, you must pay attention to the BDL type that results from the native database column type.

Depending on the native data type, data formatting may be different from Informix®.

For example, when using Oracle DB, if the table contains a DATE column, the LOAD and UNLOAD instruction will use the date/time format YYYY-MM-DD hh:mm:ss. Since the native Oracle DATE type can be used to store both Informix DATE or DATETIME YEAR TO SECOND values, and as LOAD / UNLOAD need to make date/time to string conversions when reading from or writing to unload files, it is not possible to select one of the Informix/BDL native date type formats.
Note: When using LOAD/UNLOAD, if the target database server provides the exact equivalent date types as the native Informix/BDL DATE, the date values will use the DBDATE format setting.
Table 1. Database server support of LOAD and UNLOAD
Database Server Type LOAD/UNLOAD support
IBM® DB2® LUW Yes, see details
IBM Informix Yes, native SQL feature
IBM Netezza Yes, see details
Microsoft™ SQL Server Yes, with some limitations
Oracle® MySQL Yes, see details
Oracle Database Server Yes, with some limitations
PostgreSQL Yes, see details
SAP® ASE Yes, see details
SAP HANA® Yes, see details
SQLite Yes, see details