By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,222 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

deadlock or timeout (very urgent)

P: 1
Hi All,

I am a newbie to db2 database. Currently i am facing an issue which i know for sure that many would have faced and resolved.

I am running 6 sql scripts concurrently against the db

the db server is db2/6000 8.2.3

The log file shows couple of errors with the same msg ,

Expand|Select|Wrap|Line Numbers
  1. DROP TABLE  testing123
  2. DB21034E  The command was processed as an SQL statement because it was not a  valid Command Line Processor command.  During SQL processing it returned:
  3. SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001
No one is using this table when i try to drop this table.

But after sometime when i run the same command , the script executes successfully. The same error appears on all the 6 log files.

I also tried running one sql at a time and found that i did not encounter the timeout error .

I guess this should be due to the timeout parameter set at the database level.
But i am not sure which parameter to tweak .

some of the parameters that was found on the net were locktimeout , maxlocks etc

Expand|Select|Wrap|Line Numbers
  1. Max storage for lock list (4KB)              (LOCKLIST) = 25000
  2. Interval for checking deadlock (ms)         (DLCHKTIME) = 10000
  3. Percent. of lock lists per application       (MAXLOCKS) = 80
  4. Lock timeout (sec)                        (LOCKTIMEOUT) = 180
i am also trying to get the snapshot of the database.

Please help me resolve this issue .

Any help on this regard highly appreciated .

-K
Dec 3 '07 #1
Share this Question
Share on Google+
1 Reply


docdiesel
Expert 100+
P: 297
Hi,

try a
Expand|Select|Wrap|Line Numbers
  1. set lock mode to wait 5
in your sql scripts to set their timeout to 5 seconds. Perhaps then they give you a hint what's wrong.

Regards,

Bernd
Dec 5 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.