Hi,

I am having a question based on the conflict between a batch job and online transaction.

Detail:
I have an application from where online transactions came to access the DB2 database. During one point of time a batch job runs and creates a deadlock situation for the online transaction to be processed. This increases the response time for the online transactions. The batch job makes insert and update to one DB2 table which is also used by the online transaction. Is there any way to place a logic in the program (my point is have a kind of commit logic after every 100 row updates or something like that) to avoid this kind of scenario ?

check the LOCKTIMEOUT parameter in db cfg and make sure it is not anything less than 30 secs....


secondly you may need to reduce the commit count.....

Also you will have to check the plan for insert and update statements.... for large tables with large amount of data this will take time so you may need to fine tune your query.... you can also use db2 advisor for that matter and accordingly you'll have to change your LOCKTIMEOUT parameter and commit count... and / or maybe add new indexes....


Another alternative is to run the batch during non-peak hours.... any acceptable time before or after the maintenance window....

are you using insert or import ?
commitcount is for import only
try to use a commit as soon as possible or allowed
or if always on the same table - try lock table in share mode
no other updates can occur and others will be in lock wait$always try to be as specific as possible while describing the scenario and how this is accomplished

hi sorry for the delay response...
the program associated with the batch job performs insert, delete and update operations. Currently the program has commit logic in place but not for after any count. I checked with our DBA about the LOCKTIMEOUT parameter and as per their info it has been set correctly.

The batch job time cannot be changed due to some critical processing.

Please let me know if you have any other suggestions.

thanks,