Job Hangs

[just4geeks]

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

[Raftsman]

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.

[just4geeks]

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

[Raftsman]

Will there ever be any updates? If not, when insert then update is selected, it will only insert.

[just4geeks]

Will there ever be any updates? If not, when insert then update is selected, it will only insert.

There won't be any update. but just to double ensure have given where 1 = 0 in the update statement where clause .

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.

[pratimdc]

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.

[pratimdc]

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.