Hi
while i am running the job i am getting the error
APT_CombinedOperatorController(2),0: Fatal Error: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 52) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transa
Is this a Lock made by Datastage or it is a database lock
My job will had a Update SQL and the target is also is one of my source,
what my logic is i want to update my target table based on INNER JOIN on some other tables and some where Clauses
so can anyone explain why i am getting this lock.
and same updates are doing on other Tables its working fine
but for this Job only iam getting this error.
SRC1
-
-
------Lookup-----Transformer------Target(one of the source)
-
SRC2-
my actual query is
UPDATE Table_name
SET Table_name.HospitalGRP = [Hosp_Grp_Num], Table_name.HospitalFAC = [Hosp_Num]
from Table_name
INNER JOIN Table_name2 ON
( Table_name.SPECIALTY_PRODUCT_INDIC = Table_name2.PROBASE_PRODUCT_TYPE)
AND ( Table_name.EnrollmentFacility = Table_name2.PRVDR_FAC) AND
( Table_name.EnrollmentGroup = Table_name2.PRVDR_GRP) AND
( Table_name.StateCode = Table_name2.StateCode)
WHERE ((( Table_name.StateCode) In ('CA','OK','TX','OR')) AND
(( Table_name.PCP_INDIC)="Y") AND (( Table_name2.PRIMARY_HOSPITAL_INDICATOR)="Y"));
Query isn't the issue and as noted it is a database deadlock. You could be locking yourself or you could have had the bad luck to be deadlocked against another process. Is this repeatable? Does running on one node solve the problem?
If yes, you may be able to solve this by using intelligent hash partitioning with multiple nodes so one node doesn't run into another.
-craig
"You can never have too many knives" -- Logan Nine Fingers
i am running on 2-node processor
and i made the target as HASH partitioning and the job finished successful for a particular job and again for another job the job got aborted with folloeing error message
ODBC_Enterprise_76,1: Fatal Error: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transa
my source and target is SQl-Server.I made target as HASH partition and job was successful for 1 job and for another job it was aborted due to Lock
could you please help on this?
Thanks in Advance
Last edited by jpraveen on Mon Sep 13, 2010 1:23 am, edited 1 time in total.
i am running on 2-node processor
and i made the target as HASH partitioning and the job finished successful for a particular job and again for another job the job got aborted with folloeing error message
ODBC_Enterprise_76,1: Fatal Error: [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transa
my source and target is SQl-Server.I made target as HASH partition and job was successful for 1 job and for another job it was aborted due to Lock