Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Extracting data from Oracle database

We have a Oracle V8i sitting on a Sun Solaris Server. We have another DB2 database on iSeries (version V5R3M0). Our aim is to extract data from the Oracle database every 30 minutes (it will not be a straight pull but will involve massaging the data) and loading onto DB2/400. At the moment there is no connectivity between these two servers.

Could you please suggest what should be our approach and any solutions would be much appreciated.

We've thought of following.

1. Pulling data using FTP (our iSeries has a FTP server setup). But I'm not sure if I can run a Unix Script to connect to this FTP server on Solaris. If I have to use FTP on Solaris, do I have to use any special add-ons?

2. Oracle Gateways on the iSeries: Our DBA is working on this scenario, but we're not sure if this is the way to go.

You have a couple of options. If your IT staff has Java skills, one approach would be to write Java code that uses type 4 JDBC drivers to access the Oracle database, massage the data, and populate the iSeries DB2 databases.

If your company is lacking Java skills or would prefer another method, you might want to consider Websphere Information Integrator (Formerly DB2 Information Integrator - DB2 II). When you need to access a non-DB2 data source from an iSeries program written in any high-level programming language, DB2 II is a great solution. Be warned that it does require the data access to be performed with an SQL-based interface such as CLI, embedded SQL, or JDBC. For more information on DB2 Information Integrator and other methods of accessing non-DB2 data sources, view the white paper "Heterogeneous Data Access for iSeries Applications"

Regarding the FTP question, you can use FTP to pull data from a Solaris server. You would first want to export the Oracle data into a delimited format and than FTP the delimited data to the iSeries server and then use the CPYFRMIMPF CL command.

There are also third-party data replication products that support going from Oracle to DB2 UDB for iSeries.


Visit the ITKnowledge Exchange and get answers to your DB2 questions fast.

Check out this Search400.com Featured Topic: Expert advice on DB2

Search400.com's targeted search engine: Get relevant information on DB2/400.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.