Multi-instance job with Oracle source and DB2 target

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Multi-instance job with Oracle source and DB2 target

Post by rakesh.puli »

Hi

I have a requirement like to load 50 DB2 target tables from 50 Oracle source tables or source files with different schemas with only using source as Oracle connector stage.

How can I achieve this? Please let me know.

Thanks
Rakesh
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Well, the short answer is RCP a.k.a. runtime column propagation, is that anything you're familiar with? And one problem will be your "or source files" statement along with "only using source as oracle connector stage". While there are ways in Oracle to read a file as if it were a table, it's what I'd call an advanced topic that I have yet to see many (any?) people use. Is that really a requirement here? :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Post by rakesh.puli »

Hi Chulett,

Here the source is only files, All these source files have different schemas.
But we have to read the files using the Oracle connector stage and load into target DB2 tables.

Thanks
Rakesh Puli
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

rakesh.puli wrote:but we have to read the files from oracle connector stage
No, no you don't. It makes zero sense. Push back on that "requirement". And still wondering if you've ever used RCP, something critical to have any chance of doing this in a generic sense. Otherwise (unless someone has other thoughts) you're back to defining a job per file schema and launching the right job for each file. AND... if for some odd reason you have no other choice than to use an Oracle connector, here is an introduction to Oracle External Tables. BUT I don't see how that won't lock you into a non-RCP / non-generic solution. :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

I concur with Craig.

You need to use the correct stage for the correct source / target.

1) If your source is an Oracle table, then use an Oracle Connector
2) If your source is a file, use a Sequential File stage with a schema file
3) If your target is DB2 table, use DB2 stage

If you are doing no data manipulation, this can be accomplished with two jobs using RCP.

1) One job that reads from a sequential file and dumps to DB2.
2) One job that reads from an Oracle table and dumps to DB2.

RCP can be used, assuming all source columns (fields) are represented in the target tables. All the source and target names and schema file names would have to be job parameters that are updated as required.
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Post by rakesh.puli »

From your points I understand that we cannot read a file from oracle connectorstage or anyother database connector stages. We can read the file from only filestages like sequential file stage. If we read only from sequential file stage, then why we have to enable the run time column propagation and what happens if we dont enable it.
rakesh.puli
Premium Member
Premium Member
Posts: 29
Joined: Wed Mar 22, 2017 10:45 pm

Post by rakesh.puli »

Suppose if i want to load the metadata in sequential file at source stage, I will import the metadata from the table definition and load it into the sequential file stage, but I dont understand here while loading, what is the use of enabling the rcp option? what happens if we dont enable it.
Post Reply