deadlock error

[vcannadevula]

Hi all,

I am getting this error when i run a job.
[IBM][CLI Driver][DB2/6000] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001

This is the only job I am running and it takes 4mins usually to finish this job.I tried running this job again and I am getting the same error.

Please let me how to solve this

Thank You

[ray.wurlod]

The error message, as you can see from its tags (in square brackets) is being generated within DB2. Talk with your DBA about possible causes of deadlocks and timeouts, such as other operations happening in the database.

I was once badly bitten because overnight batch update processes were run, about which we were never informed - the job tested OK during business hours, but failed to run overnight.

Does your design select from and update the same table? This can cause deadlocks in some cases. The workaround is to stage the data (for example into a text file) so that the SELECT can complete before the updating begins.

[vcannadevula]

Does your design select from and update the same table? This can cause deadlocks in some cases. The workaround is to stage the data (for example into a text file) so that the SELECT can complete before the updating begins.[/quote]

Ya , The job updates the same table from which it is selecting .I have reduced the transaction size for reading data from this table and it is fine now.Do you think it will be a problem later if dont stage data into a text file?
Thank You for your help

[ray.wurlod]

Reducing the transaction size does help, though the only safe size is 1.
Otherwise all you've done is to reduce (not eliminate) the risk that you're trying to update the same page as the one from which you're selecting.

This happens in databases that use page level locking rather than row level locking, striving for efficiency of lock and I/O management.

[dsxuserrio]

Hi
Did you stop the job while it was running?? This will leave the database process still running. So when you start the same job aain may result in deadlocks. Ask the DBA to clear all the locks and start afresh.

Also for selects add the phrase "for read only" . That helps a lot.
Thanks
dsxuserrio

[DSkkk]

due to selections from the table to which you are loading there can be a lock.
use clean up resources in the director to release all the locks and then try running your job again.
if that doesn't work then ask your datastage administrator to bounce the server.

[koolnitz]

Hi All,

I am also facing a similar problem.
I am using a DB2/UDB API stage to insert records into a table. In the same stage, after insertion, I am updating a column for all the inserted tuples.

Below are the details that might be helpful to analyze the problem:

DS Version: EE 7.5.1A
Stage used: DB2/UDB API
Array Size: Tried with many values (1,50,100...)
Transaction size: Tried with many values (1,50,100...)
Update Action: Insert rows without clearing
SQL>Generated: "Insert Into..." statement
SQL>After: "Update ..." statement

Warning: "db2_PreStg_Consolidated_Reln_ins,1: Warning: djp_Consolidated_Reln_3.db2_PreStg_Consolidated_Reln_ins: [IBM][CLI Driver][DB2/AIX64] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001"

Would appreciate if anyone can advise me to resolve this.

PN: Earlier, I was able to run the same job w/o any warnings. And there are not enough records to suck up the Database rollback segment.

Thanks in advance!!
- Nitin