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
Multi-instance job with Oracle source and DB2 target
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 29
- Joined: Wed Mar 22, 2017 10:45 pm
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
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 29
- Joined: Wed Mar 22, 2017 10:45 pm
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.rakesh.puli wrote:but we have to read the files from oracle connector stage
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-
- Premium Member
- Posts: 29
- Joined: Wed Mar 22, 2017 10:45 pm
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.
-
- Premium Member
- Posts: 29
- Joined: Wed Mar 22, 2017 10:45 pm