Technote

(troubleshooting)
MassLoader fails due to lock timeouts when updating a DB2 database
Problem(Abstract)
You are loading data to a DB2 database when MassLoader fails with the following error:

[IBM][CLI Driver][DB2/LINUXPPC] SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001
Cause The LOCKTIMEOUT database configuration parameters specifies the number of seconds a DB2 CLI application will wait to obtain locks. Typical values for WebSphere Commerce Database go from 30 seconds to two minutes. Once this time has elapsed, the application will fail with SQL911N and reason code 68.

The following command can be used to obtain the current lock timeout for the database:
db2 get db cfg for dbname Resolving the problem
Only use MassLoader when traffic to the site is minimal. If the site is being accessed, MassLoader might fail to lock tables such as user or catalog which are constantly used.

You should not change the lock timeout for the database as it could have an impact on the performance of the site. Always remember that the database must be tuned for Web browsing and not for the massloader utility.

If you are seeing lock escalations (db2diag.log) or deadlocks on tables being updated by massloader, decrease the commit count, so locks are released more frequently.

If your business requires that you massload data during the day when the tables are in use, use the following method to increase the lock timeout at CLI level for the massloader application only. This won't affect the database configuration or other applications connecting to the database.

  1. Create a new alias for the WebSphere Commerce database:
    db2 catalog db dbname as newname

  2. Update the CLI configuration for the new alias:
    db2 update cli cfg for section newname using LockTimeout 300            
    The timeout value is specified in seconds. On the previous example, we specify 300 seconds or 5 minutes.

  3. Ensure the changes are in place:
    db2 get cli configuration

  4. When using MassLoader set the -dbname specifier to use the new alias.


This method has the following limitations:

  1. It is only available on DB2 8.2 (8.1.7) or higher

  2. The import method has to be sqlimport.


Increasing the lock timeout for massloader won't ensure that all the timeout situations are resolved, but there should be a substantial decrease in the number of occurrences.
Cross Reference information
Segment Product Component Platform Version Edition
Commerce WebSphere Commerce Business Edition Implementation / Usage AIX, Linux, Solaris, Windows 5.6, 5.6.1 Business Edition
Commerce WebSphere Commerce Professional Edition Implementation / Usage AIX, Linux, Solaris, Windows 5.6, 5.6.1, 6.0 Professional Edition
Commerce WebSphere Commerce - Express Implementation / Usage Linux, Windows 5.6, 5.6.1, 6.0 Express
Commerce WebSphere Commerce Developer Enterprise Implementation / Usage Windows 6.0 Enterprise
Commerce WebSphere Commerce Developer Professional Edition Implementation / Usage Windows 5.6, 5.6.1, 6.0 Developer Professional Edition
Commerce WebSphere Commerce Developer Express Implementation / Usage Windows 5.6, 5.6.1, 6.0 Developer Express
   

Document Information

Current web document: http://www.ibm.com/support/docview.wss?uid=swg21225681