Hi,
The Job Design is
Oracle enterprise stage-->lookup stage-->surrogate key stage-->transformer-->Oracle enterprise stage(OES)
The Target OES has write method :Upsert with Insert then update mode
As we just need to insert record so in update where clause have given where 1 = 0
When we see the performance statistics of the job,
after the surrogate key stage only few records are passed(50,000) and the same status is for long time i.e. the job hangs.
Where as when the Target OES write method was load the job never hunged ,
I have just changed the write method.
Please advice
Thanks In Advance
Job Hangs
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Job Hangs
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
Raftsman wrote:I think the where clause is your problem. I understand that you don't want to use the update. You can use the OPEN command in the input ORACLE stage to truncate the table and then the update clause would never get actioned unless you have duplicate keys.
Thanks for the quick response,
I can't truncate the table as the table contains data.
My basic requirement is to load the data into the table.
When I use write method as Load . I cannot ensure that the commit is execute when the full load happens as Load uses sqlldr.
I can ensure that commit is execute at the end of full load by upsert method using the environment variable
$APT_ORA_UPSERT_COMMIT_ROW_INTERVAL
Please advice if there is any work around.
I am not sure why the job hangs,when i have just changed the write method.
Thanks In Advance
Last edited by just4geeks on Mon Sep 08, 2008 12:34 pm, edited 1 time in total.
Attitude is everything....
-
- Premium Member
- Posts: 644
- Joined: Sat Aug 26, 2006 3:59 pm
- Location: Mclean, VA
There won't be any update. but just to double ensure have given where 1 = 0 in the update statement where clause .Raftsman wrote:Will there ever be any updates? If not, when insert then update is selected, it will only insert.
I did remove the where clause from the update statement and try running the job with Upsert Order :Insert then Update,
But no luck the Job yet hangs after 50,000 rows
Please Advice.
Thanks In Advance.
Attitude is everything....
First Possibility -
Which version of DataStage is it?
Because if it is DS 7.5.3 and you are connecting to Oracle 10g, then IBM patch has to be installed. Since in this version they had removed db utilities API. On sending email to IBM, they can provide the patch for your machine.
Second Possibility -
Is the job monitor on?
If yes, please include the variable NO_JOB_MON environmental variable and set it to "True". Execute the job again, it may solve the problem.
Third Possibility -
Is there any unfinished transaction on Oracle table?
Is the table journaled?
I guess, you are not using any where clause that include table columns. In that case building of indexes on those columns are advisable if the table size is high.
Which version of DataStage is it?
Because if it is DS 7.5.3 and you are connecting to Oracle 10g, then IBM patch has to be installed. Since in this version they had removed db utilities API. On sending email to IBM, they can provide the patch for your machine.
Second Possibility -
Is the job monitor on?
If yes, please include the variable NO_JOB_MON environmental variable and set it to "True". Execute the job again, it may solve the problem.
Third Possibility -
Is there any unfinished transaction on Oracle table?
Is the table journaled?
I guess, you are not using any where clause that include table columns. In that case building of indexes on those columns are advisable if the table size is high.
First Possibility -
Which version of DataStage is it?
Because if it is DS 7.5.3 and you are connecting to Oracle 10g, then IBM patch has to be installed. Since in this version they had removed db utilities API. On sending email to IBM, they can provide the patch for your machine.
Second Possibility -
Is the job monitor on?
If yes, please include the variable NO_JOB_MON environmental variable and set it to "True". Execute the job again, it may solve the problem.
Third Possibility -
Is there any unfinished transaction on Oracle table?
Is the table journaled?
I guess, you are not using any where clause that include table columns. In that case building of indexes on those columns are advisable if the table size is high.
Which version of DataStage is it?
Because if it is DS 7.5.3 and you are connecting to Oracle 10g, then IBM patch has to be installed. Since in this version they had removed db utilities API. On sending email to IBM, they can provide the patch for your machine.
Second Possibility -
Is the job monitor on?
If yes, please include the variable NO_JOB_MON environmental variable and set it to "True". Execute the job again, it may solve the problem.
Third Possibility -
Is there any unfinished transaction on Oracle table?
Is the table journaled?
I guess, you are not using any where clause that include table columns. In that case building of indexes on those columns are advisable if the table size is high.