Transfer all tables in a DB2 to Oracle database

linhlv84 · Post by **linhlv84** » Wed Jul 06, 2011 3:21 am

Hi all,

I'm working in InfoSphere DataStage 8.0.1 (Windows OS) and facing the requirement to transfer all tables (in a schema) from DB2 into an Oracle database. The DB2 database has about 3000 tables.

Anyone have any idea?

Thanks so much.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 06, 2011 5:17 pm

Are the Oracle tables to be identically structured as the DB2 tables? If so you can make use of Runtime Column Propagation.

Is any transformation to be performed? Are there any BLOBs or CLOBs to transfer? These add a minor level of difficulty, but you can still do it by generating custom SQL in a sequence. I posted about how to do that earlier this year.

linhlv84 · Post by **linhlv84** » Wed Jul 06, 2011 8:10 pm

Thanks for your response.
Tables in source (DB2) and target (Oracle) are identical. Maybe, I need to add the current date to these tables.
I know a little about RCP (Runtime Column Propagation) and I also try this sample http://www.isecor.com/drupal6/node/163. But it's wrong. How can I define mapping in this sample (at COPY stage)?

Otherwise, there are no BLOBs or CLOBs and so on. Only DECIMAL, CHAR, VARCHAR and other simple data types in the database.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 07, 2011 12:42 am

You don't need to define mapping - just enable RCP on the output of the Copy stage. If you need to generate current date into a particular column in the target table, do that in a Transformer stage or a Column Generator stage, with RCP enabled to handle the other columns. Hopefully the current date column will be consistently named across all the Oracle tables.

linhlv84 · Post by **linhlv84** » Thu Jul 07, 2011 1:08 am

I have already created the job like this:

.
Detail in these Stage are:

+ Enable RCP in Source stage

+ Add ETL_DATE in Transformer stage

Enable RCP in the Transformer:

+ The Target stage (Oracle database)

But When I compile the Job, it's error in Add_ETL_DATE Transformer stage.
Help me, plz!

ray.wurlod · Post by **ray.wurlod** » Thu Jul 07, 2011 2:28 am

Why not make ETL_DATE data type Date?

While you're testing, lose the Oracle stage and replace with a text file. Then use one function at a time building the expression in the Transformer stage.

You really should have told us the precise error that was thrown, too.

linhlv84 · Post by **linhlv84** » Thu Jul 07, 2011 2:54 am

Sorry about my careless.

The simple job is

At the transformer stage:

But the Sequence File has only ETL_DATE field.

How I can transfer all field from the source database?

linhlv84 · Post by **linhlv84** » Thu Jul 07, 2011 9:53 pm

Anyone help me, plz!

ray.wurlod · Post by **ray.wurlod** » Fri Jul 08, 2011 7:14 pm

If you remove ETL_DATE, do all fields propagate?

linhlv84 · Post by **linhlv84** » Sun Jul 10, 2011 8:08 pm

Yep. Only add ETL_DATE, others are not change.