DSXchange

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.

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.

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.

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.

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!

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.

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?

Anyone help me, plz!

If you remove ETL_DATE, do all fields propagate?

Yep. Only add ETL_DATE, others are not change.