Hi All,

Need suggestion on this.

Using shell scripting, how I can handle the following things.

1.Connect to mainframe DB2 database.
2.Extract data from a table and stored in CSV file.
3.Extract data from CSV and do some conversion(Date format in DB2 to Date format in Oracle).
4.Load the data to oracle table in a Unix server.

This job will be executed as a scheduler to extract data from DB2 to Oracle for multiple tables.

Many thanks in advance.
Prasenjit

If you are going to run this process as a scheduled job from the machine that the Oracle database is on then I'm assuming first, that you have a database gateway setup(some info: database gateway) for the Oracle to DB2 databases.

IMHO, I would just create a database link from the Oracle database to the DB2 database(info: dblink) and just schedule a oracle job(sql statement or run a procedure) via a cron job or dbms_scheduler(info: dbms_scheduler).

Just an example sql:

Code:

begin
insert into oracle_schema.oracle_table
  select < columns you need >
   from  db2_schema.db2_table
  where  < your conditions >;
end;

Or you can export the data on the DB2 machine to a delimited flat file(i prefer TAB delimited) and ftp, scp, etc. the file over to the Oracle machine and import the flat file data via Sql*Loader or I prefer using an external table(info: external table).

hth