Multiple jobs inserting data to single oracle table

[Sreenivasulu]

Hi All,

Using SSIS we can insert data to a single table by running multiple jobs concurrently.


I have set a jobs which insert data to a single staging table in oracle (no primary keys present in this table)
I am getting deadlock issues when i run multiple jobs concurrently.
Any suggestions on this ?


Regards
Sreeni

[swarnkar]

I am getting deadlock issues when i run multiple jobs concurrently.

Hi,

Datstage is just another user to oracle, I mean if you can do any database activity through SQL+ client, you can do the same through datastage(keeping both Users same).

So if you are facing deadlock issue, it means its a issue with database not Datastage.

So investigae on Database side.

Thanks,
Nitin Swarnkar

[Sainath.Srinivasan]

Maybe it is doing a table level lock rather than row level.

Check with your DBA to identify the sessions and the corresponding data.

[Sreenivasulu]

Has any of you come across this issue earlier ?

Thanks sainath. I will look into this from DBA whether its a rowlevel lock or table level lock which is getting created.


Regards
Sreeni

[nagarjuna]

There may be possibility of getting into deadlock even while inserting concurrently . This may be true even if its a row level lock .In those cases , check INITRANS parameter in database and increase the value to number of nodes .Let us know if it helps .

[chulett]

Talk to your DBA, the exact nature of the data and the type of "multiple loads" will play into causing this problem.