Page 1 of 1
Issue loading data using Oracle Enterprise stage
Posted: Sat Jan 15, 2011 3:58 pm
by dsadm_ws
Hi All,
I have an issue loading data into an Oracle Enterprise stage.
If i try to load the data into a txt file job runs in about 2 mins.
If I use an Oracle enterprise stage to load the data using Generated Update Only Option it takes about 2hrs.
Job Design:
Oracle Enterprise-->Trnsfrmr-->Oracle Enterprise(Generated Update Only)
Thanks in advance!!!
Posted: Sat Jan 15, 2011 4:09 pm
by chulett
Apples and oranges, of course.
What kind of volume are we talking about here? And since you are doing "updates only" are your key columns (the fields in your where clause) indexed in the target table?
Posted: Sat Jan 15, 2011 4:18 pm
by ray.wurlod
Get your DBA to monitor what DataStage is asking Oracle to do (that is, to monitor the session).
Posted: Sat Jan 15, 2011 5:27 pm
by dsadm_ws
@Chulett: Volume of data is about 10million records. No there are no indexes on the Target table.
@Ray: Sure Ray will do that. What kind of issue can we expect when we monitor the session.
Posted: Sat Jan 15, 2011 8:25 pm
by nayanpatra
The table could be in load pending state. In that case you may need to release it. I would also like to know which database you are using. I mean to say if you are using a native stage for database loading then it would give better performance. Indexing on the update key column may also play a vital role in the performance. If available you may also go for a connector stage for table load purpose.
Posted: Sat Jan 15, 2011 10:21 pm
by ray.wurlod
nayanpatra wrote:I would also like to know which database you are using.
Given that the subject specifies "using Oracle Enterprise stage" I would hazard the guess that the database is Oracle.
Posted: Sat Jan 15, 2011 11:20 pm
by chulett
dsadm_ws wrote:Volume of data is about 10million records. No there are no indexes on the Target table.
Hoping you inferred from my question that "no indexes" would not be the preferred answer, yes?