Job hangs when performing insert/update

[devidotcom]

Hi All,

We are currently facing an issue with our datastage jobs. After performing the required transformations we do a self lookup on the table and then filter records as insert and update. The job has an update and insert flow to perform this.

The insert flow using upsert method with the update query with condition 1=2 and the update flow has a plain update query.

The jobs were working fine all this while even in production. We are facing issues where the processing is very slow, infact hangs. We had a look at the database front and found that the locks are generated on the child tables that have foreign key relationship. When we kill the job or unlock those tables the job gets aborted.

There is something going wrong, why would we have locks on the child tables, is there some setting on DataStage thats causing this.

When we disabled the FK constraints the job ran to success in minutes. Is this approach of having insert/update in the same job advisable? I don't see a problem doing this in PX version as everything runs in parallel.

There is no issues with the data too, there is a clear distinction between insert and update records.

Please suggest and help us resolve this issue.

Thanks.

[SURA]

Give a try for "Commit" on 1000 rows.

DS User

[devidotcom]

hhmm did not work..

[le thuong]

I would avoid having the same table as reference table (self lookup as you mentioned) and as target for insert/update in the same job. You can read the table (in a previous job) and populate a data set, then use this data set as reference in the current job.
Do you have index on the column(s) of the "Update ... where" clause ?

[chulett]

What database are we talking about? And are you saying you are loading a parent table where child tables are getting locks or vice-versa? FWIW, I don't see a 'self lookup' as being any kind of an issue here.