DSXchange

I am running a job with two Oracle stages where it extracts the Key of table A and in second stage deletes some record from table A based on some conditions.

In the target I am using user define query for delete rows. like

DELETE FROM TABLE A
where KEY in (........

The job starts fine and it even delete those records which I can see on Database side but even after deleting the jobs doesn't finish, it shows running status for hours.

I am not sure what's going on DB side. Are there any other way to achieve this ?

Any help will be appreciated.

Let me know if the Oracle database is on same server, if not whats the database version and the client connector version.

Check for locks in oracle. Try running on single node config.

Datastage and oracle are on same server.

I am not sure but i tried using nolock in my query but it dint work.

Try running job by constructing new Oracle stage without any parameters.

Sainath.Srinivasan wrote:Try running on single node config.

Did you do that?

Yes I tried running with single node but still the same issue.

Get your DBA to monitor the session. In my experience DELETE does take a very long time, particularly when there are referential integrity constraints involved.

But the fact that the deletes 'can be seen' on the database side, which I take to mean they can be seen to be gone, would imply they have been committed and thus the Oracle part is done. Well... unless intermediate commits are being done and they only checked some.

How did you find it is the 'delete' stage that takes time?

How many rows matching the delete criteria are available in the target?

What if you do a delete outside DS? How long does it take?

Did you set commit intervals and see the count go down?

Did you get the plan for delete SQL?

In addition to that

If you are sure database is taking time then
Check the indexes on columns used in where condition. I believe Its indexed as you termed it as KEY.

Analyze the table and indexes. If references of that table are present in the database analyse all referneced tables and indexes.

Check if cascade delete is enabled on that table generally it should not.

Ask your DBA to monitor the session to find any bottlenecks.

Hi,
Are you selecting and deleting from same table?
if that is the case then it will lock.

regards
sivaji

Selecting and deleting from same table will not cause locks unless you lock with 'FOR UPDATE'.

It may result in blowing your temp, rollback or redo spaces (depending on version and action in Oracle).