Bulk import from Informix into Oracle

Question

Hi. We need to pull some tables from an Informix SE database, truncate tables on Oracle 10g, and then populate them with the Informix data.

Does a bulk import work? Will data types clash?

I'd like to use a simple Java executable that we can schedule daily. Can a Java program call the bulk import? Is there an example you can provide? Thanks.

Answer 1

Interesting scenario!

There are several issues to worry about:

• What format does Oracle's bulk import expect the data to be in?
• What is the correct format for the DATE and DATETIME values?

Pragmatically (and based on experience with Informix rather than Oracle), rather than truncate the tables before bulk loading, I would bulk load the data into newly created tables (a relatively time-consuming process), then arrange to replace the old tables with the new. Depending on what works quickest, I'd either do a sequence of operations:

• Rename old table to junk table
• Rename new table to old table

followed by a sequence of 'drop junk table' operations, or I'd do:

• Drop old table
• Rename new table to old table

If the operations are done this way, the 'down time' for the tables is minimized, compared with 'truncate table' followed by 'load table'.

Oracle is like SE - its DDL statements are non-transactional (unlike IDS where you can have a transaction that drops a table, creates a new one, and then rolls back the whole set of operations).

How to export the data?

This depends on how flexible the Oracle loaders are. If they can adapt to Informix's standard output formats (for example, the UNLOAD format), then the unloading operations are trivial. You might need to set the DBDATE environment variable to ensure that date values are recognized by Oracle. I could believe that 'DBDATE="Y4MD-"' is likely to be accepted; that is the SQL standard 2009-12-02 notation for 2^nd December 2009.

The default UNLOAD format can be summarized as 'pipe-delimited fields with backslash escaping embedded newlines, backslash and pipe symbols':

abc|123|2009-12-02|a\|b\\c\
d||

This is one record with a character string, a number, a date, and another character string (containing 'a', '|', 'b', '\', 'c', newline and 'd') and a null field. Trailing blanks are removed from character strings; an empty but non-null character field has a single blank in the unload file.

If Oracle cannot readily be made to handle that, then consider whether Perl + DBI + DBD::Informix + DBD::Oracle might be a toolset to use - this allows you to connect to both the Oracle and the Informix (SE) databases and transfer the data between them.

Alternatively, you need to investigate alternative unloaders for SE. One program that may be worth investigating unless you're using Windows is SQLCMD (fair warning: author's bias creeping in). It has a fairly powerful set of output formatting options and can probably create a text format that Oracle would find acceptable (CSV, for example).

A final fallback would be to have a tool generate INSERT statements for the selected data. I think this could be useful as an addition to SQLCMD, but it isn't there yet. So, you would have to use:

SELECT 'INSERT INTO Target(Col1, Col2) VALUES (' ||
       Col1 || ', ''' || Col2 || ''');'
  FROM Source

This generates a simple INSERT statement. The snag with this is that it is not robust if Col2 (a character string) itself contains quotes (and newlines may cause problems on the receiving end too). You'd have to evaluate whether this is acceptable.