DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 27

Points: 311

Post Posted: Sat Feb 01, 2020 5:32 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222462

Post Posted: Sun Feb 02, 2020 3:41 am Reply with quote    Back to top    

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? Confused

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Not yet rated
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 27

Points: 311

Post Posted: Sat Feb 08, 2020 5:13 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222462

Post Posted: Sun Feb 09, 2020 3:20 am Reply with quote    Back to top    

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. Confused

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1704
Location: Colleyville, Texas
Points: 23166

Post Posted: Tue Feb 11, 2020 1:20 pm Reply with quote    Back to top    

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
Rate this response:  
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 27

Points: 311

Post Posted: Sun Feb 16, 2020 1:25 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
rakesh.puli



Group memberships:
Premium Members

Joined: 22 Mar 2017
Posts: 27

Points: 311

Post Posted: Sun Feb 16, 2020 2:02 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43085
Location: Denver, CO
Points: 222462

Post Posted: Sun Feb 16, 2020 10:49 am Reply with quote    Back to top    

The easy question to answer is "what happens if we don't enable RCP"... then you're in the normal job world where all source/target metadata is locked into the job. Meaning you will need a separate job for each unique combination of source file schema and target table. You can also still do whatever data manipulation is needed as it flows through the job.

With RCP? That's another kettle of fish, a large topic that can't be easily summed up in a post or twelve. High level it allows you to specify the source and target metadata at runtime and as Andy noted, the data just flows through automagically from each source column to the matching target column. No data manipulation is possible for those columns.

I can't seem to find a good / all-inclusive reference for RCP, does anyone know where of if one exists? And while I could find a teeny reference to RCP for the Oracle Connector, it doesn't seem to be mentioned for DB2 that I could find. Confused What I can find is this or a very similar comment:

Quote:
You can define part of your schema and specify that extra columns be adopted and propagated through the rest of the job.

InfoSphere® DataStage® is also flexible about metadata. It can cope with the situation where meta data isn't fully defined. You can define part of your schema and specify that, if your job encounters extra columns that are not defined in the meta data when it actually runs, it will adopt these extra columns and propagate them through the rest of the job. This is known as runtime column propagation (RCP). This can be enabled for a project via the Administrator client, and set for individual links via the Output Page Columns tab for most stages, or in the Output page General tab for Transformer stages. You should always ensure that runtime column propagation is turned on if you want to use schema files to define column metadata.

"For most stages"? Is there a list somewhere of what stages support RCP? Closest I could find is in the resulting list from this search query. Sad

_________________
-craig

"You can never have too many knives" -- Logan Nine Fingers
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours