DB2 deadlock and timeout Issue

attu · Post by **attu** » Wed Aug 06, 2008 8:47 am

A px job keeps failing because of this error

target_load,7: SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001

This is a multi instance job using DB2 EE stage as target (write method:load, write mode:append) , no. of records are around 50-70 million.
Before this job was submitted, the DBA made sure there were no locks on the database.

There were no issues with this job before and recently we are having these deadlock , timeout issues.

Is it true that PX holds a lock on the database object that is not visible in DB2?
Appreciate any valuable feedback from DS Gurus.
Thanks

ray.wurlod · Post by **ray.wurlod** » Wed Aug 06, 2008 3:21 pm

There's no such thing as a lock that is not visible to a DB2 DBA. Therefore it's not true what you assert. DataStage job is, as far as DB2 is concerned, just another client application; it does not do anything particularly unusual with locks.

attu · Post by **attu** » Thu Aug 07, 2008 8:30 am

interestingly the issue was resolved when dba's recycled the database before we restarted this job.
what is the root cause? the dba's think its an issue with PX.
any insights

attu · Post by **attu** » Thu Aug 07, 2008 3:53 pm

Just wanted to ask few more things:

We were having this issue while we tried to load data in db2 using db2 enterprise stage.

When the load fails, the table becomes inaccessible. DBA needs to drop and re-create table and tablespace ( If we do not want to retain the data)
If we want to retain data, then we have to restore from backup.

What are the benfits of using db2/udb load stage? Is it a better option?
What will happen if the load fails and table/tablespace goes to load pending state? How will non-recoverable mode =YES help us?

Thanks

fridge · Post by **fridge** » Tue Aug 12, 2008 6:27 am

u mention that the table becomes inaccessable as it is Load Pending if the process fails - this suggests that u are using the Bulk Loader utility to put the data in

depending on version of db2 - the load can take a lock on the Tablespace whilst the load is ongoing- as it takes a lock on the tablespace (not the table) could it be that u had another load going on into a seperate table that resides in same tablespace?

Also if the tablespace is in load-pending - should NEVER need to resort to drop and recreate table - certainly at the command line u can do a load TERMINATE to make it accessable

ray.wurlod · Post by **ray.wurlod** » Tue Aug 12, 2008 3:08 pm

U has not posted on this thread.

The second person personal pronoun in English is spelled "you".